SSRS - Month not starting on correct day

Question

I'm trying to make a report that generates a calendar for each month where the user can specify a range of months and years. My matrix has a column grouping on Weekday and the row grouping is on WeekNumber (see query results below).

The issue I'm stuck on is that the day is starting in the wrong place as shown below:

This is because my query only takes in the days from that particular month and not the few days from the month before and after that would normally be grayed out on a calendar:

Although I suspect this problem may also be solvable in the matrix itself I'm not sure if solving it in the query or solving it in the matrix would be better than the other.

My current query:

SELECT  t1.[TimeSheetLineID] ,
        t1.[TimeSheetID] ,
        t1.[JobID] ,
        t1.[TimeSheetDate] ,
        t1.[TimeSheetCreatedOn] ,
        t1.[TimeSheetLastModifiedOn] ,
        t1.[StartTime] ,
        t1.[EndTime] ,
        t1.[TotalTime] ,
        t1.[EmployeeName] ,
        t2.DateFull ,
        t2.[FullYear] ,
        t2.[WeekNumber] ,
        t2.[WeekDay] ,
        t2.[WeekDayName] ,
        t2.[MonthDay] ,
        t2.[MonthName] ,
        t2.[MonthNumber]
FROM    dbo.DateLookup t2
LEFT JOIN [dbo].[FactTimeSheets] t1 
  ON t1.TimeSheetDate = t2.DateFull
  AND (t1.JobID = @jobNumber) 
WHERE (t2.FullYear = @year) 
  AND (t2.MonthNumber BETWEEN @startMonth AND @endMonth)
ORDER BY FullYear, MonthNumber, MonthDay, WeekDay

Current query result (notice WeekDay starts with 2 for the month of Aug):

Based off this article.

--UPDATE--

I'm using the CTE in my query below to get the extra dates I need to fill out this calendar. I tested it on the Date table and will update my answer tomorrow with the final query:

DECLARE @year INT ,
    @startMonth DATE ,
    @endMonth DATE;
SET @year = '2016';
SET @startMonth = '2016-08-01';
SET @endMonth = '2016-08-31';

DECLARE @StartDate DATE ,
    @EndDate DATE;
SET @StartDate = DATEADD(s, 0, DATEADD(mm, DATEDIFF(m, 0, @startMonth), 0));
SET @StartDate = DATEADD(DAY, -DATEPART(WEEKDAY, @StartDate) + 1, @StartDate);
--SELECT @StartDate

SET @EndDate = DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, @startMonth) + 1, 0));
SET @EndDate = DATEADD(DAY, 7 - DATEPART(WEEKDAY, @EndDate), @EndDate)
--SELECT @EndDate;
;

WITH    Dates ( [Date] )
          AS (
   --Select First day in range
               SELECT   CONVERT(DATETIME, @StartDate) AS [Date]
               UNION ALL
   --Add a record for every day in the range
               SELECT   DATEADD(DAY, 1, [Date])
               FROM     Dates
               WHERE    Date < CONVERT(DATETIME, @EndDate)
             ),
        Events
          AS ( SELECT   [FullYear] ,
                        [DateFull] ,
                        [WeekNumber] ,
                        [WeekDay] ,
                        [WeekDayName] ,
                        [MonthDay] ,
                        [MonthName] ,
                        [MonthNumber]
               FROM     [dbo].[DateLookup]
             )
    SELECT  e.[FullYear] ,
            e.[DateFull] ,
            d.[Date] ,
            e.[WeekNumber] ,
            e.[WeekDay] ,
            e.[WeekDayName] ,
            e.[MonthDay] ,
            e.[MonthName] ,
            e.[MonthNumber]
    FROM    Dates d
            LEFT JOIN Events e ON d.[Date] = CAST(e.DateFull AS DATE)
    GROUP BY FullYear ,
            MonthNumber ,
            DateFull ,
            d.[Date] ,
            WeekNumber ,
            MonthName ,
            WeekDayName ,
            MonthDay ,
            WeekDay
    ORDER BY Date;

--FINAL UPDATED QUERY--

I hope this helps someone in the future.

SET @startDate = DATEADD(mm, DATEDIFF(m, 0, CAST(CAST(@year AS varchar) + '-' + CAST(@startMonth AS varchar) + '-01' AS DATETIME)), 0);
SET @startDate = DATEADD(DAY, -DATEPART(WEEKDAY, @startDate) + 1, @startDate);

SET @endDate = DATEADD(mm, DATEDIFF(m, 0, CAST(CAST(@year AS varchar) + '-' + CAST(@endMonth AS varchar) + '-01' AS DATETIME)) + 1, 0);
SET @endDate = DATEADD(DAY, 7 - DATEPART(WEEKDAY, @endDate), @endDate);

WITH    Dates ( [Date] )
          AS ( SELECT   CONVERT(DATETIME, @startDate) AS [Date]
               UNION ALL
               SELECT   DATEADD(DAY, 1, [Date])
               FROM     Dates
               WHERE    Date < CONVERT(DATETIME, @endDate)
             ),
        Events
          AS ( SELECT   t1.[TimeSheetLineID] ,
                        t1.[TimeSheetID] ,
                        t1.[JobID] ,
                        t1.[TimeSheetDate] ,
                        t1.[TimeSheetCreatedOn] ,
                        t1.[TimeSheetLastModifiedOn] ,
                        t1.[StartTime] ,
                        t1.[EndTime] ,
                        t1.[TotalTime] ,
                        t1.[EmployeeName] ,
                        t1.[CostCategory] ,
                        t2.[DateFull] ,
                        t2.[FullYear] ,
                        t2.[WeekNumber] ,
                        t2.[WeekDay] ,
                        t2.[WeekDayName] ,
                        t2.[MonthDay] ,
                        t2.[MonthName] ,
                        t2.[MonthNumber]
               FROM     dbo.DateLookup t2
                        LEFT JOIN [dbo].[FactTimeSheets] t1 ON t1.TimeSheetDate = t2.DateFull
                                                              AND ( t1.JobID = @jobNumber )
               WHERE    ( t2.FullYear = @year )
             )
    SELECT  e.[TimeSheetLineID] ,
            e.[TimeSheetID] ,
            e.[JobID] ,
            e.[TimeSheetDate] ,
            e.[TimeSheetCreatedOn] ,
            e.[TimeSheetLastModifiedOn] ,
            e.[StartTime] ,
            e.[EndTime] ,
            e.[TotalTime] ,
            e.[EmployeeName] ,
            e.[CostCategory] ,
            e.[DateFull] ,
            d.[Date] ,
            e.[FullYear] ,
            e.[WeekNumber] ,
            e.[WeekDay] ,
            e.[WeekDayName] ,
            e.[MonthDay] ,
            e.[MonthName] ,
            e.[MonthNumber]
    FROM    Dates d
            LEFT JOIN Events e ON d.[Date] = CAST(e.DateFull AS DATE)
    ORDER BY Date;

Answer 1

You can group the dates in a matrix to get the layout right.

Insert a matrix. Group and sort the columns by weekday (number). Group and sort the rows by week number. It should look like this:

Now when you run it it will come out like this: