CASE WHEN statement? - 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 WHEN statement?

Author

Topic

TPie9
Yak Posting Veteran

67 Posts

Posted - 2009-07-14 : 10:53:35

I'm new to SQL (less than a year) and I'm trying to join a sales table with a AR table by period and year for individual stations. When the query results come back I'm getting sales multiple times for one period for each AR aging bucket and I only need the information in the query results / table once for each period. I've tried a CASE WHEN clause on the ARaging column but the aging can change from period to period. I've included the 2 tables below, the join results along with the query that gave me the join results and the desired results. Any assistance would be appreciated. Thanks

Sales
Period Year Sales
1 2008 81,571.08
2 2008 46,060.49
3 2008 39,416.18
4 2008 93,367.95
5 2008 29,494.07
6 2008 99,478.77
7 2008 171,542.39
8 2008 168,073.91
9 2008 191,510.67
10 2008 203,519.33
11 2008 21,296.81
12 2008 (1,200.00)
1 2009 (452.94)
2 2009 1,750.00
3 2009 -
4 2009 -
5 2009 -

AR
Month Year ARAging ARAmount
9 2008 Current 168,422.40
9 2008 31-45 71,244.10
9 2008 46-60 3,500.00
9 2008 61-90 4,709.50
9 2008 Over 90 914.10
10 2008 Current 179,669.79
10 2008 31-45 102,840.63
10 2008 46-60 7,700.00
10 2008 61-90 9,324.00
10 2008 Over 90 (25.75)
11 2008 Current 3,096.05
11 2008 31-45 43,287.26
11 2008 46-60 26,094.20
11 2008 61-90 61,363.65
11 2008 Over 90 2,790.50
12 2008 61-90 560.25
12 2008 Over 90 5,399.56
1 2009 Current (1,006.34)
1 2009 Over 90 329.75
2 2009 46-60 743.66
2 2009 Over 90 329.75
3 2009 Over 90 329.75
4 2009 Over 90 329.75
5 2009 Over 90 329.75
6 2009 Over 90 329.75

select a.*,
case when s.sales is null then 0 else s.sales end as Sales
from AR a
left join Sales s
on a.month = s.period and a.year = s.year
order by a.year, a.month

Join Results
Month Year ARAging ARAmount Sales
9 2008 Current 168,422.40 191,510.67
9 2008 31-45 71,244.10 191,510.67
9 2008 46-60 3,500.00 191,510.67
9 2008 61-90 4,709.50 191,510.67
9 2008 Over 90 914.10 191,510.67
10 2008 Current 179,669.79 203,519.33
10 2008 31-45 102,840.63 203,519.33
10 2008 46-60 7,700.00 203,519.33
10 2008 61-90 9,324.00 203,519.33
10 2008 Over 90 (25.75) 203,519.33
11 2008 Current 3,096.05 21,296.81
11 2008 31-45 43,287.26 21,296.81
11 2008 46-60 26,094.20 21,296.81
11 2008 61-90 61,363.65 21,296.81
11 2008 Over 90 2,790.50 21,296.81
12 2008 61-90 560.25 (1,200.00)
12 2008 Over 90 5,399.56 (1,200.00)
1 2009 Current (1,006.34) (452.94)
1 2009 Over 90 329.75 (452.94)
2 2009 46-60 743.66 1,750.00
2 2009 Over 90 329.75 1,750.00
3 2009 Over 90 329.75 -
4 2009 Over 90 329.75 -
5 2009 Over 90 329.75 -
6 2009 Over 90 329.75 -

Desired Results
Month Year ARAging ARAmount Sales
9 2008 Current 168,422.40 191,510.67
9 2008 31-45 71,244.10 -
9 2008 46-60 3,500.00 -
9 2008 61-90 4,709.50 -
9 2008 Over 90 914.10 -
10 2008 Current 179,669.79 203,519.33
10 2008 31-45 102,840.63 -
10 2008 46-60 7,700.00 -
10 2008 61-90 9,324.00 -
10 2008 Over 90 (25.75) -
11 2008 Current 3,096.05 21,296.81
11 2008 31-45 43,287.26 -
11 2008 46-60 26,094.20 -
11 2008 61-90 61,363.65 -
11 2008 Over 90 2,790.50 -
12 2008 61-90 560.25 (1,200.00)
12 2008 Over 90 5,399.56 -
1 2009 Current (1,006.34) (452.94)
1 2009 Over 90 329.75 -
2 2009 46-60 743.66 1,750.00
2 2009 Over 90 329.75 -
3 2009 Over 90 329.75 -
4 2009 Over 90 329.75 -
5 2009 Over 90 329.75 -
6 2009 Over 90 329.75 -

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-14 : 11:13:22

