jeudi 6 août 2015

How to change the query to give last 15 weeks of data instead of last 15 days from the SQL-server

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