How to get rows per day (Fill gaps when no records)

Question

I have the following results

Date       | EmployeeID 
2015-11-18 | 1          
2015-11-18 | 1          
2015-11-18 | 1          
2015-11-19 | 1          
2015-11-19 | 1          
2015-11-20 | 1          
2015-11-20 | 1          
2015-11-20 | 1          
2015-11-25 | 1

But given a range of dates (2015-11-15 - 2015-11-30) I want to display something like this

Date       | NbEmployees
2015-11-15 | 0
2015-11-16 | 0
2015-11-17 | 0
2015-11-18 | 3
2015-11-19 | 2
2015-11-20 | 3
2015-11-21 | 0
2015-11-22 | 0
2015-11-23 | 0
2015-11-24 | 0
2015-11-25 | 1
2015-11-26 | 0
2015-11-27 | 0
2015-11-28 | 0
2015-11-29 | 0
2015-11-30 | 0

I've using this approach by I only get the values from the table with data

DECLARE @StartDate DATE = '2015-11-15 00:00:00', @EndDate DATE = '2015-11-30 23:59:00'
DECLARE @CurrentDate DATE = @StartDate
DECLARE @DateRange TABLE (CurrentDate DATETIME)

WHILE(@CurrentDate <= @EndDate)
BEGIN
    INSERT INTO @DateRange VALUES(@CurrentDate)
    SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate)
END

SELECT r.CurrentDate, COUNT(EmployeeID)
FROM Employee e
RIGHT JOIN @DateRange r ON e.HireDate = r.Date

Results:

Date       | NbEmployees
2015-11-18 | 3
2015-11-19 | 2
2015-11-20 | 3
2015-11-25 | 1

Usually the simplest way is to create a calendar table (=1 row per day) and join that with your query. — James Z, Mar 22 '16 at 15:07
That's what I did. Look into my example code. My calendar is @DateRange — Maximus Decimus, Mar 22 '16 at 15:10
not sure how that could be working at all. you defined your temp date table with `CurrentDate` as the field, but are using `r.Date` in the actual query later on, plus aliasing your employee table as `e`, then using `emp.` as the alias elsewhere. — Marc B, Mar 22 '16 at 15:18

score 1 · Accepted Answer · edited May 23 '17 at 12:23

Try it like this

DECLARE @tbl TABLE([Date] DATE, EmployeeID INT);
INSERT INTO @tbl VALUES
 ('2015-11-18',1)          
,('2015-11-18',1)          
,('2015-11-18',1)         
,('2015-11-19',1)          
,('2015-11-19',1)          
,('2015-11-20',1)          
,('2015-11-20',1)          
,('2015-11-20',1)          
,('2015-11-25',1);

DECLARE @StartDate DATE = '2015-11-15 00:00:00', @EndDate DATE = '2015-11-30 23:59:00'
DECLARE @CurrentDate DATE = @StartDate
DECLARE @DateRange TABLE (CurrentDate DATETIME)

WHILE(@CurrentDate <= @EndDate)
BEGIN
    INSERT INTO @DateRange VALUES(@CurrentDate)
    SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate)
END

SELECT CurrentDate,ISNULL(NbEmployees,0) AS NbEmployees
FROM @DateRange
LEFT JOIN
(
    SELECT COUNT(tbl.EmployeeID) AS NbEmployees
          ,tbl.[Date] AS Date
    FROM @tbl AS tbl    
    GROUP BY tbl.[Date]   
) AS grouped ON CurrentDate=grouped.[Date]

The result

2015-11-15 00:00:00.000 0
2015-11-16 00:00:00.000 0
2015-11-17 00:00:00.000 0
2015-11-18 00:00:00.000 3
2015-11-19 00:00:00.000 2
2015-11-20 00:00:00.000 3
2015-11-21 00:00:00.000 0
2015-11-22 00:00:00.000 0
2015-11-23 00:00:00.000 0
2015-11-24 00:00:00.000 0
2015-11-25 00:00:00.000 1
2015-11-26 00:00:00.000 0
2015-11-27 00:00:00.000 0
2015-11-28 00:00:00.000 0
2015-11-29 00:00:00.000 0
2015-11-30 00:00:00.000 0

With something like this you could create your date-tally on the fly (avoid loops!!!)

DECLARE @StartDate DATE = '2015-11-15 00:00:00', @EndDate DATE = '2015-11-30 23:59:00';
WITH DayCount(Nmbr) AS
(
    SELECT TOP (DATEDIFF(DAY,@StartDate,@EndDate)+1) ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 FROM sys.objects
)
,RunningDates(CurrentDate) AS
(
    SELECT DATEADD(DAY,Nmbr,@StartDate) FROM DayCount
)
SELECT * FROM RunningDates

This is bound to the max count of sys.objects... You'll find a lot of examples how to create running numbers on the fly or how to create a date-tally table (for example this: https://stackoverflow.com/a/32474751/5089204)

Instead of looping this would be better with a tally/numbers table. — Sean Lange, Mar 22 '16 at 15:26
@SeanLange, I know :-) I just took the original code and changed it. At the moment I'm writing a small example to create this on the fly ... — Shnugo, Mar 22 '16 at 15:27

John · Answer 2 · 2016-03-22T15:49:12.253

You don't need to create and maintain a list_of_dates table. You could just outter join to something like this:

For SqlServer:

SELECT 
    DATEADD(DAY,number,'20010101') [Date]
FROM 
    master..spt_values
WHERE 
    type = 'P'
    AND DATEADD(DAY,number,'20010101') <= '20010104'

Or for Oracle, this:

select 
    rownum - 1 + to_date('01-JAN-2001', 'dd-mon-yyyy') dates
from 
    all_objects
where 
    rownum < to_date('01-FEB-2001', 'dd-mon-yyyy') - to_date('01-JAN-2001', 'dd-mon-yyyy') + 2

The output from this query looks like this:

DATES    
---------
01-JAN-01
02-JAN-01
03-JAN-01
04-JAN-01
05-JAN-01
06-JAN-01
07-JAN-01
08-JAN-01
09-JAN-01
10-JAN-01
11-JAN-01
12-JAN-01
13-JAN-01
14-JAN-01
15-JAN-01
16-JAN-01
17-JAN-01
18-JAN-01
19-JAN-01
20-JAN-01
21-JAN-01
22-JAN-01
23-JAN-01
24-JAN-01
25-JAN-01
26-JAN-01
27-JAN-01
28-JAN-01
29-JAN-01
30-JAN-01
31-JAN-01
01-FEB-01

How to get rows per day (Fill gaps when no records)

2 Answers2