2つのテーブルから時間を足し算、引き算をして項目列ごとの合計がしたいです。

Question

前提・実現したいこと

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

Comments

[hihijiji]

START_DATE、END_DATEには時間未満(分、秒など)は無いと思っていいのですか？

[hihijiji]

休憩でも運転でも停止でも無い時間はありますか？

[zardpray]

すみません情報不足でした。
実際は、分、秒まではあります。
よろしくお願いいたします。

[zardpray]

実際は、未接続、異常停止もあります。
よろしくお願いいたします。

Answer 1

TABLEAのデータに対して休憩を差し引いたもの（差し引く休憩はサブクエリーで取得）とTableBをunionしたものを集計すれば良さそうです。

start_dateとend_dateを差し替える場合の値は、調整が必要かもしれませんが、こんな感じかと思います。

select status
     , sum(end_date - start_date) total_time
from (
    select case when start_date < B_end_date then B_end_date else start_Date end as start_date
         , case when end_date > B_start_date then B_start_date else end_Date end as end_date
         , status
    from (
          select A.*
              , (select start_date from tableB where start_date<=A.end_date and end_date>=A.start_date) as B_start_date
              , (select end_date from tableB where start_date<=A.end_date and end_date>=A.start_date) as B_end_date
          from tableA A
         ) t1
  union all
    select start_date, end_date, status
    from tableB
) t2
group by status

追記

tableBから休憩でない時間帯を行の隙間と行の外側について求めます。
隙間についてはlag()/lead()どちらを利用しても良いですが、サンプルではlag()を使用しました。
外側については日付が取りうる最小と最大の値を用いてunionで生成しています。

このクエリー(gap)とtableAを突合して、休憩以外の開始/終了を調整します。
それとTableBをunionしたものに対して集計します。

with 
  tableA as(
    select * from (values 
     (convert(datetime,'2020/01/05 06:00'),convert(datetime,'2020/01/05 09:00'),'運転')
    ,(convert(datetime,'2020/01/05 09:00'),convert(datetime,'2020/01/05 11:00'),'停止')
    ,(convert(datetime,'2020/01/05 11:00'),convert(datetime,'2020/01/05 14:00'),'運転')
    ,(convert(datetime,'2020/01/05 14:00'),convert(datetime,'2020/01/05 16:00'),'停止')
    ,(convert(datetime,'2020/01/05 16:00'),convert(datetime,'2020/01/05 17:00'),'運転')
    ) as w(START_DATE,END_DATE,STATUS)
  )
, tableB as (
    select * from (values 
     (convert(datetime,'2020/01/05 06:00'),convert(datetime,'2020/01/05 07:00'),'休憩')
    ,(convert(datetime,'2020/01/05 08:00'),convert(datetime,'2020/01/05 12:00'),'休憩')
    ,(convert(datetime,'2020/01/05 13:00'),convert(datetime,'2020/01/05 15:00'),'休憩')
    ) as w(START_DATE,END_DATE,STATUS)
  )
, gap1 as (
    select *
         , lag(end_date) over(order by start_date) lag_end_date
         , lead(start_Date) over(order by start_date) as lead_start_date
    from tableB
 )
, gap as (
     select lag_end_date as start_date, start_Date as end_date 
     from gap1
     where lag_end_date is not null
   union all
     select CONVERT(DATETIME, '1900/01/01 00:00:00'), start_Date
     from gap1
     where lag_end_date is null --休憩の最小データの外側
   union all
     select end_date, CONVERT(DATETIME, '9999/12/31 23:59:59') 
     from gap1
     where lead_start_date is null --休憩の最大データの外側
   union all
     select *
     from (values
	        (CONVERT(DATETIME, '1900/01/01 00:00:00'), CONVERT(DATETIME, '9999/12/31 23:59:59'))
	      ) as w(start_date, end_date) 
     where not exists(select 1 from gap1) --gap1のデータが無い時
)
select  status
      , format((total_minutes / 60) * 100 + total_minutes % 60, '00:00') total_time
from (
  select status
       , sum(datediff(mi,start_date,end_date)) total_minutes
  from (
        select  case when b.start_date > a.start_Date 
                  then b.start_date else a.start_date 
                end as start_date
              , case when b.end_date < a.end_date 
                  then b.end_date else a.end_date  
                end as end_date
              , a.status
        from  tableA a inner join gap b 
              on   a.start_date<b.end_date and a.end_date>b.start_date
      union all
        select start_date, end_date, status from gap1
  ) step1
  group by status
) step2

データのパターンについて多くは検証していませんので、不備はあるかもしれませんが、そこは良しなにお願いします。
一応質問のパターンについては、求める結果にはなっています。
追記

tebleB が0件の場合の対応

with 
  tableA as(
    select * from (values 
     (convert(datetime,'2020/01/05 06:00'),convert(datetime,'2020/01/05 09:00'),'運転')
    ,(convert(datetime,'2020/01/05 09:00'),convert(datetime,'2020/01/05 11:00'),'停止')
    ,(convert(datetime,'2020/01/05 11:00'),convert(datetime,'2020/01/05 14:00'),'運転')
    ,(convert(datetime,'2020/01/05 14:00'),convert(datetime,'2020/01/05 16:00'),'停止')
    ,(convert(datetime,'2020/01/05 16:00'),convert(datetime,'2020/01/05 17:00'),'運転')
    ) as w(START_DATE,END_DATE,STATUS)
  )
