How to write a sql statement to divide the lines into multiple parts and add time by part

advertisements

I'm using sqlserver.

    Table A:
        -------------------------------------------------------
        | id    | starttime             |     endtime         |
        -------------------------------------------------------
        | 9     | 2016-09-19 02:36:05   | 2016-09-19 03:02:16 |
        | 9     | 2016-09-19 03:02:16   | 2016-09-19 09:26:15 |
        | 9     | 2016-09-19 09:26:15   | 2016-09-20 01:18:02 |

    Table B:
        ----------------------------------------
        |partno   |dayHourStart | dayHourEnd   |
        ----------------------------------------
        |3        |12:00:00.000 | 19:00:00.000 |
        |3        |22:00:00.000 | 23:59:59.000 |
        |3        |08:00:00.000 | 09:00:00.000 |
        |1        |19:00:00.000 | 22:00:00.000 |
        |1        |09:00:00.000 | 12:00:00.000 |
        |2        |00:00:00.000 | 08:00:00.000 |

Now,I want to split the rows of Table A into several parts, and sum the time by part, according to the corresponding time range of every partno in Table B. so How to write a sql statement to get the results as follows:

   Anticipant Result tableļ¼š
        --------------------------------------------------|
        | id     |partno           |  timeSum (minutes)   |
        |--------|-----------------|----------------------|
        | 9      | 101             |    200               |
        | 9      | 102             |    620               |
        | 9      | 103             |    860               |

Any ideas would be appreciated! thanks.


This is tricky, dealing with times and datetimes and overlaps. Here is one method:

select a.id, b.partno,
       datediff(minute,
                (case when b.dayHourStart > cast(a.starttime as time) then b.dayHourStart
                      else cast(a.starttime as time)
                 end),
                (case when b.dayHourEnd < cast(a.endtime as time) then b.dayHourEnd
                      else cast(a.endtime as time)
                 end)
               ) as minutes
from a join
     b
     on cast(a.starttime as time) <= b.dayHourEnd and
        cast(b.endtime as time) >= b.dayHourStart;

The only issue with this idea is the last of the rows for A. This spans two days. That just makes the calculation much more difficult. You should split that row into two rows:

    | 9     | 2016-09-19 09:26:15   | 2016-09-19 23:59:59.000 |
    | 9     | 2016-09-20 00:00:00   | 2016-09-20 01:18:02 |