SQL: Split calendar week between a given two date time

Number05

Hi, I want to list a calendar weeks which falls between two given dates. Ex: StartDate :2016-01-04 02:19:45.000 and EndDate : 2016-08-31 07:57:37.000 I want output as follows : StartDate EndDate ------------------------------------------------------------------ 2016-01-04 02:19:45.000 2016-01-10 00:00:00.000 2016-01-11 00:00:00.000 2016-01-17 00:00:00.000 2016-01-18 00:00:00.000 2016-01-24 00:00:00.000 2016-01-25 00:00:00.000 2016-01-31 00:00:00.000 2016-02-01 00:00:00.000 2016-02-07 00:00:00.000 2016-02-08 00:00:00.000 2016-02-14 00:00:00.000 2016-02-15 00:00:00.000 2016-02-21 00:00:00.000 2016-02-22 00:00:00.000 2016-02-28 00:00:00.000 . . . . . . . . . . . . . . . . . . . . 2016-08-01 00:00:00.000 2016-08-07 00:00:00.000 2016-08-08 00:00:00.000 2016-08-14 00:00:00.000 2016-08-15 00:00:00.000 2016-08-21 00:00:00.000 2016-08-22 00:00:00.000 2016-08-28 00:00:00.000 2016-08-29 00:00:00.000 2016-09-04 07:57:37.000 I have tried the following code but did not get the accurate output. ------------------------------------------------------------------------------ WITH CW as ( SELECT --@StartTime STARTDATE -- DATEADD(WEEK, DATEDIFF(WEEK, 0, @StartTime), 0) FirstDayOfWeek DATEADD(WK, DATEDIFF(WK, 0, @StartTime), 0) STARTDATE , DATEADD(WK, DATEDIFF(WK, 0, @StartTime), 6) ENDDATE UNION ALL SELECT DATEADD(WW, 1, STARTDATE) , DATEADD(WW, 1, ENDDATE) FROM CW WHERE DATEADD(WW, 1, STARTDATE) <= @EndTime ) Thanks in advance....

Richard Deeming

This works for me:

DECLARE @StartDate datetime2(0) = '2016-01-04 02:19:45.000';
DECLARE @EndDate datetime2(0) = '2016-08-31 07:57:37.000';
DECLARE @EndDay date = CAST(@EndDate As date);

WITH CW (StartDate, EndDate) As
(
SELECT
@StartDate,
DateAdd(wk, 1, CAST(CAST(@StartDate As date) As datetime2(0)))

UNION ALL

SELECT
    EndDate,
    CASE
        WHEN DateAdd(wk, 1, EndDate) < @EndDay THEN DateAdd(wk, 1, EndDate)
        ELSE @EndDate
    END
FROM
    CW
WHERE
    EndDate < @EndDay

)
SELECT
StartDate,
EndDate
FROM
CW
;

Output:

StartDate EndDate

2016-01-04 02:19:45 2016-01-11 00:00:00
2016-01-11 00:00:00 2016-01-18 00:00:00
...
2016-08-22 00:00:00 2016-08-29 00:00:00
2016-08-29 00:00:00 2016-08-31 07:57:37

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Number05

Thank You :) This worked for me too :-D :-D

Number05

