How to extend request to add 0 to cell when no activity is running
I have the following request, it works great to show the cricket time per day. All I need to do is show 0 when no cricket is playing. At the moment he is missing these dates. For more information see 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 query output
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
Desired output:
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
+3
source to share
7 replies
you can also try: - (this can only be for one specific action)
Set Nocount On;
Declare @MinDate Date
,@MaxDate Date
Declare @test Table
(
activity Varchar(100)
,date Date
,TimePerDay Decimal(5,2)
)
Declare @result Table
(
activity Varchar(100)
,date Date
,TimePerDay Decimal(5,2) Default 0
)
;WITH CTE AS
(
SELECT email
,last_update
,activity
,starttime
,endtime
,duration As Totaltime
From users With (Nolock)
WHERE activity ='cricket'
And email = 'abc'
GROUP BY email
,activity
,duration
,starttime
,endtime
,last_update
)
Insert Into @test(activity,date,TimePerDay)
Select activity
,Cast(starttime as date) As date
,SUM(datediff(second, starttime, endtime))/60.0 As TimePerDay
From cte With (Nolock)
where starttime >= dateadd(day, -15, last_update)
group by activity
,cast(starttime as date)
Select @MinDate = Min(Date)
,@MaxDate = Max(Date)
From @test
;With AllDates As
(
Select @MinDate As xDate
From @test As t1
Where t1.date = @MinDate
Union All
Select Dateadd(Day, 1, xDate)
From AllDates As ad
Where ad.xDate < @MaxDate
)
One way: - (left join)
Select 'cricket' As activity
,ad.xDate
,Isnull(t.TimePerDay,0) As TimePerDay
From AllDates As ad With (Nolock)
Left Join @test As t On ad.xDate = t.date
another way: - (insert with all dates and update)
Insert Into @result(activity,date)
Select 'cricket'
,ad.xDate
From AllDates As ad With (Nolock)
Update t
Set t.TimePerDay = t1.TimePerDay
From @result As t
Join @test As t1 On t.date = t1.date
Select *
From @result As r
Output
Update
Declare @MinDate Date
,@MaxDate Date
Select @MaxDate = Getdate()
,@MinDate = Dateadd(Day, -14, @MaxDate)
;With AllDates As
(
Select @MinDate As xDate
Union All
Select Dateadd(Day, 1, xDate)
From AllDates As ad
Where ad.xDate < @MaxDate
)
Select @activity As activity ---- @activity (your stored procedure parameter)
,ad.xDate
,Isnull(t.TimePerDay,0) As TimePerDay
From AllDates As ad With (Nolock)
Left Join @test As t On ad.xDate = t.date
+2
source to share
PREPARATION AS, you need to create a list of dates. There are several ways to do this. One of them is recursive CTE. For example, this 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 need to create LEFT JOIN
between datelist
and with a subquery with your table user
. So the general solution for your question is as follows.
I've also pieced it all 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 a table for the cricket activity for the last 15 days for one particular user.
+3
source to share
You need a list of dates. The easiest way - if you have data for each date, but not for conditions where
- is to use conditional aggregation:
Select activity, cast(starttime as date) as date,
SUM(case when activity = 'cricket' and email = 'abc'
then datediff(second, starttime, endtime))/60.0
else 0
end) as TimePerDay
from users
where starttime >= dateadd(day, -15, last_update)
group by activity, cast(starttime as date);
Otherwise, if the dates are not listed in the table, you need a list of dates. This can come from:
- Calendar table.
- Recursive CTE.
- Explicit list in request.
+2
source to share
First you need to generate a Date series with a query like this:
-- Generate 15 past days name from now
set @n:=date(now() + interval 1 day);
select (select @n:= @n - interval 1 day) day_series from users limit 15;
and then attach the select with date_series. for null values, you can use COALESCE:
SELECT q2.day_series as days , COALESCE(TimePerDay, 0) as TPD from Your_select q1
right join (
-- select date_series
) as q2
on q1.Timestamp = q2.day_series and ....
--- final request based on my understanding from the first request
set @n:=date(now() + interval 1 day);
SELECT activity
,cast(starttime AS DATE) AS q1_DATE
,COALESCE(SUM(datediff(second, starttime, endtime)) / 60.0, 0) AS TimePerDay
FROM (
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
) q1
RIGHT JOIN (
SELECT (
SELECT @n: = @n - interval 1 day
) day_series
FROM users limit 15
) q2 ON q1.q1_DATE = q2.day_series
WHERE starttime >= dateadd(day, - 15, last_update)
GROUP BY activity
,cast(starttime AS DATE);
+2
source to share
try it
;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
into #tempcte
from CTE
--where starttime >= dateadd(day, -15, last_update)
group by activity, cast(starttime as date)
select * from #tempcte
DECLARE @startDate date = (select min([date]) from #tempcte)
DECLARE @endDate date = (select max([date]) from #tempcte)
;WITH dates(Date) AS
(
SELECT @startdate as Date
UNION ALL
SELECT DATEADD(d,1,dates.Date)
FROM dates
WHERE dates.Date < @enddate --and dates.Date not in (select [date] from #tempcte)
)
--select * from dates
Select activity, [date], TimePerDay
from #tempcte
union
select 'cricket' activity, [Date] as Date, 0 as TimePerDay FROM dates where dates.Date not in (select [date] from #tempcte)
drop table #tempcte
+2
source to share
Try it. You can generate missing dates in a join using an existing CTE as the basis for Row_Number ()
;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)
UNION ALL
Select 'cricket' activity, everyday.[date], 0
FROM
(
select top 1000 dateadd(day,row_number() OVER (order by starttime),(select cast(min(starttime) as date) from CTE)) [date]
from CTE
) everyday
WHERE everyday.[date] NOT IN (SELECT cast(starttime as date) FROM CTE)
AND everyday.[date] < (SELECT cast(max(starttime) as date) from CTE)
ORDER BY date
+2
source to share
This is a long request, but should give you what you need. It will also work for multiple actions.
;
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
),
cteSummary
AS ( 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)
),
cteDateRange
AS ( SELECT activity ,
MIN(date) AS MinDate ,
MAX(date) AS MaxDate
FROM cteSummary
GROUP BY activity
),
cteDateRecur
AS ( SELECT activity ,
CalDate = CONVERT(DATE, MinDate) ,
MaxDate
FROM cteDateRange
UNION ALL
SELECT activity ,
CalDate = DATEADD(DAY, 1, c.CalDate) ,
MaxDate
FROM cteDateRecur c
WHERE c.CalDate < c.MaxDate
)
SELECT d.activity ,
d.CalDate AS date ,
ISNULL(s.TimePerDay, 0) AS TimePerDay
FROM cteDateRecur d
LEFT JOIN cteSummary s ON d.CalDate = s.date
OPTION ( MAXRECURSION 0 )
+2
source to share