我有以下查询,它可以很好地显示每天播放的板球时间.我只需要在没有板球比赛时显示0.目前正在跳过这些日期.有关更多参考,请参阅此
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,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)
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
所需的输出是
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
解决方法
你也可以试试这个： – (这只适用于一个特定的活动)
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
)
一种方法是： – (左连接)
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
另一种方式是： – (插入所有日期和更新)
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
产量
更新
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