Hi Richard Deeming I have few more doubts please help to resolve it.. :( EX: Suppose for the same machine(machineID = 50) there are two different process started at different time. First one(process P1) started at 2016-01-04 02:19:45 and ends at 2016-06-08 07:57:37.000. Second one(process P2) started at 2016-10-01 02:19:45 and ends at 2016-12-31 07:57:37.000. So how can we take calendar week between two dates for the same machine? i mean startdate is 2016-01-04 02:19:45 and enddate is 2016-12-31 07:57:37.000 for the machineID = 50. output should come in the following way - StartDate EndDate ------------------- ------------------- 2016-01-04 02:19:45 2016-01-11 00:00:00 2016-01-11 00:00:00 2016-01-18 00:00:00 ... 2016-08-22 00:00:00 2016-08-29 00:00:00 2016-08-29 00:00:00 2016-08-31 07:57:37 2016-09-26 02:19:45 2016-10-03 00:00:00 2016-10-03 00:00:00 2016-10-10 00:00:00 2016-10-10 00:00:00 2016-10-17 00:00:00 ... 2016-12-26 00:00:00 2016-12-31 07:57:37 Also i want to calculate the total duration for each process P1 and P2 based on there startdate and enddate. Thanks in advance..

Richard Deeming

Assuming you have input data that looks like this:

MachineID ProcessID StartDate EndDate

50 1 2016-01-04 02:19:45 2016-06-08 07:57:37
50 1 2016-10-01 02:19:45 2016-12-31 07:57:37

Then something like this should work:

WITH CW (ProcessID, StartDate, EndDate, RunEnd, RunEndDay) As
(
SELECT
ProcessID,
StartDate,
DateAdd(wk, 1, CAST(CAST(StartDate As date) As datetime2(0))),
EndDate,
CAST(EndDate As date)
FROM
YourTable
WHERE
MachineID = 50

UNION ALL

SELECT
    ProcessID,
    EndDate,
    CASE
        WHEN DateAdd(wk, 1, EndDate) < RunEndDay THEN DateAdd(wk, 1, EndDate)
        ELSE RunEnd
    END,
    RunEnd,
    RunEndDay
FROM
    CW
WHERE
    EndDate < RunEndDay

)
SELECT
ProcessID,
StartDate,
EndDate
FROM
CW
ORDER BY
ProcessID,
StartDate
;

Output:

ProcessID StartDate EndDate

1 2016-01-04 02:19:45 2016-01-11 00:00:00
1 2016-01-11 00:00:00 2016-01-18 00:00:00
...
1 2016-05-30 00:00:00 2016-06-06 00:00:00
1 2016-06-06 00:00:00 2016-06-08 07:57:37

2 2016-10-01 02:19:45 2016-10-08 00:00:00
2 2016-10-08 00:00:00 2016-10-15 00:00:00
...
2 2016-12-17 00:00:00 2016-12-24 00:00:00
2 2016-12-24 00:00:00 2016-12-31 07:57:37

Calculating the duration will involve calling DATEDIFF[^] to work out the difference between the start and end dates.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Number05

Hello Richard Deeming Its not working as per my requirement. For the first set of dates the output data is repeating. I mean to say, for the startdate :2016-01-04 02:19:45 and enddate :2016-06-08 07:57:37 the total number of calendar week is 35.so the output is repeating for the same but for second set of dates i am getting proper output.I have used the same code snippet witch you have suggested. Following is the output which i am getting StartTime EndTime 2016-01-04 02:19:45.000 2016-01-11 00:00:00.000 2016-01-11 00:00:00.000 2016-01-18 00:00:00.000 2016-01-11 00:00:00.000 2016-01-18 00:00:00.000 2016-01-18 00:00:00.000 2016-01-25 00:00:00.000 2016-01-18 00:00:00.000 2016-01-25 00:00:00.000 2016-01-18 00:00:00.000 2016-02-01 00:00:00.000 2016-01-25 00:00:00.000 2016-02-01 00:00:00.000 2016-01-25 00:00:00.000 2016-02-08 00:00:00.000 .... 2016-08-29 00:00:00.000 2016-08-31 07:57:37.000 2016-10-01 02:19:45.000 2016-01-11 00:00:00.000 2016-10-03 00:00:00.000 2016-10-10 00:00:00.000 2016-10-10 00:00:00.000 2016-10-17 00:00:00.000 2016-10-17 00:00:00.000 2016-10-24 00:00:00.000 .... 2016-12-19 00:00:00.000 2016-12-26 00:00:00.000 2016-12-26 00:00:00.000 2016-12-31 07:57:37.000 Please let me know my mistake. Thanks in advance.

Richard Deeming

Looks like the date ranges for the two processes are overlapping. Try selecting the process ID as well as the dates.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Number05

Hello Richard Deeming, yeah then i will check on that. And thanks for your valuable time and suggestions.