January 29, 2007 at 4:14 pm
Hello,
I have a query that contains six derived columns;
FirstPerYr; The current year
FirstPerMo; The current month
FirstPeriodRevenue; CASE the current year, use the CurrentYearSalesTable (CY). CASE the current month, match the correct column (JanRev, FebRev, etc) of the CurrentYearSalesTable to get the correct data.
FirstPeriodYrAnnum; Last year
FirstPeriodMoAnnum; The current month, last year
FirstPeriodAnnumRev; Same as FirstPeriodRevenue, but from the year and month before.
In the following query, I get the correct FirstPeriodRev value, but FirstPeriodAnnumRev comes up NULL. I know I have data for January 2006. The query is as follows;
--**************************
DECLARE @FirstPerYr Int
DECLARE @FirstPerMo Int
DECLARE @FirstPerYrAnnum Int
DECLARE @FirstPerMoAnnum Int
SET @FirstPerYr = YEAR(GETDATE())
SET @FirstPerMo = MONTH(GETDATE())
SET @FirstPerYrAnnum = YEAR(DateAdd(Year,-1,DateAdd(Month,0,GetDate())))
SET @FirstPerMoAnnum = MONTH(DateAdd(Year,-1,DateAdd(Month,0,GetDate())))
SELECT
RTRIM(DA.AcctCode) AS DAAcctCode,
RTRIM(DA.CompanyName) AS DACompanyName,
@FirstPerYr AS FirstPerYr,
@FirstPerMo AS FirstPerMo,
"FirstPeriodRev" =
CASE
WHEN @FirstPerYr = DATEPART(YEAR,GETDATE()) THEN
CASE
WHEN @FirstPerMo = 1 THEN SUM(CY.JanRev)
WHEN @FirstPerMo = 2 THEN SUM(CY.FebRev)
WHEN @FirstPerMo = 3 THEN SUM(CY.MarRev)
WHEN @FirstPerMo = 4 THEN SUM(CY.AprRev)
WHEN @FirstPerMo = 5 THEN SUM(CY.MayRev)
WHEN @FirstPerMo = 6 THEN SUM(CY.JunRev)
WHEN @FirstPerMo = 7 THEN SUM(CY.JulRev)
WHEN @FirstPerMo = 8 THEN SUM(CY.AugRev)
WHEN @FirstPerMo = 9 THEN SUM(CY.SeptRev)
WHEN @FirstPerMo = 10 THEN SUM(CY.OctRev)
WHEN @FirstPerMo = 11 THEN SUM(CY.NovRev)
WHEN @FirstPerMo = 12 THEN SUM(CY.DecRev)
END
END,
@FirstPerYrAnnum AS FirstPerYrAnnum,
@FirstPerMoAnnum AS FirstPerMoAnnum,
"FirstPeriodAnnumRev" =
CASE
WHEN @FirstPerYrAnnum = DATEADD(YEAR,-1,GETDATE()) THEN
CASE
WHEN @FirstPerMoAnnum = 1 THEN SUM(PY.JanRev)
WHEN @FirstPerMoAnnum = 2 THEN SUM(PY.FebRev)
WHEN @FirstPerMoAnnum = 3 THEN SUM(PY.MarRev)
WHEN @FirstPerMoAnnum = 4 THEN SUM(PY.AprRev)
WHEN @FirstPerMoAnnum = 5 THEN SUM(PY.MayRev)
WHEN @FirstPerMoAnnum = 6 THEN SUM(PY.JunRev)
WHEN @FirstPerMoAnnum = 7 THEN SUM(PY.JulRev)
WHEN @FirstPerMoAnnum = 8 THEN SUM(PY.AugRev)
WHEN @FirstPerMoAnnum = 9 THEN SUM(PY.SeptRev)
WHEN @FirstPerMoAnnum = 10 THEN SUM(PY.OctRev)
WHEN @FirstPerMoAnnum = 11 THEN SUM(PY.NovRev)
WHEN @FirstPerMoAnnum = 12 THEN SUM(PY.DecRev)
END
END
FROM
SalesCommissions.dbo.DailyAccountsDownload DA
INNER JOIN (SELECT
AcctCode,
Territory,
SUM(JanRev) AS JanRev,
SUM(FebRev) AS FebRev,
SUM(MarRev) AS MarRev,
SUM(AprRev) AS AprRev,
SUM(MayRev) AS MayRev,
SUM(JunRev) AS JunRev,
SUM(JulRev) AS JulRev,
SUM(AugRev) AS AugRev,
SUM(SeptRev) AS SeptRev,
SUM(OctRev) AS OctRev,
SUM(NovRev) AS NovRev,
SUM(DecRev) AS DecRev
FROM
SalesReporting.dbo.PriorYearSales
GROUP BY
AcctCode, Territory)PY
ON RTRIM(DA.AcctCode) = RTRIM(PY.AcctCode)
INNER JOIN (SELECT
AcctCode,
SUM(JanRev) AS JanRev,
SUM(FebRev) AS FebRev,
SUM(MarRev) AS MarRev,
SUM(AprRev) AS AprRev,
SUM(MayRev) AS MayRev,
SUM(JunRev) AS JunRev,
SUM(JulRev) AS JulRev,
SUM(AugRev) AS AugRev,
SUM(SeptRev) AS SeptRev,
SUM(OctRev) AS OctRev,
SUM(NovRev) AS NovRev,
SUM(DecRev) AS DecRev
FROM
SalesReporting.dbo.CurrentYearSales
GROUP BY
AcctCode)CY
ON RTRIM(DA.AcctCode) = RTRIM(CY.AcctCode)
WHERE
RTRIM(DA.AcctCode) = 'AM940'
GROUP BY
RTRIM(DA.AcctCode), DA.CompanyName
--*************************
The result set looks like this;
DAAcctCode DACompanyName FirstPerYr FirstPerMo FirstPeriodRev FirstPerYrAnnum FirstPerMoAnnum FirstPeriodAnnumRev
AM940 Sterling Educationa 2007 1 1769.75 2006 1 NULL
If the logic for CASE is the same to find the correct column for the previous year, then why would the result for the previous year come up NULL?
Thanks again for your help!
CSDunn
January 29, 2007 at 6:40 pm
Without seeing your data, I don't see anything obviously wrong, but there are a few things I'd check, just to ensure there are no typos, etc.
First, your derived table for the previous year groups by territory, even though it isn't being used anywhere else in your script. Was that an oversight, or is there some reason to group by it, and if so, why doesn't the script reflect that reason?
Secondly, have you tried to give the case statement for the previous year a failure outlet? I'd add an ELSE to both the inner and outer CASE statements there, just in case there is an error that we both are missing.
Thirdly, what does the following return?
SELECT
AcctCode,
Territory,
SUM(JanRev) AS JanRev,
SUM(FebRev) AS FebRev,
SUM(MarRev) AS MarRev,
SUM(AprRev) AS AprRev,
SUM(MayRev) AS MayRev,
SUM(JunRev) AS JunRev,
SUM(JulRev) AS JulRev,
SUM(AugRev) AS AugRev,
SUM(SeptRev) AS SeptRev,
SUM(OctRev) AS OctRev,
SUM(NovRev) AS NovRev,
SUM(DecRev) AS DecRev
FROM
SalesReporting.dbo.PriorYearSales
GROUP BY
AcctCode, Territory
WHERE RTrim(AcctCode) = 'AM940'
January 30, 2007 at 9:35 am
I found the problem. When evaluating @FirstPerYrAnnum = DATEADD(YEAR,-1,GETDATE())), The variable @FirstPerYrAnnum contained only the YEAR portion of the date. I needed to express my test as follows;
@FirstPerYrAnnum = YEAR(DATEADD(YEAR,-1,GETDATE()))
Now I get back something that looks correct. I need to test.
Putting an ELSE condition in the outer CASE helped me find this.
Thank you again for your help!
CSDunn
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply