CODEWITHSUNDEEP
sqlsql-servert-sql
gevjen
gevjen

Reputation: 675

Sql Date - Breaking End Of Month up

I have a query (below) that will start at a specific time. from that time i need to increment a week at a time (each new row will be a new week - going from sunday to saturday) i have this done - but the new wrinkle is that if it is the end of the month it needs to stop on that date and start up on the first of the month but still stop on that saturday. result set good/bad are listed below and also my query up to this point.

Bad results:

2016-05-22  2016-05-28
2016-05-29  2016-06-04

What I need:

2016-05-22  2016-05-28
2016-05-29  2016-05-31
2016-06-01  2016-06-04

Code:

BEGIN
    DECLARE @StartDate DATE = DATEFROMPARTS(2016, 5, 22);
    DECLARE @EndDate DATE = CAST(GETDATE() AS DATE);
    DECLARE @Today DATE = @EndDate;
    DECLARE @EndOfMonth DATE = @StartDate

    ; WITH [Dates] AS (
        SELECT
            @StartDate AS [StartDate],
            DATEADD(DAY, 6, @StartDate) AS [EndDate]
        UNION ALL
        SELECT
            DATEADD(DAY, 7, [StartDate]),
            DATEADD(DAY, 7, [EndDate])
        FROM [Dates]
        WHERE   DATEADD(DAY, 7, [StartDate]) <= @EndDate
    )
    SELECT
        [tcw].[StartDate],
        [tcw].[EndDate]
    FROM [Dates] AS [tcw]
    OPTION (MAXRECURSION 0)
END
GO

Upvotes: 1

Views: 280

Answers (4)

Philip Kelley
Philip Kelley

Reputation: 40359

Given that you use DATEFROMPARTS, I'm assuming you are using SQL 2012 or later. I'm also relying on your existing logic with regards to determining the first and last dates. Based on this, replace your query with the following:

; WITH [Dates] AS (
    SELECT
        @StartDate AS [StartDate],
        DATEADD(DAY, 6, @StartDate) AS [EndDate]
    UNION ALL
    SELECT
        DATEADD(DAY, 7, [StartDate]),
        DATEADD(DAY, 7, [EndDate])
    FROM [Dates]
    WHERE   DATEADD(DAY, 7, [StartDate]) <= @EndDate
)
--  All weeks where all dates are within the same month
SELECT
    StartDate
   ,EndDate
FROM [Dates]
WHERE MONTH(StartDate) = MONTH(EndDate)
--  For weeks where all dates not within the same month, the last week in the month
UNION ALL SELECT
    StartDate
   ,EOMONTH(StartDate)
FROM [Dates]
WHERE MONTH(StartDate) <> MONTH(EndDate)
--  For weeks where all dates not within the same month, the first week in the (next) month
UNION ALL SELECT
    DATEADD(dd, 1, EOMONTH(StartDate))
   ,EndDate
FROM [Dates]
WHERE MONTH(StartDate) <> MONTH(EndDate)

The unions make it a bit awkward, but unless you're processing centuries with every pass it will run quickly enough. Note also that some "weeks" will contain one day, such as for July 31, 2016 through July 31, 2016.

-- Addenda, based on the comment ---------------------------------

The following query does this, but with a big warning…

; WITH cteDates AS (
    SELECT
        @StartDate AS StartDate,
        DATEADD(DAY, 6, @StartDate) AS EndDate,
        CASE
          WHEN DATEPART(dw, EOMONTH(@StartDate)) between 2 and 6 then 0  --  Assumes SET DATEFIRST is 1!
          ELSE 1
        END  AS MonthEndsOnWeekend
    UNION ALL
    SELECT
        DATEADD(DAY, 7, StartDate),
        DATEADD(DAY, 7, EndDate),
        CASE
          WHEN DATEPART(dw, EOMONTH(DATEADD(DAY, 7, StartDate))) between 2 and 6 then 0
          ELSE 1
        END
    FROM cteDates
    WHERE   DATEADD(DAY, 7, StartDate) <= @EndDate
)
--  All weeks where all dates are within the same month,
--  and all month-ending weeks where the last day of the month is Saturday or Sunday
SELECT
    StartDate
   ,EndDate
FROM cteDates
WHERE MONTH(StartDate) = MONTH(EndDate)
  OR MonthEndsOnWeekend = 1
--  For weeks where all dates not within the same month,
--  and the the last day of the month is NOT Saturday or Sunday,
--  the last week in the month
UNION ALL SELECT
    StartDate
   ,EOMONTH(StartDate)
