我有以下查询,它可以很好地显示每天播放的板球时间.我只需要在没有板球比赛时显示0.目前正在跳过这些日期.有关更多参考,请参阅此
link.
@H_403_2@;WITH CTE AS (
SELECT email,last_update,activity,starttime,endtime,duration as [Totaltime] from users
WHERE activity='cricket' and email='abc'
GROUP BY email,duration,last_update
)
Select activity,cast(starttime as date) as date,SUM(datediff(second,endtime))/60.0 as TimePerDay
from cte
where starttime >= dateadd(day,-15,last_update)
group by activity,cast(starttime as date)
@H_403_2@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
所需的输出是
@H_403_2@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解决方法
你也可以试试这个： – (这只适用于一个特定的活动)
@H_403_2@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),2) Default 0
)
;WITH CTE AS
(
SELECT email,duration As Totaltime
From users With (Nolock)
WHERE activity ='cricket'
And email = 'abc'
GROUP BY email,last_update
)
Insert Into @test(activity,date,TimePerDay)
Select activity,Cast(starttime as date) As date,endtime))/60.0 As TimePerDay
From cte With (Nolock)
where starttime >= dateadd(day,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
)
一种方法是： – (左连接)
@H_403_2@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另一种方式是： – (插入所有日期和更新)
@H_403_2@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产量
更新
@H_403_2@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,xDate) From AllDates As ad Where ad.xDate < @MaxDate ) Select @activity As activity ---- @activity (your stored procedure parameter),0) As TimePerDay From AllDates As ad With (Nolock) Left Join @test As t On ad.xDate = t.date