CODEWITHSUNDEEP
sqlsql-servert-sqlcommon-table-expression
George Eivaz
George Eivaz

Reputation: 145

SQL Split Period by Months

Good Afternoon, I have what seems like a simple problem that has turned out to be not so simple. I have 2 dates. BeginPeriod (2010-06-10) and EndPeriod (2011-06-11).

What I would like to see if if these dates can be broken down into their respective monthly break downs. For the above example something like

I am not particular about the method. CTEs are fine but not preferred. As they say, beggars can't be choosers.

All the best, George

Upvotes: 1

Views: 3973

Answers (3)

starspiritstorm
starspiritstorm

Reputation: 1

I know that this is an old question, but I just came across with the same problem recently, and wrote an version without CTE.

Here's the tested code


DECLARE @FromDate DATETIME 
      , @ToDate DATETIME
      , @UpdateBeginDate BIT   
      , @UpdateEndDate BIT    

    SET @FromDate = '2019-01-15'
    SET @ToDate = '2021-08-17'
    SET @UpdateBeginDate = 1
    SET @UpdateEndDate = 1

DECLARE @Results TABLE ( 
      MonthStart DATETIME
    , MonthEnd DATETIME 
    , TheMonth VARCHAR(2) 
    , TheYear VARCHAR(4)
)


     -- Months in that period
     INSERT INTO @Results
     SELECT TOP (DATEDIFF(MONTH, @FromDate, @ToDate)+1) -- calculate how many rows needed
          DATEADD(MONTH, number, @FromDate)
        , DATEADD(MONTH, number, @FromDate)
        , MONTH(DATEADD(MONTH, number, @FromDate))
        , YEAR(DATEADD(MONTH, number, @FromDate))
       FROM [master].dbo.spt_values 
      WHERE [type] = N'P' 
      ORDER BY number

     /*
         by using [master].dbo.spt_values, the total series number are 2048, 
         for month cases, there will be 2048/12 ≈ 170 year range
     */

     -- Update first date of month and last date of month for each row
     UPDATE @Results
        SET MonthStart = DATEADD(MONTH, DATEDIFF(MONTH, 0, MonthStart), 0) 
          , MonthEnd = DATEADD(MONTH, ((YEAR(MonthEnd) - 1900) * 12) + MONTH(MonthEnd), -1)
     
     /*
         MonthStart
         Caculate the month difference from 1900-01-01 (0 represent 1900-01-01) to the current row's MonthStart record (DATEDIFF part)
         Then add the month difference from 1900-01-01 (DATEADD part)

         MonthEnd
         Caculate the year difference from 1900 to the current row's MonthEnd record then multiply 12 and plus the months of the record (The middle part)
         Then add the month difference from 1899-12-31 (DATEADD part)
         NOTE: -1 means 1899-12-31

         The MonthEnd sentence is equivalent to this, if you keeped the record of TheYear and TheMonth
         MonthEnd = DATEADD(MONTH, ((TheYear - 1900) * 12) + TheMonth, -1)
     */


     IF @UpdateBeginDate = 1
     BEGIN
         UPDATE @Results 
            SET MonthStart = @FromDate
          WHERE MonthStart = (SELECT MIN(MonthStart) FROM @Results)
     END


     IF @UpdateEndDate = 1
     BEGIN
         UPDATE @Results 
            SET MonthEnd = @ToDate
          WHERE MonthEnd = (SELECT MAX(MonthEnd) FROM @Results)
     END


    select * from @Results

I tested this code in MSSQL 2005 and MSSQL 2017, I think other versions might worked well too.

Hope this will help other people who want a solution without CTE.

Upvotes: 0

shurik
shurik

Reputation: 805

Another options with "Numbers" CTE 

declare @df datetime, @dt datetime
set @df = '20100610'
set @dt = '20110611'

;WITH e1(n) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 10
e2(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY e1.n) FROM e1 CROSS JOIN e1 AS b) -- 10*10
select  
    case when e2.n = 1 
        then @df 
        else dateadd(day, -day(@df) + 1, dateadd(month, e2.n - 1, @df)) end, 
    case when e2.n = datediff(month, @df, @dt) + 1 
        then dateadd(month, e2.n -1 , @df) 
        else EOMONTH( dateadd(month, e2.n -1 , @df) ) end
from e2
where e2.n

Instead of Numbers CTE you can use some other option for example as described here http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1 I often have Numbers table in my DB for such tasks. I think primarily because I started using this technique before CTEs were added to MS SQL but it is less typing also.

Upvotes: 0

JamieD77
JamieD77

Reputation: 13949

CTE it is.

DECLARE @BeginPeriod DATETIME = '2010-06-10',
        @EndPeriod DATETIME = '2011-06-11'

;WITH cte AS
(
    SELECT DATEADD(month, DATEDIFF(month, 0, @BeginPeriod), 0) AS StartOfMonth, 
           DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, @BeginPeriod) + 1, 0)) AS EndOfMonth
    UNION ALL
    SELECT DATEADD(month, 1, StartOfMonth) AS StartOfMonth, 
           DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, 1, StartOfMonth)) + 1, 0)) AS EndOfMonth  
    FROM   cte
    WHERE  DATEADD(month, 1, StartOfMonth) <= @EndPeriod
)
SELECT  
    (CASE WHEN StartOfMonth < @BeginPeriod THEN @BeginPeriod ELSE StartOfMonth END) StartOfMonth,
    (CASE WHEN EndOfMonth > @EndPeriod THEN @EndPeriod ELSE EndOfMonth END) EndOfMonth
FROM cte

the last EndOfMonth is the value you used as @EndPeriod set it to DATEADD(day, -1, @EndPeriod) if you want the previous day

You can use this to trim the time.

SELECT  
    CONVERT(VARCHAR(10), (CASE WHEN StartOfMonth < @BeginPeriod THEN @BeginPeriod ELSE StartOfMonth END), 120) StartOfMonth,
    CONVERT(VARCHAR(10), (CASE WHEN EndOfMonth > @EndPeriod THEN @EndPeriod ELSE EndOfMonth END), 120) EndOfMonth
FROM cte

Upvotes: 7

Related Questions