To populate all dates in a month in SQL and SSRS

Question

We need to create a report based on employee, say Kumar, logged 3 hours. For example, if a person is logged Hours in system for 1-1-2021. But he didn't log for 2-1-2021.

We have a join query to get the date and worked hours for each employee.

Our present report is like below (this is the screenshot from SSRS report)

But we need the missing dates also in the report. Abhishek has logged only from 4th Jan in the system. But we need rows above 4th Jan also in the report. First name and last name should be in those columns in the new row. The hours worked should be 0 and other column values should be N/A or Null.

But here we need a query to get all dates in a month in the report whether the employee logged hours or not.() for not logged days)

This is the report that we want in SSRS.(i created this in excel)

How can i do that?

enter image description here

It would be nice if you provide some sample data and desired output — Sergey
In your query you have to outer join with a date table containing all the dates — niktrs
It wasn't that difficult the last name from the first picture, and the second one makes it even easier... — James Z

Hannover Fist Hannover Fist · Accepted Answer · 2021-04-13T16:10:34

You are not able to force the missing dates with SSRS. To make this work correctly, you'll need to update your query to get all the dates in your date range and then LEFT JOIN your current data to the dates.

Add a INTO to your current report query put to put the data in a #TEMP_TABLE.

SELECT <CURRENT FIELDS>
INTO #TEMP_TABLE
FROM BLAH BLAH BLAH

Then Create a table of dates for your date range using a CTE with RECURSION.

DECLARE @START_DATE DATE = '01/01/2020' --THESE DATES SHOULD BE CHANGE TO USE PARAMETERS OF YOUR DATE RANGE
DECLARE @END_DATE  DATE  = '03/31/2021' --OR MIN/MAX FROM THE #TEMP_TABLE

;WITH GETDATES AS  
    (  
        SELECT @START_DATE AS THEDATE
        UNION ALL  
        SELECT DATEADD(DAY,1, THEDATE) FROM GETDATES  
        WHERE THEDATE < @END_DATE  
)

SELECT D.THEDATE, T.*
FROM GETDATES D 
LEFT JOIN #TEMP_TABLE T ON D.THEDATE = T.DATE_WORKED
OPTION (maxrecursion 0)

This will return every date in THEDATE field with your current data in the other fields.

To populate all dates in a month in SQL and SSRS

1 Answers