The following query gives playing time of the users from the database on daily basis for the last 15 days. It adds 0 if no game is played. Now I want to get the data of playing time on weekly basis and 0 if no game is played in the whole week. So I want the query to give the last 15 weeks of data.
Here is the daily query.
CREATE procedure [dbo].[spGetPlayingTimeOfthepeoplesPerDay]
@email nvarchar(50)
AS
Begin
Set Nocount On;
Declare @MinDate Date, @MaxDate Date, @LastXDays Int
Select @LastXDays = -15
Select @MaxDate = peoples.l_update from peoples where peoples.email=@email
Declare @test Table
(
quantity Varchar(100)
,date Date
,TimePerDay Decimal(5,2)
)
;WITH CTE AS
(
SELECT peoples.email, peoples.l_update, act.quantity, act.starttime, act.endtime, act.duration as [Totaltime] from peoples
inner join MPeoples ON peoples.Id=MPeoples.parent_id
inner join slines ON MPeoples.id=slines.movesuser_id
inner join seg ON slines.id=seg.sline_id
inner join act ON seg.id=act.seg_id
WHERE act.quantity='playing' and (peoples.email=@email)
GROUP BY peoples.email, act.quantity, act.duration,
act.starttime, act.endtime, peoples.l_update
)
Insert Into @test(quantity,date,TimePerDay)
Select quantity
,Cast(starttime as date) As date
,SUM(datediff(second, starttime, endtime))/60.0 As TimePerDay
From cte With (Nolock)
where starttime >= dateadd(day, @LastXDays, l_update)
group by quantity
,cast(starttime as date)
Select @MaxDate = @MaxDate
,@MinDate = dateadd(day, (@LastXDays + 1), @MaxDate)
;With AllDates As
(
Select @MinDate As xDate
Union All
Select Dateadd(Day, 1, xDate)
From AllDates As ad
Where ad.xDate < @MaxDate
)
Select 'playing' As quantity
,ad.xDate
,Isnull(t.TimePerDay,0) As TimePerDay
From AllDates As ad With (Nolock)
Left Join @test As t On ad.xDate = t.date
END
Aucun commentaire:
Enregistrer un commentaire