September 28, 2009 at 6:38 am
I have written the following which returns exacly what I want:
create table #Temp
( id int,
item varchar(5),
f_year int,
f_month int,
qty decimal(18,2)
)
insert into #Temp values (1,'AAA',2008,01,50)
insert into #Temp values (1,'AAA',2008,06,10)
insert into #Temp values (2,'AAA',2008,10,500)
select id, item, f_year,
case when Total > 0 then (P1 / Total) * 100 else 0 end as 'P1',
case when Total > 0 then (P2 / Total) * 100 else 0 end as 'P2',
case when Total > 0 then (P3 / Total) * 100 else 0 end as 'P3',
case when Total > 0 then (P4 / Total) * 100 else 0 end as 'P4',
case when Total > 0 then (P5 / Total) * 100 else 0 end as 'P5',
case when Total > 0 then (P6 / Total) * 100 else 0 end as 'P6',
case when Total > 0 then (P7 / Total) * 100 else 0 end as 'P7',
case when Total > 0 then (P8 / Total) * 100 else 0 end as 'P8',
case when Total > 0 then (P9 / Total) * 100 else 0 end as 'P9',
case when Total > 0 then (P10 / Total) * 100 else 0 end as 'P10',
case when Total > 0 then (P11 / Total) * 100 else 0 end as 'P11',
case when Total > 0 then (P12 / Total) * 100 else 0 end as 'P12'
from
(
select id, item, f_year,
sum(case when f_month = 01 then qty else 0 end) as 'P1',
sum(case when f_month = 02 then qty else 0 end) as 'P2',
sum(case when f_month = 03 then qty else 0 end) as 'P3',
sum(case when f_month = 04 then qty else 0 end) as 'P4',
sum(case when f_month = 05 then qty else 0 end) as 'P5',
sum(case when f_month = 06 then qty else 0 end) as 'P6',
sum(case when f_month = 07 then qty else 0 end) as 'P7',
sum(case when f_month = 08 then qty else 0 end) as 'P8',
sum(case when f_month = 09 then qty else 0 end) as 'P9',
sum(case when f_month = 10 then qty else 0 end) as 'P10',
sum(case when f_month = 11 then qty else 0 end) as 'P11',
sum(case when f_month = 12 then qty else 0 end) as 'P12',
sum(qty) as 'Total'
from #Temp
where f_year = 2008
group by id, item, f_year
) p
order by id, f_year, item
drop table #Temp
Basically the idea is to work out what percentage of the year's total each month's item sales represent. For example, if sales for a particular customer all occured in January then January would be represented as 100%. If the sales were split equally between two months then each month would be represented as 50% each etc.
My question is whether what I have done can be improved and no doubt the answer will be yes!
September 28, 2009 at 11:58 pm
select id, item, f_year,
sum(case when f_month = 01 then qty else 0 end)/sum(qty)*100 as 'P1',
sum(case when f_month = 02 then qty else 0 end)/sum(qty)*100 as 'P2',
sum(case when f_month = 03 then qty else 0 end)/sum(qty)*100 as 'P3',
sum(case when f_month = 04 then qty else 0 end)/sum(qty)*100 as 'P4',
sum(case when f_month = 05 then qty else 0 end)/sum(qty)*100 as 'P5',
sum(case when f_month = 06 then qty else 0 end)/sum(qty)*100 as 'P6',
sum(case when f_month = 07 then qty else 0 end)/sum(qty)*100 as 'P7',
sum(case when f_month = 08 then qty else 0 end)/sum(qty)*100 as 'P8',
sum(case when f_month = 09 then qty else 0 end)/sum(qty)*100 as 'P9',
sum(case when f_month = 10 then qty else 0 end)/sum(qty)*100 as 'P10',
sum(case when f_month = 11 then qty else 0 end)/sum(qty)*100 as 'P11',
sum(case when f_month = 12 then qty else 0 end)/sum(qty)*100 as 'P12'
from #Temp
where f_year = 2008
group by id, item, f_year
September 29, 2009 at 1:01 am
arun.sas (9/28/2009)
select id, item, f_year,sum(case when f_month = 01 then qty else 0 end)/sum(qty)*100 as 'P1',
sum(case when f_month = 02 then qty else 0 end)/sum(qty)*100 as 'P2',
sum(case when f_month = 03 then qty else 0 end)/sum(qty)*100 as 'P3',
sum(case when f_month = 04 then qty else 0 end)/sum(qty)*100 as 'P4',
sum(case when f_month = 05 then qty else 0 end)/sum(qty)*100 as 'P5',
sum(case when f_month = 06 then qty else 0 end)/sum(qty)*100 as 'P6',
sum(case when f_month = 07 then qty else 0 end)/sum(qty)*100 as 'P7',
sum(case when f_month = 08 then qty else 0 end)/sum(qty)*100 as 'P8',
sum(case when f_month = 09 then qty else 0 end)/sum(qty)*100 as 'P9',
sum(case when f_month = 10 then qty else 0 end)/sum(qty)*100 as 'P10',
sum(case when f_month = 11 then qty else 0 end)/sum(qty)*100 as 'P11',
sum(case when f_month = 12 then qty else 0 end)/sum(qty)*100 as 'P12'
from #Temp
where f_year = 2008
group by id, item, f_year
Hi Arun,
Thanks for your reply, the only problem I can see is that the possible divide by zero is not being handled.
Regards
David
September 29, 2009 at 1:10 am
David-155102 (9/29/2009)
I can see is that the possible divide by zero is not being handled.
Handle the divided by zero error for this statement by
from #Temp
group by id, item, f_year
HAVING sum(qty) > 0.00
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply