teratail
回答率
85.48%
質問するログイン新規登録
トップSQL Serverに関する質問2つのテーブルから時間を足し算、引き算をして項目列ごとの合計がしたいです。

編集履歴

回答編集履歴

18

追記

2020/01/28 00:56

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -306,6 +306,8 @@
306
306
 
307
307
  ) step1
308
308
 
309
+ -- where XXX データの範囲を決定するとしたらここで。
310
+
309
311
  group by status
310
312
 
311
313
  ) step2

17

追記

2020/01/28 00:56

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -189,3 +189,125 @@
189
189
  データのパターンについて多くは検証していませんので、不備はあるかもしれませんが、そこは良しなにお願いします。
190
190
 
191
191
  一応質問のパターンについては、求める結果にはなっています。
192
+
193
+ 追記
194
+
195
+ --
196
+
197
+ tebleB が0件の場合の対応
198
+
199
+ ```SQL
200
+
201
+ with
202
+
203
+ tableA as(
204
+
205
+ select * from (values
206
+
207
+ (convert(datetime,'2020/01/05 06:00'),convert(datetime,'2020/01/05 09:00'),'運転')
208
+
209
+ ,(convert(datetime,'2020/01/05 09:00'),convert(datetime,'2020/01/05 11:00'),'停止')
210
+
211
+ ,(convert(datetime,'2020/01/05 11:00'),convert(datetime,'2020/01/05 14:00'),'運転')
212
+
213
+ ,(convert(datetime,'2020/01/05 14:00'),convert(datetime,'2020/01/05 16:00'),'停止')
214
+
215
+ ,(convert(datetime,'2020/01/05 16:00'),convert(datetime,'2020/01/05 17:00'),'運転')
216
+
217
+ ) as w(START_DATE,END_DATE,STATUS)
218
+
219
+ )
220
+
221
+ , tableB as (
222
+
223
+ select * from (values
224
+
225
+ (convert(datetime,'2020/01/05 06:00'),convert(datetime,'2020/01/05 07:00'),'休憩')
226
+
227
+ ,(convert(datetime,'2020/01/05 08:00'),convert(datetime,'2020/01/05 12:00'),'休憩')
228
+
229
+ ,(convert(datetime,'2020/01/05 13:00'),convert(datetime,'2020/01/05 15:00'),'休憩')
230
+
231
+ ) as w(START_DATE,END_DATE,STATUS)
232
+
233
+ )
234
+
235
+ , gap1 as (
236
+
237
+ select *
238
+
239
+ , lag(end_date) over(order by start_date) lag_end_date
240
+
241
+ , lead(start_Date) over(order by start_date) as lead_start_date
242
+
243
+ from tableB
244
+
245
+ )
246
+
247
+ , gap as (
248
+
249
+ select lag_end_date as start_date, start_Date as end_date
250
+
251
+ from gap1
252
+
253
+ where lag_end_date is not null
254
+
255
+ union all
256
+
257
+ select CONVERT(DATETIME, '1900/01/01 00:00:00'), start_Date
258
+
259
+ from gap1
260
+
261
+ where lag_end_date is null --休憩の最小データの外側
262
+
263
+ union all
264
+
265
+ select end_date, CONVERT(DATETIME, '9999/12/31 23:59:59')
266
+
267
+ from gap1
268
+
269
+ where lead_start_date is null --休憩の最大データの外側
270
+
271
+ )
272
+
273
+ select status
274
+
275
+ , format((total_minutes / 60) * 100 + total_minutes % 60, '00:00') total_time
276
+
277
+ from (
278
+
279
+ select status
280
+
281
+ , sum(datediff(mi,start_date,end_date)) total_minutes
282
+
283
+ from (
284
+
285
+ select case when b.start_date > a.start_Date
286
+
287
+ then b.start_date else a.start_date
288
+
289
+ end as start_date
290
+
291
+ , case when b.end_date < a.end_date
292
+
293
+ then b.end_date else a.end_date
294
+
295
+ end as end_date
296
+
297
+ , a.status
298
+
299
+ from tableA a left join gap b
300
+
301
+ on a.start_date<b.end_date and a.end_date>b.start_date
302
+
303
+ union all
304
+
305
+ select start_date, end_date, status from gap1
306
+
307
+ ) step1
308
+
309
+ group by status
310
+
311
+ ) step2
312
+
313
+ ```

