Home>

I want to use Access_SQL to split a record within a particular record when it spans dates.
In the table below, if'start' →'finish' crosses a date, I would like to split it into two records, one until 23:59 on that day and one from 00:00 on the next day.

Specifically, when'start'→' finish'is '2010/5/10 18:00' →'2010/5/11 7:00'
I want to divide it into two parts: '2010/5/10 18:00' →'2010/5/10 23:59'and' 2010/5/11 00:00' →'2010/5/11 7:00' ..

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 Four

The result I want to output

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 Four
B 2011/6/11 0:00 2011/6/11 5:00 Four

If anyone knows how to do this, I would appreciate it if you could let me know.
Thanks for your cooperation.

  • Answer # 1

    We need more rows, so use union to split them.

    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 table 1 t1
    union all
      select id
           , CDate (format (finish, "yyyy/mm/dd 00:00:00"))
           , finish
           , Score
           , datediff ("n", start, finish) as OrigialDiff
      from table 1
      where Format (start, "yyyy/mm/dd")<>Format (finish, "yyyy/mm/dd")
    order by start


    ・ Explanation
    First Select:Edit Finish at the end of the start date with the same number of lines as the original, but across dates.
    Next select:Edit Start to the start time of the finish date, with only the number of cases that span the dates.
    OrigialDiff:Time difference (minutes) between the original start-finish. Used when dividing the score.