【SQL】Accessを使って日付をまたぐ場合にレコードを分割したいです。

Question

Access_SQLを使って特定のレコードの中で、日付をまたぐ場合にレコードを分割したいです。
下記のテーブルの中で 'start' → 'finish'が日付をまたぐ場合に、その日の23:59までと次の日の00:00からの2つのレコードに分割したいと思っています。

具体的には、 'start' → 'finish'が '2010/5/10 18:00' →　'2010/5/11 7:00'の場合
'2010/5/10 18:00' →　'2010/5/10 23:59'と '2010/5/11 00:00' →　'2010/5/11 7:00'の2つに分割したいです。

Table
id|Start|finish|Score
|:--|:--:|--:|
A|2010/5/10 18:00|2010/5/11 7:00|2
B|2011/6/10 13:00|2011/6/10 18:00|3
B|2011/6/10 21:00|2011/6/11 5:00|4

出力したい結果
id|Start|finish|Score
|:--|:--:|--:|
A|2010/5/10 18:00|2010/5/10 23:59|2
A|2010/5/11 0:00|2010/5/11 7:00|2
B|2011/6/10 13:00|2011/6/10 18:00|3
B|2011/6/10 21:00|2011/6/10 23:59|4
B|2011/6/11 0:00|2011/6/11 5:00|4

方法がわかる方がいらっしゃいましたら、ぜひ教えていただけたら幸いです。
何卒よろしくお願いいたします。

Comments

[Orlofsky]

ここの掲示版は丸投げは禁止なので、試行錯誤している現行のコードを https://teratail.com/help/question-tips#questionTips3-7 の [コード] で質問に追記してください。

[sousuke]

分割してそのさきに何がしたいのかを記載した方がいいのではないでしょうか

[player31]

ご指摘ありがとうございます。
追記して、改めてご質問されていただきます。

>>分割した後何がしたいのか
→分割したあと、その日ごとのScoreの合計を出したいと思っています。

[sousuke]

→分割したあと、その日ごとのScoreの合計を出したいと思っています。
一応確認ですけどこれだと日をまたいだスコアが倍に増えますがそれでいいんですか？

Answer 1

行を増やす必要があるので、unionを使用して分割します。

  select id, start
       , IIf(
             Format(start, "yyyy/mm/dd") <> Format(t1.finish, "yyyy/mm/dd")
           , CDate(Format(start, "yyyy/mm/dd 23:59:59"))
           , t1.finish
         ) as finish
       , Score
       , datediff("n", start, t1.finish) as OrigialDiff
  from テーブル1 t1
union all
  select id
       , CDate(format(finish, "yyyy/mm/dd 00:00:00"))
       , finish
       , Score
       , datediff("n", start, finish) as OrigialDiff
  from テーブル1
  where Format(start, "yyyy/mm/dd") <> Format(finish, "yyyy/mm/dd")
order by start

・解説
**最初のSelect：**元のデータと同じ件数で、日付を跨っている場合のFinishをstartの日付の終わりの時刻に編集します。
**次のselect：**日付が跨っているものだけの件数で、Startをfinishの日付の始まりの時刻に編集します。
**OrigialDiff:**元々のstart-finish間の時間差(分)。Scoreを案分する場合に使用。

Comments

[player31]

ありがとうございます。お陰様でやりたいことができました。
解説もとてもわかりやすく、勉強になりました。