sql – 如何在没有执行任何活动时扩展查询以在单元格中添加0

;WITH CTE AS (
SELECT email,last_update,activity,starttime,endtime,duration as [@R_936_10586@ltime] 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

解决方法@H_772_14@

你也可以试试这个： – (这只适用于一个特定的活动)

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 @R_936_10586@ltime 
    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