How to get all days in a date range even if no data exists also in SQL Server?

Question

I have one table name called Employee. I would like to get all date even if any one of the days is missing also.

Table :

create table Employee(id int, dateCheck datetime)
insert into Employee values(1, '2016/01/01')
insert into Employee values(2, '2016/01/02')
insert into Employee values(3, '2016/01/05')
insert into Employee values(4, '2016/01/07')

E.g:

+-------------------+--------------------------+
|Name               | dateCheck                |
+-------------------+--------------------------+
| 1                 | 2016-01-01 00:00:00.000  |
| 2                 | 2016-01-02 00:00:00.000  |
| 3                 | 2016-01-05 00:00:00.000  |
| 4                 | 2016-01-07 00:00:00.000  |
+-------------------+--------------------------+

I need output like below :

+-------------------+--------------------------+
|Name               | dateCheck                |
+-------------------+--------------------------+
| 1                 | 2016-01-01 00:00:00.000  |
| 2                 | 2016-01-02 00:00:00.000  |
| 0                 | 2016-01-03 00:00:00.000  |
| 0                 | 2016-01-04 00:00:00.000  |
| 3                 | 2016-01-05 00:00:00.000  |
| 0                 | 2016-01-06 00:00:00.000  |
| 4                 | 2016-01-07 00:00:00.000  |
+-------------------+--------------------------+

Please help me to solve this task.

Better create a calendar table and cross join it with your table. — gofr1, Jun 26 '16 at 16:11

nazark · Answer 1 · 2016-06-26T15:05:20.353

2

try

;WITH dt (mindt, mxdt) 
     AS (SELECT Min(dateCheck), 
                Max(dateCheck) 
         FROM   Employee 
         UNION ALL 
         SELECT Dateadd(day, 1, mindt), 
                mxdt 
         FROM   dt 
         WHERE  Dateadd(day, 1, mindt) <= mxdt) 
SELECT isnull(em.id,0) as id, 
       dt.mindt 
FROM   dt 
       LEFT JOIN employees em 
              ON mindt = em.dateCheck

edited Jun 26 '16 at 15:05

answered Jun 26 '16 at 14:37

nazark

1,240
2
10
15

Upvoted, but definitely there are better ways to generate Dates like Tally table – Pரதீப் Jun 26 '16 at 14:42
I assume there must be `FULL OUTER JOIN` and not `LEFT JOIN` – gofr1 Jun 26 '16 at 19:45
you could simply check the result,left join is fine as dt includes all the data, thank you – nazark Jun 27 '16 at 06:36

score 0 · Answer 2 · edited May 23 '17 at 12:08

0

Click here for a suggested approach. Tally tables are great, however, some of the implementations are not performant. Having a physical date table can sometime help tremendously.

edited May 23 '17 at 12:08

Community

1
1

answered Jun 26 '16 at 15:38

square_particle

526
2
7

How to get all days in a date range even if no data exists also in SQL Server?

2 Answers2