dimanche 16 août 2015

Why the following SQL-Server query gives 12 months data and not 14 months data

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

enter image description here

Aucun commentaire:

Enregistrer un commentaire