Case Statement in sqlserver - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Case Statement in sqlserver

Author

Topic

kingstonr
Starting Member

3 Posts

Posted - 2008-06-05 : 12:21:48

select id,qty into #lastyrend from table where dt='12/31/2007'--last yr end

select id, qty into #lastmonthend from table where dt='05/31/2008' --last month end

select id, qty into #lastqtrend from table where dt='03/31/2008'--last qtr end

select id,qty into #lastweeked from table where dt='05/27/2008'

select id,#lastyrend .qty,

case when #lastmonthend.qty is null then #lastyrend .qty as lastmonthend

case when #lastmonthend.qty is null then #lastyrend .qty
when #lastqtrend .qty is null then #lastmonthend.qty .qty else #lastqtrend .qty as lastqtr

case when #lastmonthend.qty is null then #lastyrend .qty
when #lastqtrend .qty is null then #lastmonthend.qty
when #lastweeked .qty is null then #lastqtrend .qty else #lastweeked .qty as lastweekend.

from table
left join table.id=#lastyrend .id
left join table.id=#lastmonthend.id
left join table.id=#lastqtrend.id
left join table.id=#lastweeked.id

IN my query I am taking values from a table for different date series.

logic is if there is no value for a particular date then take the previous date value.

Problem
--------------

in my query #lastqtrend .qty is null but #lastweeked .qty has the value.

as per my third case statement I expected I will take the #lastweeked .qty

but since #lastqtrend .qty is null it shows the value of #lastmonthend.qty
I think here after the

case when #lastmonthend.qty is null then #lastyrend .qty
when #lastqtrend .qty is null then #lastmonthend.qty ---- after executing this line below line is not executing
when #lastweeked .qty is null then #lastqtrend .qty else

Can somebody help me how to execute this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-05 : 12:31:48

[code]from table
left join #lastyrend on table.id = #lastyrend.id
left join #lastmonthend on table.id = #lastmonthend.id
left join #lastqtrend on table.id = #lastqtrend.id
left join #lastweeked on table.id = #lastweeked.id[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources