DATEADD() Problem? 1

[SQLBI]

Hi,

I'm trying to extract a recordset from my SQL 2k SP3a DB and i'm not getting the results i'm expecting.

I'm trying to show the value of a particular date's sales, together with the value of that date 1 year previous.

SELECT  T.FinYear, T.FinMonth, T.FinWeek, T.[Date], F.PracticeID,
	SUM(F.Value) AS Invoiced,
	SUM(CASE WHEN T.[Date] = DATEADD(yy, -1, T.[Date]) THEN F.Value ELSE 0 END) AS 'Last Year',
	
FROM Mart_KPI..KPI_Fact AS F
	
	INNER JOIN Mart_KPI..[Dim - Time] AS T
	ON F.TimeID = T.TimeID

WHERE F.IndicatorID = 99 AND
	F.IndicatorSubID = 1 AND
	F.VersionID = 1 AND

GROUP BY T.FinYear, T.FinMonth, T.FinWeek, T.[Date], F.PracticeID

ORDER BY F.PracticeID, T.[Date], T.FinYear, T.FinMonth

All i keep getting in the 'Last Year' column however is zero, even though i know the data is there.

If i run

SELECT T.[Date], DATEADD(yy, -1, T.[Date])
FROM [Dim - Time] AS T

the DATEADD() function works perfectly, so i'm in 2 minds as to whether or not this is the cause of my problem.

Can anyone point me in the right direction?

Thanks in advance.




Leigh Moore
Business Systems Manager
Vets4Pets Veterinary Group

http://www.vets4pets.com

 

[donutman]

You shouldn't group by T.[Date]. You should group by Year(T.[Date]) so add that to both your Select clause and Group By clause. Remove T.[Date] from both. You won't need a Last Year column at all, but you will get new rows for the past years which you can filter out if there are too many.
Play with that for awhile and report back if you need further help. Hint: a derived table would allow you to report Last year's results in the same row as this year's.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

... and this will never evaluate to true:

CASE WHEN T.[Date] = DATEADD(yy, -1, T.[Date]) THEN F.Value ELSE 0 END

 

[kavin]

Is ur T.[Date] is Date + Time field, If so then it is problem?

 

[SQLBI]

donutman,

I've had to use T.[Date] as the report that i'm going to base this recordset on will show sales for each day, week and month of a particular period.

Also, my financial year is Apr to Mar, so by using YEAR() i'll have the wrong Jan-Mar figures returned.

I'll have a play with a derived table, but does this need to be in the SELECT statement or the WHERE clause?

Thanks for the help.


Leigh Moore
Business Systems Manager
Vets4Pets Veterinary Group

http://www.vets4pets.com

 

[SQLBI]

By Jove i think i've got it...

I've simplified my code and come up with the following statement

SELECT  T.[Date], F.PracticeID, SUM(F.Value) AS 'Invoiced', SUM(D.[Last Year]) AS 'Last Year',
	((SUM(F.Value)-SUM(D.[Last Year]))/SUM(D.[Last Year])) AS '% to Last Year'
		
FROM Mart_KPI..KPI_Fact AS F
	
	INNER JOIN Mart_KPI..[Dim - Time] AS T
	ON F.TimeID = T.TimeID

	LEFT OUTER JOIN (SELECT T.[Date], F.PracticeID, SUM(F.Value) AS 'Last Year'
			FROM Mart_KPI..KPI_Fact AS F
		
				INNER JOIN Mart_KPI..[Dim - Time] AS T
				ON F.TimeID = T.TimeID

			WHERE F.IndicatorID = 99 AND
			F.IndicatorSubID = 1 AND
			F.VersionID = 1

			GROUP BY T.[Date], F.PracticeID) AS D
	
	ON (D.[Date] = DATEADD(yy, -1, T.[Date]) AND D.PracticeID = F.PracticeID)

WHERE F.IndicatorID = 99 AND
	F.IndicatorSubID = 1 AND
	F.VersionID = 1

GROUP BY T.[Date], F.PracticeID

ORDER BY F.PracticeID, T.[Date]

This gives me the correct info and a NULL value if there is no prior year data.

Thanks for your help guys, donutman have a star!


Leigh Moore
Business Systems Manager
Vets4Pets Veterinary Group

http://www.vets4pets.com