16

変更

2020/01/27 23:51

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -176,7 +176,7 @@
176
176
 
177
177
  union all
178
178
 
179
- select * from tableb
179
+ select start_date, end_date, status from gap1
180
180
 
181
181
  ) step1
182
182
 

15

変更

2020/01/27 13:50

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -116,7 +116,7 @@
116
116
 
117
117
  union all
118
118
 
119
- select CONVERT(DATETIME, '1900/01/01 00:00:00') as start_date, start_Date as end_date
119
+ select CONVERT(DATETIME, '1900/01/01 00:00:00'), start_Date
120
120
 
121
121
  from gap1
122
122
 
@@ -124,11 +124,23 @@
124
124
 
125
125
  union all
126
126
 
127
- select end_date as start_date, CONVERT(DATETIME, '9999/12/31 23:59:59') as end_date
127
+ select end_date, CONVERT(DATETIME, '9999/12/31 23:59:59')
128
128
 
129
129
  from gap1
130
130
 
131
131
  where lead_start_date is null --休憩の最大データの外側
132
+
133
+ union all
134
+
135
+ select *
136
+
137
+ from (values
138
+
139
+ (CONVERT(DATETIME, '1900/01/01 00:00:00'), CONVERT(DATETIME, '9999/12/31 23:59:59'))
140
+
141
+ ) as w(start_date, end_date)
142
+
143
+ where not exists(select 1 from gap1) --gap1のデータが無い時
132
144
 
133
145
  )
134
146
 
@@ -158,7 +170,7 @@
158
170
 
159
171
  , a.status
160
172
 
161
- from tableA a left join gap b
173
+ from tableA a inner join gap b
162
174
 
163
175
  on a.start_date<b.end_date and a.end_date>b.start_date
164
176
 

14

修正

2020/01/27 13:42

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -144,21 +144,21 @@
144
144
 
