CODEWITHSUNDEEP
sqlsql-server-2008
Captain16
Captain16

Reputation: 327

Showing date even zero value SQL

I have SQL Query:

SELECT Date, Hours, Counts FROM TRANSACTION_DATE

Example Output: 

     Date        | Hours | Counts
   ----------------------------------
   01-Feb-2018   |   20  |   5 
   03-Feb-2018   |   25  |   3 
   04-Feb-2018   |   22  |   3 
   05-Feb-2018   |   21  |   2 
   07-Feb-2018   |   28  |   1 
   10-Feb-2018   |   23  |   1

If you can see, there are days that missing because no data/empty, but I want the missing days to be shown and have a value of zero: 

     Date        | Hours | Counts
   ----------------------------------
   01-Feb-2018   |   20  |   5 
   02-Feb-2018   |    0  |   0
   03-Feb-2018   |   25  |   3 
   04-Feb-2018   |   22  |   3 
   05-Feb-2018   |   21  |   2
   06-Feb-2018   |    0  |   0 
   07-Feb-2018   |   28  |   1 
   08-Feb-2018   |    0  |   0
   09-Feb-2018   |    0  |   0
   10-Feb-2018   |   23  |   1

Thank you in advanced.

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270351

You need to generate a sequence of dates. If there are not too many, a recursive CTE is an easy method:

with dates as (
      select min(date) as dte, max(date) as last_date
      from transaction_date td
      union all
      select dateadd(day, 1, dte), last_date
      from dates
      where dte < last_date
     )
select d.date, coalesce(td.hours, 0) as hours, coalesce(td.count, 0) as count
from dates d left join 
     transaction_date td
     on d.dte = td.date;

Upvotes: 2

Related Questions