[code]
case ARAging
when 'Current' then case when s.sales is null then 0 else s.sales end
else ''
end as sales
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-14 : 11:14:50

[code]
case ARAging
when 'Current' then isnull(s.sales,0)
else ''
end as sales
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

TPie9
Yak Posting Veteran

67 Posts

Posted - 2009-07-14 : 11:52:17

Thanks for the reply, but it doesn't give me exactly what I'm looking for. Periods 12/2008 and 2/2009 have sales for the month but no 'current' AR so sales for those months return a value of zero because the WHEN portion calls for 'current'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-14 : 14:10:50

[code]select a.*,
case when s.sales is null then 0 else s.sales end as Sales
from (select row_number() over (partition by month,year order by ARAging) AS Seq,* from AR) a
left join Sales s
on a.month = s.period and a.year = s.year and Seq=1
order by a.year, a.month
[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-14 : 14:37:13

[code]declare @Sales table (period int,[year] int, sales money)

insert @Sales
select 1, 2008, 81571.08 union all
select 2, 2008, 46060.49 union all
select 3, 2008, 39416.18 union all
select 4, 2008, 93367.95 union all
select 5, 2008, 29494.07 union all
select 6, 2008, 99478.77 union all
select 7, 2008, 171542.39 union all
select 8, 2008, 168073.91 union all
select 9, 2008, 191510.67 union all
select 10, 2008, 203519.33 union all
select 11, 2008, 21296.81 union all
select 12, 2008, 1200.00 union all
select 1, 2009, 452.94 union all
select 2, 2009, 1750.00 union all
select 3, 2009, null union all
select 4, 2009, null union all
select 5, 2009, null

declare @AR table ([month] int, [year] int, araging varchar(255), aramount money)

insert @AR
select 9, 2008, 'Current', 168422.40 union all
select 9, 2008, '31-45', 71244.10 union all
select 9, 2008, '46-60', 3500.00 union all
select 9, 2008, '61-90', 4709.50 union all
select 9, 2008, 'Over 90', 914.10 union all
select 10, 2008, 'Current', 179669.79 union all
select 10, 2008, '31-45', 102840.63 union all
select 10, 2008, '46-60', 7700.00 union all
select 10, 2008, '61-90', 9324.00 union all
select 10, 2008, 'Over 90', 25.75 union all
select 11, 2008, 'Current', 3096.05 union all
select 11, 2008, '31-45', 43287.26 union all
select 11, 2008, '46-60', 26094.20 union all
select 11, 2008, '61-90', 61363.65 union all
select 11, 2008, 'Over 90', 2790.50 union all
select 12, 2008, '61-90', 560.25 union all
select 12, 2008, 'Over 90', 5399.56 union all
select 1, 2009, 'Current', 1006.34 union all
select 1, 2009, 'Over 90', 329.75 union all
select 2, 2009, '46-60', 743.66 union all
select 2, 2009, 'Over 90', 329.75 union all
select 3, 2009, 'Over 90', 329.75 union all
select 4, 2009, 'Over 90', 329.75 union all
select 5, 2009, 'Over 90', 329.75 union all
select 6, 2009, 'Over 90', 329.75

select
[month], [year], araging, aramount,
case rownum
when 1 then convert(varchar(25),isnull(sales,0))
else '-'
end as sales
from
(
select a.*,
case when s.sales is null then 0 else s.sales end as Sales,
row_number() over (partition by a.[year],a.[month] order by ascii(replace(left(araging,1),'c','0'))) as rownum
from @AR a
left join @Sales s
on a.[month] = s.period and a.[year] = s.[year]
)dt
order by [year], [month]
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

TPie9
Yak Posting Veteran

67 Posts

Posted - 2009-07-14 : 14:41:58

Thanks. That's what I was looking for.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-14 : 14:42:49

welcome