質問編集履歴

3

文法の修正

2018/09/22 01:07

投稿

locoJr.
locoJr.

score15

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

2

書式の改善

2018/09/22 01:07

投稿

locoJr.
locoJr.

score15

test CHANGED
File without changes
test CHANGED
@@ -42,7 +42,15 @@
42
42
 
43
43
  ### 検討内容
44
44
 
45
+ 上記テーブルのデータはViewで以下の形式で持つことで、楽に問合せができると考えています。
46
+
47
+ ※タスク登録時には、既存の機能で他にも多くのテーブルにデータを追加しているため、実データで持つのは避けられたら・・・と思っています。
48
+
49
+ ![イメージ説明](333663d3d2e755ca781ac0b53fa72ee3.png)
50
+
51
+
52
+
45
- クライアントアプリからは、以下を引数としたストアドファンクションを実行します。
53
+ 問い合わせの方法ですが、クライアントアプリから以下を引数としたストアドファンクションを実行します。
46
54
 
47
55
  - 作業ID
48
56
 
@@ -56,15 +64,7 @@
56
64
 
57
65
  - 開始可能日時
58
66
 
59
-
60
-
61
- 上記テーブルのデータはViewで以下の形式で持つことで、楽に問合せできると考えてます
67
+  ※開始日時一番早1件のみ(TOP 1かつORDER BY 開始日時 DESC)
62
-
63
- チームの時間帯毎の残リソース及び作業時間があれば、可能と考えました。
64
-
65
- タスク登録時には、既存の機能で他にも多くのテーブルにデータを追加しているため、実データで持つのは避けられたら・・・と思っています。
66
-
67
- ![イメージ説明](333663d3d2e755ca781ac0b53fa72ee3.png)
68
68
 
69
69
 
70
70
 
@@ -82,71 +82,339 @@
82
82
 
83
83
 
84
84
 
85
+ ### ソース
86
+
87
+ 2018/09/22:追記しました。
88
+
89
+ - チームマスタ
90
+
91
+ ```lang-SQL
92
+
93
+ CREATE TABLE TeamMaster
94
+
95
+ (
96
+
97
+ チームID varchar(10) NOT NULL,
98
+
99
+ 所有リソース decimal(5,2) NOT NULL
100
+
101
+ )
102
+
103
+ ON PRIMARY
104
+
105
+ ALTER TABLE TeamMaster ADD CONSTRAINT PK_TeamMaster
106
+
107
+ PRIMARY KEY (チームID)
108
+
109
+ ON PRIMARY
110
+
111
+
112
+
113
+ INSERT INTO TeamMaster (チームID, 所有リソース) VALUES ('A', 5)
114
+
115
+ ```
116
+
117
+
118
+
119
+ - 作業マスタ
120
+
121
+ ```lang-SQL
122
+
123
+ CREATE TABLE WorkMaster
124
+
125
+ (
126
+
127
+ 作業ID varchar(10) NOT NULL,
128
+
129
+ 作業名 varchar(20) NOT NULL,
130
+
131
+ 必要リソース decimal(4,2) NOT NULL
132
+
133
+ )
134
+
135
+ ON PRIMARY
136
+
137
+ ALTER TABLE WorkMaster ADD CONSTRAINT PK_WorkMaster
138
+
139
+ PRIMARY KEY (作業ID)
140
+
141
+ ON PRIMARY
142
+
143
+
144
+
145
+ INSERT INTO WorkMaster (作業ID, 作業名, 必要リソース) VALUES ('W1', 'Work1', 2)
146
+
147
+ INSERT INTO WorkMaster (作業ID, 作業名, 必要リソース) VALUES ('W2', 'Work2', 2)
148
+
149
+ ```
150
+
151
+
152
+
153
+ - チーム⇔作業紐付けマスタ
154
+
155
+ ```lang-SQL
156
+
157
+ CREATE TABLE Team_WorkMaster
158
+
159
+ (
160
+
161
+ チームID varchar(10) NOT NULL,
162
+
163
+ 作業ID varchar(10) NOT NULL
164
+
165
+ )
166
+
167
+ ON PRIMARY
168
+
169
+ ALTER TABLE Team_WorkMaster ADD CONSTRAINT PK_Team_WorkMaster
170
+
171
+ PRIMARY KEY (チームID, 作業ID)
172
+
173
+ ON PRIMARY
174
+
175
+
176
+
177
+ INSERT INTO Team_WorkMaster (チームID, 作業ID) VALUES ('A', 'W1')
178
+
179
+ INSERT INTO Team_WorkMaster (チームID, 作業ID) VALUES ('A', 'W2')
180
+
181
+ ```
182
+
183
+
184
+
185
+ - 計画済タスクトランザクション
186
+
187
+ ```lang-SQL
188
+
189
+ CREATE TABLE PlanedTaskTran
190
+
191
+ (
192
+
193
+ タスクID decimal(10,0) NOT NULL, --実際には自動採番を設定している
194
+
195
+ 作業ID varchar(10) NOT NULL,
196
+
197
+ 開始日時 datetime NOT NULL,
198
+
199
+ 終了日時 datetime NOT NULL
200
+
201
+ )
202
+
203
+ ON PRIMARY
204
+
205
+ ALTER TABLE PlanedTaskTran ADD CONSTRAINT PK_PlanedTaskTran
206
+
207
+ PRIMARY KEY (タスクID)
208
+
209
+ ON PRIMARY
210
+
211
+
212
+
213
+ INSERT INTO PlanedTaskTran (タスクID, 作業ID, 開始日時, 終了日時) VALUES (1, 'W1', '2018/09/21 10:00:00', '2018/09/21 15:00:00')
214
+
215
+ INSERT INTO PlanedTaskTran (タスクID, 作業ID, 開始日時, 終了日時) VALUES (2, 'W2', '2018/09/21 11:00:00', '2018/09/21 14:00:00')
216
+
217
+ ```
218
+
219
+
220
+
221
+
222
+
85
223
  ### 試したこと
