Monthly Report Group by week in C# or SQL

Question

I'm creating an application that should show a bar chart in the dashboard displaying count of records by week_1 to week_n in a month:

Example (July):
Week 1 (2014-07-01 to 2014-07-05) = 30 records
Week 2 (2014-07-06 to 2014-07-12) = 15 records
Week 3 (2014-07-13 to 2014-07-19) = 50 records
Week 4 (2014-07-20 to 2014-07-26) = 24 records
Week 5 (2014-07-27 to 2014-07-31) = 6 records

I have seen several examples but they all seem to be focusing on Week of the Year. if no record was added for that week, the report should show 0 for the week. All examples I have seen use Group By Date, this will only output a count if there's a corresponding record for the week.

possible duplicate of [Calculate week of month in .NET](http://stackoverflow.com/questions/2136487/calculate-week-of-month-in-net) — Chris Hinton, Jul 29 '14 at 14:32
@cahinton You are right, the post above is indeed my solution. Thanks — Simua, Jul 29 '14 at 14:41
See my answer below that will group by and show weeks with 0. — Chris Hinton, Jul 29 '14 at 14:43
I'm testing it now, will add a comment and accept it if it gives me desired result. — Simua, Jul 29 '14 at 14:52

score 1 · Accepted Answer · answered Jul 29 '14 at 15:29

Try the following function, you pass in the monthend date then it return weeks

        CREATE FUNCTION WeeksOfTheMonth(@MothEndDate DATETIME)
        RETURNS
            @DateOfMonth TABLE(
                StartDate DATETIME,
                EndDate DATETIME,
                WeekNo INT
                )

        AS
        BEGIN

        DECLARE @NoOfWeeks AS INT,
                @StartDateNo AS INT,
                @EndDateNo AS INT,
                @Count AS INT

        SELECT 
            @NoOfWeeks = DATEPART(WEEK,DAY(@MothEndDate)), 
            @StartDateNo = 1, 
            @EndDateNo = 5,
            @Count = 1

        WHILE(@NoOfWeeks > 0)
        BEGIN

        INSERT INTO @DateOfMonth(StartDate, EndDate, WeekNo)
        SELECT DATEADD(day, DATEDIFF (day, 1, DATEADD(month, DATEDIFF(month, 0, @MothEndDate), 0) -1) /7*7 + 5, @StartDateNo),
               DATEADD(day, DATEDIFF (day, 1, DATEADD(month, DATEDIFF(month, 0, @MothEndDate), 0) -1) /7*7 + 6, @EndDateNo),
               @Count

         SELECT  
            @StartDateNo = @StartDateNo + 7, 
            @EndDateNo = @EndDateNo + 7, 
            @NoOfWeeks = @NoOfWeeks - 1,
            @Count = @Count + 1
        END
        RETURN
        END

Call it like

           SELECT *  FROM WeeksOfTheMonth('31 Aug 2014')

Your weeks output skip sundays. (Maybe due to @@DATEFIRST settings?) — Kevin Cook, Jul 29 '14 at 15:51

Monthly Report Group by week in C# or SQL

1 Answers1