SQL- Date Diff- # of week in each month between two date periods

Question

Problem: Display in columns the number of weeks in each month between two date periods (out to three months is fine for now). If possible, from the current day (Dynamic)

Where I currently am:

SELECT Q3.[Begin Date]
    ,Q3.[End Date]
    ,Q3.Diff_in_Year
    ,sum(CASE 
            WHEN Q3.Year_Counter = 0
                THEN datediff(mm, Q3.y_start, Q3.y_end) + 1
            ELSE 0
            END) Y1
    ,sum(CASE 
            WHEN Q3.Year_Counter = 1
                THEN datediff(mm, Q3.y_start, Q3.y_end) + 1
            ELSE 0
            END) Y2
    ,sum(CASE 
            WHEN Q3.Year_Counter = 2
                THEN datediff(mm, Q3.y_start, Q3.y_end) + 1
            ELSE 0
            END) Y3
    ,sum(CASE 
            WHEN Q3.Year_Counter = 3
                THEN datediff(mm, Q3.y_start, Q3.y_end) + 1
            ELSE 0
            END) Y4
    ,sum(CASE 
            WHEN Q3.Year_Counter = 4
                THEN datediff(mm, Q3.y_start, Q3.y_end) + 1
            ELSE 0
            END) Y5
    ,sum(CASE 
            WHEN Q3.Year_Counter = 5
                THEN datediff(mm, Q3.y_start, Q3.y_end) + 1
            ELSE 0
            END) Y6
    ,sum(CASE 
            WHEN Q3.Year_Counter = 6
                THEN datediff(mm, Q3.y_start, Q3.y_end) + 1
            ELSE 0
            END) Y7
    ,sum(CASE 
            WHEN Q3.Year_Counter = 7
                THEN datediff(mm, Q3.y_start, Q3.y_end) + 1
            ELSE 0
            END) Y8
    ,sum(CASE 
            WHEN Q3.Year_Counter = 8
                THEN datediff(mm, Q3.y_start, Q3.y_end) + 1
            ELSE 0
            END) Y9
    ,sum(CASE 
            WHEN Q3.Year_Counter = 9
                THEN datediff(mm, Q3.y_start, Q3.y_end) + 1
            ELSE 0
            END) Y10
FROM (
    SELECT Q1.[Begin Date]
        ,Q1.[End Date]
        ,Q1.years Diff_in_Year
        ,Q2.number AS Year_Counter
        ,(
            CASE 
                WHEN Q2.number = 0
                    THEN Q1.[Begin Date]
                ELSE dateadd(yy, datediff(yy, 0, dateadd(yy, q2.number, q1.[Begin Date])), 0)
                END
            ) AS y_Start
        ,(
            CASE 
                WHEN ((Q1.years - 1) = Q2.number)
                    THEN Q1.[End Date]
                ELSE DATEADD(yy, DATEDIFF(yy, 0, dateadd(yy, q2.number + 1, q1.[Begin Date]) + 1), - 1)
                END
            ) AS y_End
        ,Year(Q1.[Begin Date]) + Q2.number YearInYYYY
    FROM (
        SELECT [Begin Date]
            ,[End Date]
            ,DATEDIFF(year, [Begin Date], [End Date]) + 1 AS years
        FROM my dates
        ) Q1
    INNER JOIN master..spt_values Q2 ON Q2.type = 'P'
        AND Q2.number < Q1.years
    ) Q3
GROUP BY Q3.[Begin Date]
    ,Q3.[End Date]
    ,q3.Diff_in_Year

How the current code works: Given a date range, the number of months in each year between two dates. IE 1/1/2014 - 1/18/2015 would give two columns "2014" and 2015" the value of 2014 is 12 and the value of 2015 is 1 signifying that there are 13 months between the specified dates.

What I am hoping to achieve is something similar to
Start Date    End Date  Month 1   Month 2   Month 3
-----------------------------------------------------
1/1/2014      3/8/2014      4        4         1

Your issue is not at all clear. What does the table "my" look like? Perhaps starting at sqlfiddle.com would help us understand your issue. — Sean Lange
I hope you mean [my dates] It has exactly what its title is. A start date and an end date — Brian
heh well as posted you didn't have brackets around it so it appeared as the table named my aliased as dates. ;) So let's assume there is one row in that table. What is the logic from calculating the output? There seems to be a lot of query here but I don't understand what you are trying to accomplish. — Sean Lange
Your title says # of weeks, your query and text indicate # months, and your desired result set is # of weeks. Which do you want? — Jaaz Cole
As an aside: in case you run into some kind of confusion having to do with your use of mm in datediff, this article might help in explaining it. — Andriy M

Jaaz Cole Jaaz Cole · Accepted Answer · 2014-08-19T22:10:34

Dynamic SQL solutions aside (search for dynamic pivot in TSQL), I whipped up a couple of answers. Since your question is unclear whether you want weeks or months, I put together a quick one for each.

Months Example Here:

declare @startdate date = '1/1/2014', @enddate date = '3/1/2015'
select p.*
from (
    select @startdate as StartDate, @enddate as EndDate, right(convert(varchar,(dateadd(mm,RowID-1,@startdate)),105),4) [Group]
    from (
        select *, row_number()over(order by name) as RowID
        from master..spt_values
        ) d
    where d.RowID <= datediff(mm, @startdate, @enddate)
    ) t
    pivot (
        count([Group]) for [Group] in (
            [2014],[2015]
        )
    ) p

Weeks Example Here:

declare @startdate date = '1/1/2014', @enddate date = '3/1/2015'
select p.*
from (
    select @startdate as StartDate, @enddate as EndDate, right(convert(varchar,(dateadd(ww,RowID-1,@startdate)),105),7) [Group]
    from (
        select *, row_number()over(order by name) as RowID
        from master..spt_values
        ) d
    where d.RowID <= datediff(ww, @startdate, @enddate)
    ) t
    pivot (
        count([Group]) for [Group] in (
              [01-2014]
            , [02-2014]
            , [03-2014]
            , [04-2014]
            , [05-2014]
            , [06-2014]
            , [07-2014]
            , [08-2014]
            , [09-2014]
            , [10-2014]
            , [11-2014]
            , [12-2014]
            , [01-2015]
            , [02-2015]
            , [03-2015]
        )
    ) p

SQL- Date Diff- # of week in each month between two date periods

1 Answers