May 21, 2008 at 8:59 am
That was a good one 🙂
The idea is to get normalized numbers for months (1..12) and join on them.
the ; must be there if with is not first statement in batch.
create table mySales (Unit_ID varchar(20),
Jul numeric(28,9), Aug numeric(28,9), Sep numeric(28,9),
Oct numeric(28,9), Nov numeric(28,9), [Dec] numeric(28,9),
Jan numeric(28,9), Feb numeric(28,9), Mar numeric(28,9),
Apr numeric(28,9), May numeric(28,9), Jun numeric(28,9))
go
insert into mySales (Unit_ID, Jul, Aug, Sep, Oct, Nov, [Dec], Jan, Feb, Mar, Apr, May, Jun)
select 'Italy', 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120
insert into mySales (Unit_ID, Jul, Aug, Sep, Oct, Nov, [Dec], Jan, Feb, Mar, Apr, May, Jun)
select 'France', 15, 25, 35, 45, 55, 65, 75, 85, 95, 105, 115, 125
insert into mySales (Unit_ID, Jul, Aug, Sep, Oct, Nov, [Dec], Jan, Feb, Mar, Apr, May, Jun)
select 'Spain', 17, 27, 37, 47, 57, 67, 77, 87, 97, 107, 117, 127
;with accumulated (Unit_ID, month_value, month_number, normalized_month_number)
as
(
select convert(varchar(20), Unit_ID) Unit_ID,
convert(numeric(28,9),month_value) month_value,
convert(int, datepart(m, '01-' + month_name + '-2008')) as month_number,
convert(int, (datepart(m, '01-' + month_name + '-2008') + 5) % 12) + 1 as normalized_month_number
from mySales
unpivot (month_value for month_name in (jul,aug,sep,oct,nov,[dec],jan,feb,mar,apr,may,jun)) as month
where ((datepart(m, '01-' + month_name + '-2008') + 5) % 12 + 1) = 1
union all
select convert(varchar(20), a.Unit_ID) Unit_ID,
convert(numeric(28, 9), a.month_value + b.month_value) month_value,
convert(int, datepart(m, '01-' + a.month_name + '-2008')) as month_number,
convert(int, (datepart(m, '01-' + month_name + '-2008') + 5) % 12) + 1 as normalized_month_number
from mySales
unpivot (month_value for month_name in (jul,aug,sep,oct,nov,[dec],jan,feb,mar,apr,may,jun)) as a
inner join accumulated b on ((datepart(m, '01-' + month_name + '-2008') + 5) % 12 + 1) = b.normalized_month_number + 1
and a.Unit_ID = b.Unit_ID
)
select * from accumulated
order by Unit_ID, normalized_month_number
go
drop table mySales
Piotr
...and your only reply is slàinte mhath
May 21, 2008 at 1:36 pm
Piotr you're a star, that's what I was looking for! Thanks a lot for your help, much appreciated!
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply