質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.59%

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

解決済

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 535

zardpray

score 6

前提・実現したいこと

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • hihijiji

    2020/01/24 12:47

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

    キャンセル

  • hihijiji

    2020/01/24 12:52

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

    キャンセル

  • zardpray

    2020/01/24 12:53

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

    キャンセル

  • zardpray

    2020/01/24 13:02

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

    キャンセル

回答 2

checkベストアンサー

+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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2020/01/27 19:21 編集

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

    キャンセル

  • 2020/01/27 22:46 編集

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

    キャンセル

  • 2020/01/28 09:02

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

    キャンセル

0

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

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じゃなかったらもうちょっとすっきり書けるのですが…

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2020/01/24 16:52

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.59%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る