86
224
 
87
- Viewの多段使用で近いところまではいったのですが、実際のソースは会社にありメモできていませんが、おおまかに以下のような流れで考えました。
225
+ ~~Viewの多段使用で近いところまではいったのですが、実際のソースは会社にありメモできていませんが、おおまかに以下のような流れで考えました。~~
88
-
226
+
89
- ```
227
+ 2018/09/22:誤記等もありましたので、修正しました。
90
-
228
+
229
+
230
+
91
- --1.一旦、日時情報を1カラムにまとめる
231
+ - 一旦、日時情報を1カラムにまとめる
232
+
233
+ ```SQL
234
+
235
+ CREATE VIEW tmp1
236
+
237
+ (種別, チームID, 作業ID, 所有リソース, 必要リソース, 日時)
238
+
239
+ AS
92
240
 
93
241
  SELECT
94
242
 
95
243
  1 AS 種別
96
244
 
97
- ,T.チームID
245
+ ,Team_WorkMaster.チームID
246
+
98
-
247
+ ,PlanedTaskTran.作業ID
248
+
99
- ,T.所有リソース
249
+ ,TeamMaster.所有リソース
100
-
250
+
101
- ,W.必要リソース
251
+ ,WorkMaster.必要リソース
102
-
252
+
103
- ,P.開始日時 AS 日時
253
+ ,PlanedTaskTran.開始日時 AS 日時
104
-
254
+
105
- FROM 計画済タスクトランザクション AS P
255
+ FROM PlanedTaskTran
106
-
256
+
107
- JOIN チーム⇔作業紐付けマスタ AS TW ON TW.作業ID = P.作業ID
257
+ JOIN Team_WorkMaster ON Team_WorkMaster.作業ID = PlanedTaskTran.作業ID
108
-
258
+
109
- JOIN 作業マスタ AS W ON W.作業ID = P.作業ID
259
+ JOIN WorkMaster ON WorkMaster.作業ID = PlanedTaskTran.作業ID
110
-
260
+
111
- JOIN チームマスタ AS T ON T.チームID = TW.チームID
261
+ JOIN TeamMaster ON TeamMaster.チームID = Team_WorkMaster.チームID
112
-
262
+
113
- UNION
263
+ UNION ALL --データは重複することはないため、UNION ALLに修正しました。
114
264
 
115
265
  SELECT
116
266
 
117
267
  -1
118
268
 
