如何在Sql-server中总结按个别日期分组的时间 [英] how to sum the time grouped by individual day in Sql-server
本文介绍了如何在Sql-server中总结按个别日期分组的时间的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个包含 id、play、starttime 和 endtime 的表.我想找到每天的总播放时间.我认为查询将类似于以下内容,但我确定它不正确.不玩游戏时如果我得到0也会很方便,但如果很难我不介意.
I have a table with id, play, starttime and endtime. I want to find the total play time per day. I think the query will be similar as the following but I am sure it is not right. It will be also very convenient if i get 0 when no game is played but if it is difficult I dont mind.
Select
id,
play,
date,
CASE
WHEN datediff(day, starttime, endtime) = 0 then sum(totaltime)
END as TimePerDay
from cte where starttime >= '2015-05-30 17:11:34.000'
group by id, playtime, starttime, endtime
我正在寻找
id | play | Date | totaltime
1 | hockey | 05/06/2015 | 0
2 | hockey | 04/06/2015 | 0
3 | hockey | 03/06/2015 | 230
4 | hockey | 02/06/2015 | 10
5 | hockey | 01/06/2015 | 120
推荐答案
你能试试这个吗
Select play, cast(starttime as date) as date,
SUM(datediff(MINUTE, endtime, starttime)) as TimePerDay
from cte
where starttime >= '2015-05-30 17:11:34.000'
group by play, cast(starttime as date)
union
SELECT 'hockey', DATEADD(DAY,number+1,(select min(starttime) from cte)) as date, 0 as TimePerDay
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY,number+1,(select min(starttime) from cte)) < (select max(starttime) from cte)
and CAST(DATEADD(DAY,number+1,(select min(starttime) from cte)) as date) not in (select cast(starttime as date) from cte)
速成版:
DECLARE @startDate date = (select min(starttime) from cte)
DECLARE @endDate date = (select max(starttime) from cte)
;WITH dates(Date) AS
(
SELECT @startdate as Date
UNION ALL
SELECT DATEADD(d,1,[Date])
FROM dates
WHERE DATE < @enddate
)
Select play, cast(starttime as date) as date,
SUM(datediff(MINUTE, endtime, starttime)) as TimePerDay
from cte
where starttime >= '2015-05-30 17:11:34.000'
group by play, cast(starttime as date)
union
SELECT 'hockey' as play, Date, 0 as TimePerDay
FROM dates
where Date not in (select cast(starttime as date) from cte)
这篇关于如何在Sql-server中总结按个别日期分组的时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文