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.
All i keep getting in the 'Last Year' column however is zero, even though i know the data is there.
If i run
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
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.
Code:
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
Code:
SELECT T.[Date], DATEADD(yy, -1, T.[Date])
FROM [Dim - Time] AS T
Can anyone point me in the right direction?
Thanks in advance.
Leigh Moore
Business Systems Manager
Vets4Pets Veterinary Group