前提・実現したいこと
SQL Serverを使用し、2つのテーブルから時間を足し算、引き算をしてSTATUS列ごとの合計がしたいです。
例、
TABLE_A
START_DATE | END_DATE | STATUS |
---|---|---|
2020/01/05 06:00 | 2020/01/05 09:00 | 運転 |
2020/01/05 09:00 | 2020/01/05 11:00 | 停止 |
2020/01/05 11:00 | 2020/01/05 14:00 | 運転 |
2020/01/05 14:00 | 2020/01/05 16:00 | 停止 |
2020/01/05 16:00 | 2020/01/05 17:00 | 運転 |
TABLE_B
START_DATE | END_DATE | STATUS |
---|---|---|
2020/01/05 06:00 | 2020/01/05 07:00 | 休憩 |
2020/01/05 08:00 | 2020/01/05 12:00 | 休憩 |
20/01/05 13:00 | 2020/01/05 15:00 | 休憩 |
STATUS | TOTAL_TIME |
---|---|
運転 | 3:00 |
停止 | 1:00 |
休憩 | 7:00 |
補足情報(FW/ツールのバージョンなど)
SQL Server 2016
気になる質問をクリップする
クリップした質問は、後からいつでもMYページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
回答2件
0
ベストアンサー
TABLEAのデータに対して休憩を差し引いたもの(差し引く休憩はサブクエリーで取得)とTableBをunionしたものを集計すれば良さそうです。
start_dateとend_dateを差し替える場合の値は、調整が必要かもしれませんが、こんな感じかと思います。
SQL
1select status 2 , sum(end_date - start_date) total_time 3from ( 4 select case when start_date < B_end_date then B_end_date else start_Date end as start_date 5 , case when end_date > B_start_date then B_start_date else end_Date end as end_date 6 , status 7 from ( 8 select A.* 9 , (select start_date from tableB where start_date<=A.end_date and end_date>=A.start_date) as B_start_date 10 , (select end_date from tableB where start_date<=A.end_date and end_date>=A.start_date) as B_end_date 11 from tableA A 12 ) t1 13 union all 14 select start_date, end_date, status 15 from tableB 16) t2 17group by status
追記
tableBから休憩でない時間帯を行の隙間と行の外側について求めます。
隙間についてはlag()/lead()どちらを利用しても良いですが、サンプルではlag()を使用しました。
外側については日付が取りうる最小と最大の値を用いてunionで生成しています。
このクエリー(gap)とtableAを突合して、休憩以外の開始/終了を調整します。
それとTableBをunionしたものに対して集計します。
SQL
1with 2 tableA as( 3 select * from (values 4 (convert(datetime,'2020/01/05 06:00'),convert(datetime,'2020/01/05 09:00'),'運転') 5 ,(convert(datetime,'2020/01/05 09:00'),convert(datetime,'2020/01/05 11:00'),'停止') 6 ,(convert(datetime,'2020/01/05 11:00'),convert(datetime,'2020/01/05 14:00'),'運転') 7 ,(convert(datetime,'2020/01/05 14:00'),convert(datetime,'2020/01/05 16:00'),'停止') 8 ,(convert(datetime,'2020/01/05 16:00'),convert(datetime,'2020/01/05 17:00'),'運転') 9 ) as w(START_DATE,END_DATE,STATUS) 10 ) 11, tableB as ( 12 select * from (values 13 (convert(datetime,'2020/01/05 06:00'),convert(datetime,'2020/01/05 07:00'),'休憩') 14 ,(convert(datetime,'2020/01/05 08:00'),convert(datetime,'2020/01/05 12:00'),'休憩') 15 ,(convert(datetime,'2020/01/05 13:00'),convert(datetime,'2020/01/05 15:00'),'休憩') 16 ) as w(START_DATE,END_DATE,STATUS) 17 ) 18, gap1 as ( 19 select * 20 , lag(end_date) over(order by start_date) lag_end_date 21 , lead(start_Date) over(order by start_date) as lead_start_date 22 from tableB 23 ) 24, gap as ( 25 select lag_end_date as start_date, start_Date as end_date 26 from gap1 27 where lag_end_date is not null 28 union all 29 select CONVERT(DATETIME, '1900/01/01 00:00:00'), start_Date 30 from gap1 31 where lag_end_date is null --休憩の最小データの外側 32 union all 33 select end_date, CONVERT(DATETIME, '9999/12/31 23:59:59') 34 from gap1 35 where lead_start_date is null --休憩の最大データの外側 36 union all 37 select * 38 from (values 39 (CONVERT(DATETIME, '1900/01/01 00:00:00'), CONVERT(DATETIME, '9999/12/31 23:59:59')) 40 ) as w(start_date, end_date) 41 where not exists(select 1 from gap1) --gap1のデータが無い時 42) 43select status 44 , format((total_minutes / 60) * 100 + total_minutes % 60, '00:00') total_time 45from ( 46 select status 47 , sum(datediff(mi,start_date,end_date)) total_minutes 48 from ( 49 select case when b.start_date > a.start_Date 50 then b.start_date else a.start_date 51 end as start_date 52 , case when b.end_date < a.end_date 53 then b.end_date else a.end_date 54 end as end_date 55 , a.status 56 from tableA a inner join gap b 57 on a.start_date<b.end_date and a.end_date>b.start_date 58 union all 59 select start_date, end_date, status from gap1 60 ) step1 61 group by status 62) step2
データのパターンについて多くは検証していませんので、不備はあるかもしれませんが、そこは良しなにお願いします。
一応質問のパターンについては、求める結果にはなっています。
追記
tebleB が0件の場合の対応
SQL
1with 2 tableA as( 3 select * from (values 4 (convert(datetime,'2020/01/05 06:00'),convert(datetime,'2020/01/05 09:00'),'運転') 5 ,(convert(datetime,'2020/01/05 09:00'),convert(datetime,'2020/01/05 11:00'),'停止') 6 ,(convert(datetime,'2020/01/05 11:00'),convert(datetime,'2020/01/05 14:00'),'運転') 7 ,(convert(datetime,'2020/01/05 14:00'),convert(datetime,'2020/01/05 16:00'),'停止') 8 ,(convert(datetime,'2020/01/05 16:00'),convert(datetime,'2020/01/05 17:00'),'運転') 9 ) as w(START_DATE,END_DATE,STATUS) 10 ) 11, tableB as ( 12 select * from (values 13 (convert(datetime,'2020/01/05 06:00'),convert(datetime,'2020/01/05 07:00'),'休憩') 14 ,(convert(datetime,'2020/01/05 08:00'),convert(datetime,'2020/01/05 12:00'),'休憩') 15 ,(convert(datetime,'2020/01/05 13:00'),convert(datetime,'2020/01/05 15:00'),'休憩') 16 ) as w(START_DATE,END_DATE,STATUS) 17 ) 18, gap1 as ( 19 select * 20 , lag(end_date) over(order by start_date) lag_end_date 21 , lead(start_Date) over(order by start_date) as lead_start_date 22 from tableB 23 ) 24, gap as ( 25 select lag_end_date as start_date, start_Date as end_date 26 from gap1 27 where lag_end_date is not null 28 union all 29 select CONVERT(DATETIME, '1900/01/01 00:00:00'), start_Date 30 from gap1 31 where lag_end_date is null --休憩の最小データの外側 32 union all 33 select end_date, CONVERT(DATETIME, '9999/12/31 23:59:59') 34 from gap1 35 where lead_start_date is null --休憩の最大データの外側 36) 37select status 38 , format((total_minutes / 60) * 100 + total_minutes % 60, '00:00') total_time 39from ( 40 select status 41 , sum(datediff(mi,start_date,end_date)) total_minutes 42 from ( 43 select case when b.start_date > a.start_Date 44 then b.start_date else a.start_date 45 end as start_date 46 , case when b.end_date < a.end_date 47 then b.end_date else a.end_date 48 end as end_date 49 , a.status 50 from tableA a left join gap b 51 on a.start_date<b.end_date and a.end_date>b.start_date 52 union all 53 select start_date, end_date, status from gap1 54 ) step1 55 -- where XXX データの範囲を決定するとしたらここで。 56 group by status 57) step2
投稿2020/01/24 03:22
編集2020/01/28 00:56総合スコア25173
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2020/01/24 05:31
2020/01/24 08:10 編集
2020/01/24 08:33
2020/01/24 09:34
2020/01/25 01:19
2020/01/27 00:51
2020/01/27 10:01
2020/01/27 23:53 編集
2020/01/27 23:54 編集
2020/01/28 00:02
0
デバッグすらしてませんが、だいたいこんな感じで運転中の休憩時間は出せると思います。
SQL
1SELECT SUM( 2 CASE WHEN a.END_DATE > b.END_DATE THEN b.END_DATE ELSE a.END_DATE END 3 - CASE WHEN a.START_DATE > b.START_DATE THEN a.START_DATE ELSE b.START END)) AS 運転中の休憩 4FROM TABLE_A AS a 5INNER JOIN TABLE_B AS b 6ON a.START_DATE <= b.END_DATE AND a.END_DATE >= b.START_DATE 7WHERE a.STATUS = "運転"
運転時間の合計から引けば休憩じゃない運転時間は出ると思います。
SQL Serverじゃなかったらもうちょっとすっきり書けるのですが…
投稿2020/01/24 04:47
総合スコア4150
あなたの回答
tips
太字
斜体
打ち消し線
見出し
引用テキストの挿入
コードの挿入
リンクの挿入
リストの挿入
番号リストの挿入
表の挿入
水平線の挿入
プレビュー
質問の解決につながる回答をしましょう。 サンプルコードなど、より具体的な説明があると質問者の理解の助けになります。 また、読む側のことを考えた、分かりやすい文章を心がけましょう。