, tableB as (
    select * from (values 
     (convert(datetime,'2020/01/05 06:00'),convert(datetime,'2020/01/05 07:00'),'休憩')
    ,(convert(datetime,'2020/01/05 08:00'),convert(datetime,'2020/01/05 12:00'),'休憩')
    ,(convert(datetime,'2020/01/05 13:00'),convert(datetime,'2020/01/05 15:00'),'休憩')
    ) as w(START_DATE,END_DATE,STATUS)
  )
, gap1 as (
    select *
         , lag(end_date) over(order by start_date) lag_end_date
         , lead(start_Date) over(order by start_date) as lead_start_date
    from tableB
 )
, gap as (
     select lag_end_date as start_date, start_Date as end_date 
     from gap1
     where lag_end_date is not null
   union all
     select CONVERT(DATETIME, '1900/01/01 00:00:00'), start_Date
     from gap1
     where lag_end_date is null --休憩の最小データの外側
   union all
     select end_date, CONVERT(DATETIME, '9999/12/31 23:59:59') 
     from gap1
     where lead_start_date is null --休憩の最大データの外側
)
select  status
      , format((total_minutes / 60) * 100 + total_minutes % 60, '00:00') total_time
from (
  select status
       , sum(datediff(mi,start_date,end_date)) total_minutes
  from (
        select  case when b.start_date > a.start_Date 
                  then b.start_date else a.start_date 
                end as start_date
              , case when b.end_date < a.end_date 
                  then b.end_date else a.end_date  
                end as end_date
              , a.status
        from  tableA a left join gap b 
              on   a.start_date<b.end_date and a.end_date>b.start_date
      union all
        select start_date, end_date, status from gap1
  ) step1
  -- where  XXX データの範囲を決定するとしたらここで。
  group by status
) step2

Comments

[zardpray]

ご回答ありがとうございます。
すみません。t1のところを抜き出して動かしてみましたら
「サブクエリは複数の値を返しました。」と表示されます。
TABLE_Aに対しTABLE_Bが複数返すので起きていると思われます。
何か対処法はありませんでしょうか
よろしくお願いいたします。

[sazi]

よく考えるとSQLのみでは結構難題ですね。
なにが難題かというと、tableAのデータをTableBの状態によって分割（行を増やす）しないと駄目なところです。
再帰用いてできるかどうかって所ですね。

[sazi]

lagとかlead使えば良さそうですね。
ちょっと考えてみます。

[zardpray]

ありがとうございます。
自分も考えてみます。
よろしくお願いいたします。

[sazi]

そのまま実行できるように、テーブルデータをcteで展開しました。

[zardpray]

回答ありがとうございます。
実際に試してみたら上手く動作いたしました。
私は、最初に休憩時間と重なる時間を出して最後に合計という流れでSQL文を作っていましたが
上手くいかず。
saziさんのlag,lead関数を使って休憩外の時間で計算して驚きました。
ありがとうございました。

[zardpray]

TABLE_Bの休憩データを日付で絞り込み、レコードが0件になった場合に、TABLE＿Aレコードが表示されなくなりました。（WITH gap1 ASの所に日付絞り込み条件追加しました。）
何か対処法がありませんでしょうか？

[sazi]

inner join をleft join に変更し、開始日/終了日の調整用のCASEを反転させ、集計時にgap1参照するように変更した版を追記ました。

[sazi]

gapにgap1がデータなしの場合に全期間となるデータをunionで追記するように2番目のsqlを修正。
※一応最後でtable_bをunionする箇所もgap1に変更
ただ、「TABLE_Bの休憩データを日付で絞り込み」というのは条件とする場所としては不適切ではないでしょうか。
適切なのは集計する階層で、全体に対して行うべき（日付の範囲ならTable_Aも同様の範囲になるべき）かと思います。

[zardpray]

すみません。
tableAも日付で絞り込みます。
全体的に日付で絞り込み休憩がない場合の条件になります。

Answer 2

デバッグすらしてませんが、だいたいこんな感じで運転中の休憩時間は出せると思います。

SELECT SUM(
    CASE WHEN a.END_DATE > b.END_DATE THEN b.END_DATE ELSE a.END_DATE END 
    - CASE WHEN a.START_DATE > b.START_DATE THEN a.START_DATE ELSE b.START END)) AS 運転中の休憩
FROM TABLE_A AS a
INNER JOIN TABLE_B AS b
ON a.START_DATE <= b.END_DATE AND a.END_DATE >= b.START_DATE
WHERE a.STATUS = "運転"

運転時間の合計から引けば休憩じゃない運転時間は出ると思います。
SQL Serverじゃなかったらもうちょっとすっきり書けるのですが…

Comments

[hihijiji]

よく考えたら運転中に複数回休憩がある場合を考慮してませんでした。
一応残しておきます。