質問編集履歴

文法の修正

2018/09/22 01:07

投稿

locoJr.

スコア15

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -374,7 +374,7 @@
       WHEN MAX(t.日時) IS NULL THEN
-        CONVERT(datetime, CONVERT(char(8), MAX(tmp2.日時), 112) + '06:00:00')
+        CONVERT(datetime, CONVERT(char(8), MAX(tmp2.日時), 112) + '06:00:00') --6時の情報は、別テーブルに定義した値をSELECTします。
       ELSE

書式の改善

2018/09/22 01:07

投稿

locoJr.

スコア15

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -42,7 +42,15 @@
 ### 検討内容
+上記テーブルのデータはViewで以下の形式で持つことで、楽に問合せができると考えています。
+※タスク登録時には、既存の機能で他にも多くのテーブルにデータを追加しているため、実データで持つのは避けられたら・・・と思っています。
+![イメージ説明](333663d3d2e755ca781ac0b53fa72ee3.png)
-クライアントアプリからは、以下を引数としたストアドファンクションを実行します。
+問い合わせの方法ですが、クライアントアプリから以下を引数としたストアドファンクションを実行します。
 - 作業ID
@@ -56,15 +64,7 @@
 - 開始可能日時
-上記テーブルのデータはViewで以下の形式で持つことで、楽に問合せができると考えています。
+　※開始日時が一番早い1件のみ。（TOP 1かつORDER BY 開始日時 DESC）
-チームの時間帯毎の残リソース及び作業時間があれば、可能と考えました。
-タスク登録時には、既存の機能で他にも多くのテーブルにデータを追加しているため、実データで持つのは避けられたら・・・と思っています。
-![イメージ説明](333663d3d2e755ca781ac0b53fa72ee3.png)
@@ -82,71 +82,339 @@
+### ソース
+2018/09/22：追記しました。
+- チームマスタ
+```lang-SQL
+CREATE TABLE TeamMaster
+(
+  チームID varchar(10) NOT NULL,
+  所有リソース decimal(5,2) NOT NULL
+)
+ON PRIMARY
+ALTER TABLE TeamMaster ADD CONSTRAINT PK_TeamMaster
+PRIMARY KEY (チームID)
+ON PRIMARY
+INSERT INTO TeamMaster (チームID, 所有リソース) VALUES ('A', 5)
+```
+- 作業マスタ
+```lang-SQL
+CREATE TABLE WorkMaster
+(
+  作業ID varchar(10) NOT NULL,
+  作業名 varchar(20) NOT NULL,
+  必要リソース decimal(4,2) NOT NULL
+)
+ON PRIMARY
+ALTER TABLE WorkMaster ADD CONSTRAINT PK_WorkMaster
+PRIMARY KEY (作業ID)
+ON PRIMARY
+INSERT INTO WorkMaster (作業ID, 作業名, 必要リソース) VALUES ('W1', 'Work1', 2)
+INSERT INTO WorkMaster (作業ID, 作業名, 必要リソース) VALUES ('W2', 'Work2', 2)
+```
+- チーム⇔作業紐付けマスタ
+```lang-SQL
+CREATE TABLE Team_WorkMaster
+(
+  チームID varchar(10) NOT NULL,
+  作業ID varchar(10) NOT NULL
+)
+ON PRIMARY
+ALTER TABLE Team_WorkMaster ADD CONSTRAINT PK_Team_WorkMaster
+PRIMARY KEY (チームID, 作業ID)
+ON PRIMARY
+INSERT INTO Team_WorkMaster (チームID, 作業ID) VALUES ('A', 'W1')
+INSERT INTO Team_WorkMaster (チームID, 作業ID) VALUES ('A', 'W2')
+```
+- 計画済タスクトランザクション
+```lang-SQL
+CREATE TABLE PlanedTaskTran
+(
+  タスクID decimal(10,0) NOT NULL, --実際には自動採番を設定している
+  作業ID varchar(10) NOT NULL,
+  開始日時 datetime NOT NULL,
+  終了日時 datetime NOT NULL
+)
+ON PRIMARY
+ALTER TABLE PlanedTaskTran ADD CONSTRAINT PK_PlanedTaskTran
+PRIMARY KEY (タスクID)
+ON PRIMARY
+INSERT INTO PlanedTaskTran (タスクID, 作業ID, 開始日時, 終了日時) VALUES (1, 'W1', '2018/09/21 10:00:00', '2018/09/21 15:00:00')
+INSERT INTO PlanedTaskTran (タスクID, 作業ID, 開始日時, 終了日時) VALUES (2, 'W2', '2018/09/21 11:00:00', '2018/09/21 14:00:00')
+```
 ### 試したこと
-Viewの多段使用で近いところまではいったのですが、実際のソースは会社にありメモできていませんが、おおまかに以下のような流れで考えました。
+~~Viewの多段使用で近いところまではいったのですが、実際のソースは会社にありメモできていませんが、おおまかに以下のような流れで考えました。~~
-```
+2018/09/22：誤記等もありましたので、修正しました。
---1.一旦、日時情報を1カラムにまとめる
+- 一旦、日時情報を1カラムにまとめる
+```SQL
+CREATE VIEW tmp1
+  (種別, チームID, 作業ID, 所有リソース, 必要リソース, 日時)
+AS
 SELECT
   1 AS 種別
