CODEWITHSUNDEEP
sql-server
dhamo
dhamo

Reputation: 181

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

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.

Upvotes: 1

Views: 685

Answers (2)

square_particle
square_particle

Reputation: 524

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.

Upvotes: 0

nazark
nazark

Reputation: 1240

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

Upvotes: 2

Related Questions