145
145
  from (
146
146
 
147
- select case when b.start_date < a.start_Date
147
+ select case when b.start_date > a.start_Date
148
148
 
149
- then a.start_date else b.start_date
149
+ then b.start_date else a.start_date
150
150
 
151
151
  end as start_date
152
152
 
153
- , case when b.end_date > a.end_date
153
+ , case when b.end_date < a.end_date
154
154
 
155
- then a.end_date else b.end_date
155
+ then b.end_date else a.end_date
156
156
 
157
157
  end as end_date
158
158
 
159
159
  , a.status
160
160
 
161
- from tableA a inner join gap b
161
+ from tableA a left join gap b
162
162
 
163
163
  on a.start_date<b.end_date and a.end_date>b.start_date
164
164
 

13

推敲

2020/01/27 10:31

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -160,9 +160,7 @@
160
160
 
161
161
  from tableA a inner join gap b
162
162
 
163
- on a.start_date<=b.end_date and a.end_date>=b.start_date
163
+ on a.start_date<b.end_date and a.end_date>b.start_date
164
-
165
- and b.end_date!=a.start_date and b.start_date!=a.end_date
166
164
 
167
165
  union all
168
166
 

12

推敲

2020/01/25 01:47

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -116,7 +116,7 @@
116
116
 
117
117
  union all
118
118
 
119
- select CONVERT(DATETIME, '19000101 00:00:00') as start_date, start_Date as end_date
119
+ select CONVERT(DATETIME, '1900/01/01 00:00:00') as start_date, start_Date as end_date
120
120
 
121
121
  from gap1
122
122
 
@@ -124,7 +124,7 @@
124
124
 
125
125
  union all
126
126
 
127
- select end_date as start_date, CONVERT(DATETIME, '99991231 23:59:59') as end_date
127
+ select end_date as start_date, CONVERT(DATETIME, '9999/12/31 23:59:59') as end_date
128
128
 
129
129
  from gap1
130
130
 

11

追記

2020/01/25 01:42

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -176,6 +176,6 @@
176
176
 
177
177
  ```
178
178
 
179
- 但し、休憩の期間に完全に重なるTableAのデータには、多分対応できていません。
179
+ データのパターンについて多くは検証していませんので、不備はあるかもしれませんが、そこは良しなにお願いします。
180
180
 
181
- その他にも不備はあるかもしれませんが、そこは良しなにお願いします。
181
+ 一応質問のパターンについては、求める結果にはなっています。

10

データを含めるように変更

2020/01/25 01:29

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -60,7 +60,41 @@
60
60
 
61
61
  ```SQL
62
62
 
63
+ with
64
+
65
+ tableA as(
66
+
67
+ select * from (values
68
+
69
+ (convert(datetime,'2020/01/05 06:00'),convert(datetime,'2020/01/05 09:00'),'運転')
70
+
71
+ ,(convert(datetime,'2020/01/05 09:00'),convert(datetime,'2020/01/05 11:00'),'停止')
72
+
73
+ ,(convert(datetime,'2020/01/05 11:00'),convert(datetime,'2020/01/05 14:00'),'運転')
74
+
75
+ ,(convert(datetime,'2020/01/05 14:00'),convert(datetime,'2020/01/05 16:00'),'停止')
76
+
77
+ ,(convert(datetime,'2020/01/05 16:00'),convert(datetime,'2020/01/05 17:00'),'運転')
78
+
79
+ ) as w(START_DATE,END_DATE,STATUS)
80
+
81
+ )
82
+
83
+ , tableB as (
84
+
85
+ select * from (values
86
+
87
+ (convert(datetime,'2020/01/05 06:00'),convert(datetime,'2020/01/05 07:00'),'休憩')
88
+
89
+ ,(convert(datetime,'2020/01/05 08:00'),convert(datetime,'2020/01/05 12:00'),'休憩')
90
+
91
+ ,(convert(datetime,'2020/01/05 13:00'),convert(datetime,'2020/01/05 15:00'),'休憩')
92
+
93
+ ) as w(START_DATE,END_DATE,STATUS)
94
+
95
+ )
96
+
63
- with gap1 as (
97
+ , gap1 as (
64
98
 
65
99
  select *
66
100
 
@@ -98,39 +132,47 @@
98
132
 
99
133
  )
100
134
 
101
- select status
135
+ select status
102
136
 
103
- , sum(end_date - start_date) total_time
137
+ , format((total_minutes / 60) * 100 + total_minutes % 60, '00:00') total_time
104
138
 
105
139
  from (
106
140
 
107
- select case when b.start_date < a.start_Date
141
+ select status
108
142
 
109
- then a.start_date else b.start_date
143
+ , sum(datediff(mi,start_date,end_date)) total_minutes
110
144
 
111
- end as start_date
145
+ from (
112
146
 
113
- , case when b.end_date > a.end_date
147
+ select case when b.start_date < a.start_Date
114
148
 
115
- then a.end_date else b.end_date
149
+ then a.start_date else b.start_date
116
150
 
117
- end as end_date
151
+ end as start_date
118
152
 
119
- , a.status
153
+ , case when b.end_date > a.end_date
120
154
 
121
- from tableA a inner join gap b
155
+ then a.end_date else b.end_date
122
156
 
123
- on a.start_date<=b.end_date and a.end_date>=b.start_date
157
+ end as end_date
124
158
 
125
- and b.end_date!=a.start_date and b.start_date!=a.end_date
159
+ , a.status
126
160
 
127
- union all
161
+ from tableA a inner join gap b
128
162
 
129
- select * from tableb
163
+ on a.start_date<=b.end_date and a.end_date>=b.start_date
130
164
 
131
- ) t1
165
+ and b.end_date!=a.start_date and b.start_date!=a.end_date
132
166
 
167
+ union all
168
+
169
+ select * from tableb
170
+
171
+ ) step1
172
+
133
- group by status
173
+ group by status
174
+
175
+ ) step2
134
176
 
135
177
  ```
136
178
 

9

推敲

2020/01/25 01:19

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -134,4 +134,6 @@
134
134
 
135
135
  ```
136
136
 
137
+ 但し、休憩の期間に完全に重なるTableAのデータには、多分対応できていません。
138
+
137
- 不備はあるかもしれませんが、そこは良しなにお願いします。
139
+ その他にも不備はあるかもしれませんが、そこは良しなにお願いします。

8

推敲

2020/01/24 12:04

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -134,6 +134,4 @@
134
134
 
135
135
  ```
136
136
 
137
- 但し、上記はTableBの時間帯に完全に含まれるTableAのデータを除外する事には対応していません。
138
-
139
- その他にも不備はあるかもしれませんが、そこは良しなにお願いします。
137
+ 不備はあるかもしれませんが、そこは良しなにお願いします。

7

訂正

2020/01/24 12:01

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -90,7 +90,7 @@
90
90
 
91
91
  union all
92
92
 
93
- select end_date as start_date, CONVERT(DATETIME, '99991231 24:59:59') as end_date
93
+ select end_date as start_date, CONVERT(DATETIME, '99991231 23:59:59') as end_date
94
94
 
95
95
  from gap1
96
96
 

6

訂正

2020/01/24 11:55

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -90,7 +90,7 @@
90
90
 
91
91
  union all
92
92
 
93
- select end_date as start_date, CONVERT(DATETIME, '99991231 59:59:59') as end_date
93
+ select end_date as start_date, CONVERT(DATETIME, '99991231 24:59:59') as end_date
94
94
 
95
95
  from gap1
96
96
 

5

追記

2020/01/24 11:55

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -41,3 +41,99 @@
41
41
  group by status
42
42
 
43
43
  ```
44
+
45
+ 追記
46
+
47
+ --
48
+
49
+ tableBから休憩でない時間帯を行の隙間と行の外側について求めます。
50
+
51
+ 隙間についてはlag()/lead()どちらを利用しても良いですが、サンプルではlag()を使用しました。
52
+
53
+ 外側については日付が取りうる最小と最大の値を用いてunionで生成しています。
54
+
55
+
56
+
57
+ このクエリー(gap)とtableAを突合して、休憩以外の開始/終了を調整します。
58
+
59
+ それとTableBをunionしたものに対して集計します。
60
+
61
+ ```SQL
62
+
63
+ with gap1 as (
64
+
65
+ select *
66
+
67
+ , lag(end_date) over(order by start_date) lag_end_date
68
+
69
+ , lead(start_Date) over(order by start_date) as lead_start_date
70
+
71
+ from tableB
72
+
73
+ )
74
+
75
+ , gap as (
76
+
77
+ select lag_end_date as start_date, start_Date as end_date
78
+
79
+ from gap1
80
+
81
+ where lag_end_date is not null
82
+
83
+ union all
84
+
85
+ select CONVERT(DATETIME, '19000101 00:00:00') as start_date, start_Date as end_date
86
+
87
+ from gap1
88
+
89
+ where lag_end_date is null --休憩の最小データの外側
90
+
91
+ union all
92
+
93
+ select end_date as start_date, CONVERT(DATETIME, '99991231 59:59:59') as end_date
94
+
95
+ from gap1
96
+
97
+ where lead_start_date is null --休憩の最大データの外側
98
+
99
+ )
100
+
101
+ select status
102
+
103
+ , sum(end_date - start_date) total_time
104
+
105
+ from (
106
+
107
+ select case when b.start_date < a.start_Date
108
+
109
+ then a.start_date else b.start_date
110
+
111
+ end as start_date
112
+
113
+ , case when b.end_date > a.end_date
114
+
115
+ then a.end_date else b.end_date
116
+
117
+ end as end_date
118
+
119
+ , a.status
120
+
121
+ from tableA a inner join gap b
122
+
123
+ on a.start_date<=b.end_date and a.end_date>=b.start_date
124
+
125
+ and b.end_date!=a.start_date and b.start_date!=a.end_date
126
+
127
+ union all
128
+
129
+ select * from tableb
130
+
131
+ ) t1
132
+
133
+ group by status
134
+
135
+ ```
136
+
137
+ 但し、上記はTableBの時間帯に完全に含まれるTableAのデータを除外する事には対応していません。
138
+
139
+ その他にも不備はあるかもしれませんが、そこは良しなにお願いします。

4

再考

2020/01/24 10:51

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -41,43 +41,3 @@
41
41
  group by status
42
42
 
43
43
  ```
44
-
45
- 追記
46
-
47
- --
48
-
49
- 一つの休憩以外のデータに対し、複数の休憩があるという事なので、Join対応版。
50
-
51
- ```SQL
52
-
53
- select status
54
-
55
- , sum(end_date - start_date) total_time
56
-
57
- from (
58
-
59
- select case when A.start_date < B.end_date then B.end_date else A.start_date end as start_date
60
-
61
- , case when A.end_date > B.start_date then B.start_date else A.end_Date end as end_date
62
-
63
- , A.status
64
-
65
- from tableA A left join table B
66
-
67
- on B.start_date<=A.end_date and B.end_date>=A.start_date
68
-
69
- union all
70
-
71
- select start_date
72
-
73
- , end_date
74
-
75
- , status
76
-
77
- from tableB
78
-
79
- ) t1
80
-
81
- group by status
82
-
83
- ```

3

推敲

2020/01/24 05:54

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -76,7 +76,7 @@
76
76
 
77
77
  from tableB
78
78
 
79
- ) t2
79
+ ) t1
80
80
 
81
81
  group by status
82
82
 

2

追記

2020/01/24 05:49

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -41,3 +41,43 @@
41
41
  group by status
42
42
 
43
43
  ```
44
+
45
+ 追記
46
+
47
+ --
48
+
49
+ 一つの休憩以外のデータに対し、複数の休憩があるという事なので、Join対応版。
50
+
51
+ ```SQL
52
+
53
+ select status
54
+
55
+ , sum(end_date - start_date) total_time
56
+
57
+ from (
58
+
59
+ select case when A.start_date < B.end_date then B.end_date else A.start_date end as start_date
60
+
61
+ , case when A.end_date > B.start_date then B.start_date else A.end_Date end as end_date
62
+
63
+ , A.status
64
+
65
+ from tableA A left join table B
66
+
67
+ on B.start_date<=A.end_date and B.end_date>=A.start_date
68
+
69
+ union all
70
+
71
+ select start_date
72
+
73
+ , end_date
74
+
75
+ , status
76
+
77
+ from tableB
78
+
79
+ ) t2
80
+
81
+ group by status
82
+
83
+ ```

1

追記

2020/01/24 05:48

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -1 +1,43 @@
1
1
  TABLEAのデータに対して休憩を差し引いたもの（差し引く休憩はサブクエリーで取得）とTableBをunionしたものを集計すれば良さそうです。
2
+
3
+
4
+
5
+ start_dateとend_dateを差し替える場合の値は、調整が必要かもしれませんが、こんな感じかと思います。
6
+
7
+ ```SQL
8
+
9
+ select status
10
+
11
+ , sum(end_date - start_date) total_time
12
+
13
+ from (
14
+
15
+ select case when start_date < B_end_date then B_end_date else start_Date end as start_date
16
+
17
+ , case when end_date > B_start_date then B_start_date else end_Date end as end_date
18
+
19
+ , status
20
+
21
+ from (
22
+
23
+ select A.*
24
+
25
+ , (select start_date from tableB where start_date<=A.end_date and end_date>=A.start_date) as B_start_date
26
+
27
+ , (select end_date from tableB where start_date<=A.end_date and end_date>=A.start_date) as B_end_date
28
+
29
+ from tableA A
30
+
31
+ ) t1
32
+
33
+ union all
34
+
35
+ select start_date, end_date, status
36
+
37
+ from tableB
38
+
39
+ ) t2
40
+
41
+ group by status
42
+
43
+ ```