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
See Question&Answers more detail:os