119
- ,T.チームID
120
-
121
- ,T.所有リソース
122
-
123
- ,W.必要リソース
124
-
125
- ,P.終了日時
126
-
127
- FROM 計画済タスクトランザクション AS P
128
-
129
- JOIN チーム⇔作業紐付けマスタ AS TW ON TW.作業ID = P.作業ID
130
-
131
- JOIN 作業マスタ AS W ON W.作業ID = P.作業ID
132
-
133
- JOIN チームマスタ AS T ON T.チームID = TW.チームID
134
-
135
-
136
-
137
- --2.種別の情報から、・・・すみませn、忘れました。
138
-
139
-
140
-
141
- --3.2で作成した情報を1レコードずらしてJOIN(FROM 2 JOIN 2 AS 2V ON 2V.日時 < 2.日時)し、
142
-
143
- -- 2V.日時を開始日時、2.日時を終了日時として情報を算出する。
144
-
145
-
146
-
147
- --4.3で作成した情報から、DATEDIFF(ss,開始日時,終了日時)で作業時間を算出する。
148
-
149
- ```
269
+ ,Team_WorkMaster.チームID
270
+
271
+ ,PlanedTaskTran.作業ID
272
+
273
+ ,TeamMaster.所有リソース
274
+
275
+ ,WorkMaster.必要リソース
276
+
277
+ ,PlanedTaskTran.開始日時
278
+
279
+ FROM PlanedTaskTran
280
+
281
+ JOIN Team_WorkMaster ON Team_WorkMaster.作業ID = PlanedTaskTran.作業ID
282
+
283
+ JOIN WorkMaster ON WorkMaster.作業ID = PlanedTaskTran.作業ID
284
+
285
+ JOIN TeamMaster ON TeamMaster.チームID = Team_WorkMaster.チームID
286
+
287
+ ```
288
+
289
+ |種別|チームID|作業ID|所有リソース|必要リソース|日時
290
+
291
+ |:--|:--|:--|:--|:--|:--|
292
+
293
+ |1|A|W1|5|2|2018/09/21 10:00:00|
294
+
295
+ |1|A|W2|5|2|2018/09/21 11:00:00|
296
+
297
+ |-1|A|W2|5|2|2018/09/21 14:00:00|
298
+
299
+ |-1|A|W1|5|2|2018/09/21 15:00:00|
300
+
301
+
302
+
303
+
304
+
305
+ - 日時毎の残リソースを算出する
306
+
307
+ ```lang-SQL
308
+
309
+ CREATE VIEW tmp2
310
+
311
+ (チームID, 日時, 残リソース)
312
+
313
+ AS
314
+
315
+ SELECT
316
+
317
+ tmp1.チームID
318
+
319
+ ,tmp1.日時
320
+
321
+ ,(SELECT
322
+
323
+ MAX(t.所有リソース) - (SUM(t.種別) * MAX(t.必要リソース))
324
+
325
+ FROM tmp1 AS t
326
+
327
+ WHERE t.日時 < tmp1.日時
328
+
329
+ OR (t.日時 = tmp1.日時 AND t.種別 <= tmp1.種別)) AS 残リソース
330
+
331
+ FROM tmp1
332
+
333
+ ```
334
+
335
+ |チームID|日時|残リソース|
336
+
337
+ |:--|:--|:--|
338
+
339
+ |A|2018/09/21 10:00:00|3|
340
+
341
+ |A|2018/09/21 11:00:00|1|
342
+
343
+ |A|2018/09/21 14:00:00|3|
344
+
345
+ |A|2018/09/21 15:00:00|5|
346
+
347
+
348
+
349
+
350
+
351
+ - 時間帯毎の残リソースを算出する
352
+
353
+ ```lang-SQL
354
+
355
+ CREATE VIEW RemainResource
356
+
357
+ (チームID, 開始日時, 終了日時, 残リソース, 作業可能時間)
358
+
359
+ AS
360
+
361
+ SELECT
362
+
363
+ tmp3.*
364
+
365
+ ,DATEDIFF(ss,tmp3.開始日時,tmp3.終了日時) AS 作業可能時間
366
+
367
+ FROM
368
+
369
+ (SELECT
370
+
371
+ MAX(tmp2.チームID) AS チームID
372
+
373
+ ,CASE
374
+
375
+ WHEN MAX(t.日時) IS NULL THEN
376
+
377
+ CONVERT(datetime, CONVERT(char(8), MAX(tmp2.日時), 112) + '06:00:00')
378
+
379
+ ELSE
380
+
381
+ MAX(t.日時)
382
+
383
+ END AS 開始日時
384
+
385
+ ,MAX(tmp2.日時) AS 終了日時
386
+
387
+ ,MAX(tmp2.残リソース) AS 残リソース
388
+
389
+ FROM tmp2
390
+
391
+ LEFT OUTER JOIN tmp2 AS t
392
+
393
+ ON t.日時 < tmp2.日時
394
+
395
+ GROUP BY tmp2.日時) AS tmp3
396
+
397
+ ```
398
+
399
+ |チームID|開始日時|終了日時|残リソース|作業可能時間|
400
+
401
+ |:--|:--|--:|--:|--:|
402
+
403
+ |A|2018/09/21 06:00:00|2018/09/21 10:00:00|5|14400|
404
+
405
+ |A|2018/09/21 10:00:00|2018/09/21 11:00:00|3|3600|
406
+
407
+ |A|2018/09/21 11:00:00|2018/09/21 14:00:00|1|10800|
408
+
409
+ |A|2018/09/21 14:00:00|2018/09/21 15:00:00|3|3600|
410
+
411
+
412
+
413
+
414
+
415
+ ※上記クエリでは、2018/09/21 15:00:00~2018/09/22 06:00:00までの残リソースが5という情報は出力できていません。
416
+
417
+
150
418
 
151
419
  かなり長々としたクエリになり、且つ計画済トランザクションのデータが多いとパフォーマンスにも影響を与えると思い、
152
420
 

1

文法の修正

2018/09/22 01:04

投稿

locoJr.
locoJr.

score15

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