FROM cteDates
WHERE MONTH(StartDate) <> MONTH(EndDate)
  AND MonthEndsOnWeekend = 0
--  For weeks where all dates not within the same month,
--  and the the last day of the month is NOT Saturday or Sunday,
--  the first week in the (next) month
UNION ALL SELECT
    DATEADD(dd, 1, EOMONTH(StartDate))
   ,EndDate
FROM cteDates
WHERE MONTH(StartDate) <> MONTH(EndDate)
  AND MonthEndsOnWeekend = 0

I use the DATEPART function to identify the day of week (Saturday, Sunday, etc). SQL will return a number for this function, where the number returned for day of week depends upon the setting of SET DATEFIRST. For my installations, we use the default where 1 = Monday. If you do not have complete control over the setting of SET DATEFIRST everywhere your code may ever be run, forever, then you may have problems outside the scope of this discussion. An alternative that I have used is to use DATENAME, which will return character strings, e.g. Saturday, Sunday, etc… but this has the same problem with regards to the setting of SET LANGUAGE.

(fyi, I also took out the [ ] and renamed the cte, because they were bugging me.)

Upvotes: 1

Irawan Soetomo
Irawan Soetomo

Reputation: 1325

Split any weeks that breach end of month into two, then union back them into that are not breaching the end of month.

    declare 
        @StartDate date = datefromparts(2016, 5, 22),
        @EndDate date = cast(getdate() as date)

    ; 
    with 
    dates as 
    (
        select
            @StartDate as StartDate,
            dateadd(day, 6, @StartDate) as EndDate,
            eomonth(@StartDate) as EndOfMonth,
            dateadd(day, 1 - day(dateadd(month, 1, @StartDate)), 
                dateadd(month, 1, @StartDate)) as FirstDayOfNextMonth

        union all

        select
            dateadd(day, 7, d.StartDate),
            dateadd(day, 7, d.EndDate),
            eomonth(dateadd(day, 7, d.StartDate)),
            dateadd(day, 1 - day(dateadd(month, 1, d.StartDate)), 
                dateadd(month, 1, d.StartDate))
        from 
            dates as d
        where   
            dateadd(day, 7, StartDate) <= @EndDate
    )
    -- week within the month
    select
        d.StartDate,
        d.EndDate
    from 
        dates as d
    where
        d.EndDate <= d.EndOfMonth

    union all

    -- week breach to next month, first part
    select
        d.StartDate,
        d.EndOfMonth as EndDate
    from 
        dates as d
    where
        d.EndDate > d.EndOfMonth

    union all

    -- week breach to next month, second part
    select
        d.FirstDayOfNextMonth as StartDate,
        d.EndDate
    from 
        dates as d
    where
        d.EndDate > d.EndOfMonth

    order by 1

    option (maxrecursion 0)

Result:

| StartDate  | EndDate    |
|------------|------------|
| 2016-05-22 | 2016-05-28 |
| 2016-05-29 | 2016-05-31 |
| 2016-06-01 | 2016-06-04 |
| 2016-06-05 | 2016-06-11 |
| 2016-06-12 | 2016-06-18 |
| 2016-06-19 | 2016-06-25 |
| 2016-06-26 | 2016-06-30 |
| 2016-07-01 | 2016-07-02 |
| 2016-07-03 | 2016-07-09 |
| 2016-07-10 | 2016-07-16 |
| 2016-07-17 | 2016-07-23 |
| 2016-07-24 | 2016-07-30 |
| 2016-07-31 | 2016-07-31 |
| 2016-08-01 | 2016-08-06 |
| 2016-08-07 | 2016-08-13 |
| 2016-08-14 | 2016-08-20 |

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 82020

I added the Day of Week (beg/end) just to illustrate

Declare @Date1 Date='2016-05-22'
Declare @Date2 Date='2016-07-31'

;with cteBase as (
Select *
       ,Flag1=IIF(Row_Number() over (Partition By Month(RetVal) Order By RetVal)=1 or DatePart(WEEKDAY,RetVal)=1 or Day(RetVal)=1 ,1,0)
       ,Flag2=IIF(DatePart(WEEKDAY,RetVal)=7 or Lead(Day(RetVal),1) over (Order By RetVal)=1,1,0)
       ,RowNr=Row_Number() over (Order By RetVal)
    From  [dbo].[udf-Create-Range-Date](@Date1,@Date2,'DD',1)
)
Select DateR1=cast(A.RetVal as Date)
      ,B.DateR2
      ,DOW1=DateName(Weekday,A.RetVal) 
      ,DOW2=DateName(Weekday,B.DateR2) 