-  ,T.チームID
+  ,Team_WorkMaster.チームID
+  ,PlanedTaskTran.作業ID
-  ,T.所有リソース
+  ,TeamMaster.所有リソース
-  ,W.必要リソース
+  ,WorkMaster.必要リソース
-  ,P.開始日時 AS 日時
+  ,PlanedTaskTran.開始日時 AS 日時
-FROM 計画済タスクトランザクション AS P
+FROM PlanedTaskTran
-JOIN チーム⇔作業紐付けマスタ AS TW ON TW.作業ID = P.作業ID
+JOIN Team_WorkMaster ON Team_WorkMaster.作業ID = PlanedTaskTran.作業ID
-JOIN 作業マスタ AS W ON W.作業ID = P.作業ID
+JOIN WorkMaster ON WorkMaster.作業ID = PlanedTaskTran.作業ID
-JOIN チームマスタ AS T ON T.チームID = TW.チームID
+JOIN TeamMaster ON TeamMaster.チームID = Team_WorkMaster.チームID
-UNION
+UNION ALL --データは重複することはないため、UNION ALLに修正しました。
 SELECT
   -1
-  ,T.チームID
-  ,T.所有リソース
-  ,W.必要リソース
-  ,P.終了日時
-FROM 計画済タスクトランザクション AS P
-JOIN チーム⇔作業紐付けマスタ AS TW ON TW.作業ID = P.作業ID
-JOIN 作業マスタ AS W ON W.作業ID = P.作業ID
-JOIN チームマスタ AS T ON T.チームID = TW.チームID
---2.種別の情報から、・・・すみませｎ、忘れました。
---3.2で作成した情報を1レコードずらしてJOIN(FROM 2 JOIN 2 AS 2V ON 2V.日時 < 2.日時)し、
---  2V.日時を開始日時、2.日時を終了日時として情報を算出する。
---4.3で作成した情報から、DATEDIFF(ss,開始日時,終了日時)で作業時間を算出する。
-```
+  ,Team_WorkMaster.チームID
+  ,PlanedTaskTran.作業ID
+  ,TeamMaster.所有リソース
+  ,WorkMaster.必要リソース
+  ,PlanedTaskTran.開始日時
+FROM PlanedTaskTran
+JOIN Team_WorkMaster ON Team_WorkMaster.作業ID = PlanedTaskTran.作業ID
+JOIN WorkMaster ON WorkMaster.作業ID = PlanedTaskTran.作業ID
+JOIN TeamMaster ON TeamMaster.チームID = Team_WorkMaster.チームID
+```
+|種別|チームID|作業ID|所有リソース|必要リソース|日時
+|:--|:--|:--|:--|:--|:--|
+|1|A|W1|5|2|2018/09/21 10:00:00|
+|1|A|W2|5|2|2018/09/21 11:00:00|
+|-1|A|W2|5|2|2018/09/21 14:00:00|
+|-1|A|W1|5|2|2018/09/21 15:00:00|
+- 日時毎の残リソースを算出する
+```lang-SQL
+CREATE VIEW tmp2
+  (チームID, 日時, 残リソース)
+AS
+SELECT
+  tmp1.チームID
+  ,tmp1.日時
+  ,(SELECT
+      MAX(t.所有リソース) - (SUM(t.種別) * MAX(t.必要リソース))
+    FROM tmp1 AS t
+    WHERE t.日時 < tmp1.日時
+    OR (t.日時 = tmp1.日時 AND t.種別 <= tmp1.種別)) AS 残リソース
+FROM tmp1
+```
+|チームID|日時|残リソース|
+|:--|:--|:--|
+|A|2018/09/21 10:00:00|3|
+|A|2018/09/21 11:00:00|1|
+|A|2018/09/21 14:00:00|3|
+|A|2018/09/21 15:00:00|5|
+- 時間帯毎の残リソースを算出する
+```lang-SQL
+CREATE VIEW RemainResource
+  (チームID, 開始日時, 終了日時, 残リソース, 作業可能時間)
+AS
+SELECT
+  tmp3.*
+  ,DATEDIFF(ss,tmp3.開始日時,tmp3.終了日時) AS 作業可能時間
+FROM
+  (SELECT
+    MAX(tmp2.チームID) AS チームID
+    ,CASE
+      WHEN MAX(t.日時) IS NULL THEN
+        CONVERT(datetime, CONVERT(char(8), MAX(tmp2.日時), 112) + '06:00:00')
+      ELSE
+        MAX(t.日時)
+    END AS 開始日時
+    ,MAX(tmp2.日時) AS 終了日時
+    ,MAX(tmp2.残リソース) AS 残リソース
+  FROM tmp2
+  LEFT OUTER JOIN tmp2 AS t
+    ON t.日時 < tmp2.日時
+  GROUP BY tmp2.日時) AS tmp3
+```
+|チームID|開始日時|終了日時|残リソース|作業可能時間|
+|:--|:--|--:|--:|--:|
+|A|2018/09/21 06:00:00|2018/09/21 10:00:00|5|14400|
+|A|2018/09/21 10:00:00|2018/09/21 11:00:00|3|3600|
+|A|2018/09/21 11:00:00|2018/09/21 14:00:00|1|10800|
+|A|2018/09/21 14:00:00|2018/09/21 15:00:00|3|3600|
+※上記クエリでは、2018/09/21 15:00:00～2018/09/22 06:00:00までの残リソースが5という情報は出力できていません。
 かなり長々としたクエリになり、且つ計画済トランザクションのデータが多いとパフォーマンスにも影響を与えると思い、

文法の修正

2018/09/22 01:04

投稿

locoJr.

スコア15

test CHANGED Viewed

	@@ -1 +1 @@
1	- ~~チームの~~時間帯~~毎の使用リソースを出力す~~るViewを作成したい
1	+ 時間帯が重複していれば、数値が少なくなるようなViewを作成したい

test CHANGED Viewed

File without changes