Decomposition of time data across days

Database environment: SQL SERVER 2005

In Baidu know to see this topic, the original problem link MS SQL database on how the time loop to deal with the cross-day problem

The

resembles the following table:
--------------------------------------------------------------------------------------------------- --------------
Device Start time end time (minutes)
A 2015-08-01 17:06:49 2015-08-02 03:41:32 635
B 2015-08-01 20:54:42 201 5-08-03 23:59:59 3605
---------------------------------------------------------------------------------------　　-------------------------
Requirements:
1, cycle the time period of each device to the total time of the day
2, 1 days time calculation results must be 1440
3, cannot be handled with cursors
Demand result set table is as follows:
--------------------------------------------------------------------------------------------------------------- --
Device start time total time (minutes)
a 2015-08-01 17:06:49 2015-08-01 23:59:59 413
A 2015-08-02 00:00:00 2015-08-02 03:4 1:32 222
B 2015-08-01 20:54:42 2015-08-01 23:59:59 185
B 2015-08-02 00:00:00 2015-08-02 23:59:59 1440
B 201 5-08-03 00:00:00 2015-08-03 23:59:59 1440
--------------------------------------------------------------------- -------------------------------------------------

　　

This problem has been implemented by users with recursion, if not recursive, how should we deal with it? Below, I say my idea of realization:

1. Find out the number of days between the start and end times of each device

2. Create a new time after decomposition

3. Find out the time difference (minutes) of each device after decomposition, if it is a whole day, it is counted as 1440;

If it is the last day, the total time is subtracted from the sum of the previous time.

SQL implementation

/*test Data*/ withx0 as(SELECT   'A'  asSB,CONVERT(DATETIME,'2015-08-01 17:06:49') asDate_begin,CONVERT(DATETIME,'2015-08-02 03:41:32') asDate_end,635  asTotal_timeUNION  All               SELECT   'B'  asSB,CONVERT(DATETIME,'2015-08-01 20:54:42') asDate_begin,CONVERT(DATETIME,'2015-08-03 23:50:52') asDate_end,3056  asTotal_timeUNION  All               SELECT   'C'  asSB,CONVERT(DATETIME,'2015-08-04 12:40:20') asDate_begin,CONVERT(DATETIME,'2015-08-05 23:59:59') asDate_end,2119  astotal_time),/*calculates the number of days between two points in time*/X1 as(SELECTSB, Date_begin, Date_end, Total_time, DATEDIFF( Day, Date_begin, Date_end) asCacl_day--the number of days between the start time and end time                fromx0),/*break down the next day*/X2 as(SELECTSB, Case  whenmsv. Number = 0  ThenDate_beginELSE CONVERT(VARCHAR(Ten),DATEADD( Day, msv. Number, Date_begin), -)                        END  asDate_begin, Case  whenmsv. Number =X.cacl_day ThenDate_endELSE CONVERT(VARCHAR(Ten),DATEADD( Day, msv. Number, Date_begin), -)                                  + '23:59:59'                        END  asdate_end, Total_time, Case  whenmsv. Number =X.cacl_day Then 1                             ELSE 0                        END  asFlag--If this is the last day, the ID is 1, otherwise 0                fromx1 x, Master. Spt_values msvWHEREMsv.type= 'P'                         andmsv. Number <=x.cacl_day), X3 as(SELECTSB, Date_begin, Date_end, Total_time,  Case  when CONVERT(CHAR(8), Date_begin,108)= '00:00:00'                                   and CONVERT(CHAR(8), Date_end,108)= '23:59:59'                              Then 1440                             ELSE DATEDIFF(MINUTE, Date_begin, Date_end)END  asCacl_time,--if it's all day, then it's 1440 minutes, otherwise, 2 time subtractionflag, Row_number () Over(PARTITION bySbORDER  byDate_end) asRn--line number, used to count the remaining minutes of each day                fromx2)SELECTSB, Date_begin, Date_end, Case  whenFlag= 1  ThenTotal_time-(SELECT   SUM(cacl_time) fromX3 xWHEREX.sb=X3.SB andX.rn<x3.rn)ELSECacl_time--the time spent on the last day is the total time minus the sum of the preceding times            END  asCacl_time fromX3

View Code

I have added some test data myself, first look at the data of the original table

The time after decomposition

Interested friends, you can compare the 2 ways to achieve the similarities and differences.

　　

Decomposition of time data across days