CASE statement - help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CASE statement - help

Author

Topic

Chamark
Starting Member

28 Posts

Posted - 2010-04-02 : 16:45:33

I am attempting to get a previous year's amount (PYA) and I keep getting current year's amount and need help in understanding why this CASE statement doesn't work any maybe get some direction. I need to be able to get 2010 numbers in all columns except with one column for previous year and keep in order of select statement. Any help is greatly appreciated.

SELECT
[YearMonth],[Actual_Month],[Target_Month],[Forecast_Month],

CASE WHEN EXISTS(Select Actual_Month
FROM [SPARS].[dbo].[t_kpi_Data] WHERE YearMonth
Between '01/01/2009' AND '12/31/2009' AND DataSource_ID = '629')
THEN Actual_Month END
AS PYA

,[Actual_YTD]
,[Forecast_YTD]
[Target_YTD]

FROM [SPARS].[dbo].[t_kpi_Data]
WHERE YearMonth Between '01/01/2010' AND '12/31/2010'
AND DataSource_ID = '629'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-03 : 01:56:08

you're filtering on this years date range, then how do you get last years data? inside case when you've just used last years query using EXISTS, so it just checks for presence of data rather than retrieving it.

seems like what you need is


SELECT 
[YearMonth],[Actual_Month],[Target_Month],[Forecast_Month],
t.Actual_Month AS PYA 
,[Actual_YTD]
,[Forecast_YTD]
[Target_YTD]

FROM [SPARS].[dbo].[t_kpi_Data] k
OUTER APPLY (Select Actual_Month
FROM [SPARS].[dbo].[t_kpi_Data] 
WHERE YearMonth=DATEADD(yy,-1,k.YearMonth) AND DataSource_ID = k.DataSource_ID)t
WHERE k.YearMonth Between '01/01/2010' AND '12/31/2010'
AND k.DataSource_ID = '629'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Chamark
Starting Member

28 Posts

Posted - 2010-04-03 : 09:28:20

Hi visakh16,

Thanks for your assistance. I get

Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'Actual_Month'.

when I execute this statement - any thoughts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-03 : 12:28:45

[code]SELECT
[YearMonth],k.[Actual_Month],[Target_Month],[Forecast_Month],
t.Actual_Month AS PYA
,[Actual_YTD]
,[Forecast_YTD]
[Target_YTD]

FROM [SPARS].[dbo].[t_kpi_Data] k
OUTER APPLY (Select Actual_Month
FROM [SPARS].[dbo].[t_kpi_Data]
WHERE YearMonth=DATEADD(yy,-1,k.YearMonth) AND DataSource_ID = k.DataSource_ID)t
WHERE k.YearMonth Between '01/01/2010' AND '12/31/2010'
AND k.DataSource_ID = '629'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Chamark
Starting Member

28 Posts

Posted - 2010-04-04 : 10:08:32

Thank you so much visakh16 - that worked. You rock!!! So much to learn so little time

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-04 : 13:48:11

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources