CASE Returning NULL

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

here ur doin a SUM in case statement...if ne of the data whose sum is being taken is null..it'll show the result as null...guess thats wats hapennin...check the data...|||

Thank you for your response. I tried to just set the True condition of the first WHEN to zero, and still got NULL. Then I tried to edit the exiting True condition from SUM(PY.JanRev) to SUM(ISNULL(PY.JanRev,0)) and still got NULL.

cdun2

|||

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

|||put an else in both inner and outer case...see if it goes there...|||

o..u already got it...:)...

always use an else in case statements..gud practice..u nvr know when u need it..

CASE returning different data types

Hi, folks...
I was trying to write a sql query which returns different data types, but it
returns every time, a smalldatetime format...
SELECT CASE WHEN Tipo = 'N' THEN Numerico
WHEN Tipo = 'L' THEN Logico
WHEN Tipo = 'P' THEN Percentual
WHEN Tipo = 'D' THEN Data
ELSE NULL
END as ValorParametro
FROM ParametrosEmissores
WHERE Codigo = 'TRUNCANOME'
'Numerico' is an integer field
'L' is a bit field
'P' is a float field
'D' is a smalldatetime field.
Whatever is the datatype returning, the result is a smalldatetime field...
Any help would be apreciated.
Daniela.CASE is an expression -- and by definition every path of an expression must
return the same datatype (think of a function in a procedural language --
any function you define can only have a single return datatype). You might
consider, in this case, casting all return values to a string datatype.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Daniela Binatti" <Daniela Binatti@.discussions.microsoft.com> wrote in
message news:39E5A440-51BB-4DAF-A73B-8BDFD9E136F7@.microsoft.com...
> Hi, folks...
> I was trying to write a sql query which returns different data types, but
it
> returns every time, a smalldatetime format...
> SELECT CASE WHEN Tipo = 'N' THEN Numerico
> WHEN Tipo = 'L' THEN Logico
> WHEN Tipo = 'P' THEN Percentual
> WHEN Tipo = 'D' THEN Data
> ELSE NULL
> END as ValorParametro
> FROM ParametrosEmissores
> WHERE Codigo = 'TRUNCANOME'
> 'Numerico' is an integer field
> 'L' is a bit field
> 'P' is a float field
> 'D' is a smalldatetime field.
> Whatever is the datatype returning, the result is a smalldatetime field...
> Any help would be apreciated.
> Daniela.
>|||No, can't do that.. ALL Possible data values that could come out of a case
must be the same datatype. That's because the datatype is not associated
with the data item itself, but with the COLUMN of the resulting Resultset.
SQL has t oassigna a datatypes to the Column...
But not all s lost... You simply have t oCAST The data in each primitive
Table colum,n to the same datatype in the case statement
SELECT CASE WHEN Tipo = 'N' THEN Cast(Numerico As VarChar(20))
WHEN Tipo = 'L' And Logico = 1 THEN 'True'
WHEN Tipo = 'L' And Logico = 0 THEN 'False'
WHEN Tipo = 'P' THEN Cast(Percentual As VarChar(20))
WHEN Tipo = 'D' THEN Convert(VarChar(20), Data, 112)
ELSE NULL
END as ValorParametro
FROM ParametrosEmissores
WHERE Codigo = 'TRUNCANOME'
"Daniela Binatti" wrote:

> Hi, folks...
> I was trying to write a sql query which returns different data types, but
it
> returns every time, a smalldatetime format...
> SELECT CASE WHEN Tipo = 'N' THEN Numerico
> WHEN Tipo = 'L' THEN Logico
> WHEN Tipo = 'P' THEN Percentual
> WHEN Tipo = 'D' THEN Data
> ELSE NULL
> END as ValorParametro
> FROM ParametrosEmissores
> WHERE Codigo = 'TRUNCANOME'
> 'Numerico' is an integer field
> 'L' is a bit field
> 'P' is a float field
> 'D' is a smalldatetime field.
> Whatever is the datatype returning, the result is a smalldatetime field...
> Any help would be apreciated.
> Daniela.
>|||If they are different types then how do you want to display them in a
single column? Maybe we could help you better if you explain more about
what you are trying to do.
David Portas
SQL Server MVP
--|||Thank you very much. I've sorted out the problem by converting every result
in a sql_variant field.
"Daniela Binatti" wrote:

> Hi, folks...
> I was trying to write a sql query which returns different data types, but
it
> returns every time, a smalldatetime format...
> SELECT CASE WHEN Tipo = 'N' THEN Numerico
> WHEN Tipo = 'L' THEN Logico
> WHEN Tipo = 'P' THEN Percentual
> WHEN Tipo = 'D' THEN Data
> ELSE NULL
> END as ValorParametro
> FROM ParametrosEmissores
> WHERE Codigo = 'TRUNCANOME'
> 'Numerico' is an integer field
> 'L' is a bit field
> 'P' is a float field
> 'D' is a smalldatetime field.
> Whatever is the datatype returning, the result is a smalldatetime field...
> Any help would be apreciated.
> Daniela.
>

Capture Result of Update?

I have an Update statement in a stored procedure, and I want to capture
the number of rows affected for returning to the caller. If this was a
simple Insert, I'd use Scope_Identity() to get the Identity. But in
this case I want the count of all updated rows.
Thanks.
You want to use either @.@.RowCount (or RowCount_Big() if there is any
possibility that more than 2 billion rows may be affected by the Update
statement.
Something like:
Declare @.UpdateCount int
Update Mytable Set ... Where ...
Select @.UpdateCount = @.@.RowCount
Tom
Then @.UpdateCount will contain the number of rows which matched the Where
clause in your Update statement.
<bradwiseathome@.hotmail.com> wrote in message
news:1143750464.490863.149600@.v46g2000cwv.googlegr oups.com...
>I have an Update statement in a stored procedure, and I want to capture
> the number of rows affected for returning to the caller. If this was a
> simple Insert, I'd use Scope_Identity() to get the Identity. But in
> this case I want the count of all updated rows.
> Thanks.
>