sql - How to extend the query to add 0 in the cell when no activity is performed

Question

I have the following query, it is working fine to show the cricket time played per day. All I need is to show 0 when no cricket is played. At the moment it is skipping those dates. For more reference please see this link.

;WITH CTE AS (
SELECT email, last_update, activity, starttime, endtime, duration as [Totaltime] from users 
WHERE activity='cricket' and email='abc'
GROUP BY email, activity, duration, starttime, endtime, last_update
)
Select activity, cast(starttime as date) as date,
SUM(datediff(second, starttime, endtime))/60.0 as TimePerDay
from cte
where starttime >= dateadd(day, -15, last_update)
group by activity, cast(starttime as date)

current output of the query is

activity | date        | TimePerDay
cricket  | 2015-06-16  | 80.01
cricket  | 2015-06-17  | 90.01
cricket  | 2015-06-18  | 120.01
cricket  | 2015-06-22  | 10.01

The desired output is

activity | date        | TimePerDay
cricket  | 2015-06-16  | 80.01
cricket  | 2015-06-17  | 90.01
cricket  | 2015-06-18  | 120.01
cricket  | 2015-06-19  | 0
cricket  | 2015-06-20  | 0
cricket  | 2015-06-21  | 0
cricket  | 2015-06-22  | 10.01

Answer 1

AS PREPARATION, you need to generate a list of dates. There are multiple ways for that. One is a recursive CTE. For example, this one creates a list of the last 15 days:

with datelist ([date]) as 
(
    select dateadd(dd, -15, cast(getdate() as date)) as [date] -- 15 days back
    union all
    select dateadd(dd, 1, [date]) from datelist where dateadd(dd, 1, [date]) <= getdate()
)
select * from datelist option (maxrecursion 400)

FOR THE FINAL SOLUTION, you now need to create a LEFT JOIN between the datelist and with a subquery with your user table. An overall solution for your question therefore goes as follows.

I have also put everything together in a SQLFiddle: http://sqlfiddle.com/#!3/36510/1

with datelist ([date]) as 
(
    select dateadd(dd, -15, cast(getdate() as date)) as [date] -- 15 days back
    union all
    select dateadd(dd, 1, [date]) from datelist where dateadd(dd, 1, [date]) <= getdate()
)
select 'cricket' as activity, 
       d.[date],
       coalesce(SUM(datediff(second, u.starttime, u.endtime)/60.0), 0)
            as TimePerDay      
from datelist d
     left join 
     (
          select [starttime], [endtime], cast(starttime as date) as [date]
          from [users]  
          where activity='cricket' and email='abc'
     ) u
     on d.[date] = u.[date]
group by d.[date]
option (maxrecursion 400)

So this query will give you the table for the cricket activity of the last 15 days of one specific user.