I have the following query which gives me data for 12 months. Originally the query was for 14 days and I changed the day to month in datediff method to get 14 months data but I am getting only 12 months data. Can any one please check and see why? The output table is attached below.
ALTER procedure [dbo].[spGetplayingTimeOftheplayersPerMonth_Updated_6_August_lastMonths]
@email nvarchar(50)
AS
Begin
Set Nocount On;
Declare @MinDate Date
,@MaxDate Date
,@LastXMonths Int
,@ForLast14Date Date
Select @LastXMonths = -14
Select @ForLast14Date = players.last_update from players where players.email=@email
--Select @MaxDate = DateADD(DAY, 14, @ForLast14Date)
Select @MaxDate = DateADD(Month, 15, Dateadd(Month, -14, @ForLast14Date))
Declare @test Table
(
activity Varchar(100)
,date Date
,TimePerDay Decimal(5,2)
)
;WITH CTE AS
(
SELECT players.email, players.last_update, Activities.activity, activities.starttime, activities.endtime, Activities.duration as [Totaltime] from players
inner join Movesplayers ON players.Id=Movesplayers.parent_id
inner join Storylines ON Movesplayers.id=Storylines.movesuser_id
inner join Segments ON Storylines.id=Segments.storyline_id
inner join Activities ON Segments.id=Activities.segment_id
--WHERE activities.activity='cricket' and (players.email=@email)
WHERE activities.activity='playing' and (players.email=@email)
GROUP BY players.email, activities.activity, activities.duration, activities.starttime, activities.endtime, players.last_update
)
Insert Into @test(activity,date,TimePerDay)
Select activity
,Cast(starttime as date) As date
,SUM(datediff(second, starttime, endtime))/60.0 As TimePerDay
From cte With (Nolock)
--where starttime >= dateadd(day, @LastXDays, last_update)
where starttime >= dateadd(Month, @LastXMonths, Dateadd(Month, -14, @ForLast14Date))
group by activity
,cast(starttime as date)
--Select @MaxDate = Getdate()
Select @MaxDate = @MaxDate
,@MinDate = dateadd(Month, (@LastXMonths + 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 activity
--,ad.xDate
,min(ad.xDate) As xDate
,Isnull(sum(t.TimePerDay),0) As TimePerDay
From AllDates As ad With (Nolock)
Left Join @test As t On ad.xDate = t.date
GROUP BY datepart(Month, ad.xDate)
--ORDER BY YEAR(datepart(Month, ad.xDate)) DESC, MONTH(datepart(Month, ad.xDate)) DESC, DAY(datepart(Month, ad.xDate))
ORDER BY MIN(ad.xDate)
option (maxrecursion 0)
END
Aucun commentaire:
Enregistrer un commentaire