From cteBase A 
Cross Apply (Select DateR2=min(cast(RetVal as date)) From cteBase Where Flag2=1 and RowNr>=A.RowNr) B
Where A.Flag1=1 and B.DateR2 is not null

Returns

DateR1      DateR2      DOW1        DOW2
2016-05-22  2016-05-28  Sunday      Saturday
2016-05-29  2016-05-31  Sunday      Tuesday
2016-06-01  2016-06-04  Wednesday   Saturday
2016-06-05  2016-06-11  Sunday      Saturday
2016-06-12  2016-06-18  Sunday      Saturday
2016-06-19  2016-06-25  Sunday      Saturday
2016-06-26  2016-06-30  Sunday      Thursday
2016-07-01  2016-07-02  Friday      Saturday
2016-07-03  2016-07-09  Sunday      Saturday
2016-07-10  2016-07-16  Sunday      Saturday
2016-07-17  2016-07-23  Sunday      Saturday
2016-07-24  2016-07-30  Sunday      Saturday

The UDF I use to create dynamic date Ranges

CREATE FUNCTION [dbo].[udf-Create-Range-Date] (@DateFrom datetime,@DateTo datetime,@DatePart varchar(10),@Incr int)

Returns 
@ReturnVal Table (RetVal datetime)

As
Begin
    With DateTable As (
        Select DateFrom = @DateFrom
        Union All
        Select Case @DatePart
               When 'YY' then DateAdd(YY, @Incr, df.dateFrom)
               When 'QQ' then DateAdd(QQ, @Incr, df.dateFrom)
               When 'MM' then DateAdd(MM, @Incr, df.dateFrom)
               When 'WK' then DateAdd(WK, @Incr, df.dateFrom)
               When 'DD' then DateAdd(DD, @Incr, df.dateFrom)
               When 'HH' then DateAdd(HH, @Incr, df.dateFrom)
               When 'MI' then DateAdd(MI, @Incr, df.dateFrom)
               When 'SS' then DateAdd(SS, @Incr, df.dateFrom)
               End
        From DateTable DF
        Where DF.DateFrom < @DateTo
    )

    Insert into @ReturnVal(RetVal) Select DateFrom From DateTable option (maxrecursion 32767)

    Return
End

-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','YY',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','DD',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-31','MI',15) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-02','SS',1)

Upvotes: 0

Matt
Matt

Reputation: 14381

Looking at your code I assume you are using sql-server. EOMONTH() is available in sql-server 2012 + and makes finding the end date a little easier. Basically you need to test for what day of the week the start date is and how close to the end of the month a start day is and then balance that through your recursion. you can add another LEVEL column and track a week number if you want. E.g. 1 as LEVEL in first query in UNION Level+1 as Level.

DECLARE @StartDate DATE = '2016-05-22'
DECLARE @EndDate DATE = GETDATE()

--if @StartDate provided is middle of the week and you want to adjust to sunday use this
SET @StartDate = CASE
       WHEN DATEPART(WEEKDAY,@StartDate) = 1 THEN @StartDate
       ELSE DATEADD(day,-7 + DATEPART(WEEKDAY,@StartDate),@StartDate)
    END

;WITH cteRecursiveDates AS (
    SELECT
       @StartDate as Startdate
       ,CASE
          WHEN DATEDIFF(day,@StartDate,EOMONTH(@StartDate)) < (7 - DATEPART(WEEKDAY,@StartDate))
              THEN DATEADD(day,DATEDIFF(day,@StartDate,EOMONTH(@StartDate)) ,@StartDate)
          ELSE DATEADD(day,(7- DATEPART(WEEKDAY,@StartDate)),@StartDate)
       END as EndDate

    UNION ALL

    SELECT
       DATEADD(day,1,c.EndDate) as StartDate
       ,CASE
          WHEN DATEDIFF(day,DATEADD(day,1,c.EndDate),EOMONTH(DATEADD(day,1,c.EndDate))) < (7 - DATEPART(WEEKDAY,DATEADD(day,1,c.EndDate)))
             THEN DATEADD(day,DATEDIFF(day,DATEADD(day,1,c.EndDate),EOMONTH(DATEADD(day,1,c.EndDate))) ,DATEADD(day,1,c.EndDate))
          ELSE DATEADD(day,(7- DATEPART(WEEKDAY,DATEADD(day,1,c.EndDate))),DATEADD(day,1,c.EndDate))
       END as EndDate
    FROM
       cteRecursiveDates c
    WHERE
       DATEADD(day,1,c.EndDate) <= @EndDate
)

SELECT *
FROM
    cteRecursiveDates

Upvotes: 0

Related Questions