Friday, February 24, 2012

Attn TSQL Challenge Seekers

Hi Folks - Well this is going to be a fun ride. I have a simple table that I need to do some slightly not so simple logic on. I am using SQL2005 and this happens in a stored proc. I need to do this in straight T-SQL and w/o using a cursor, you can use a CTE if needed.

ID PARENTID CAT CATRANK THEVALUE 1 1 A 0 11 2 1 B 1 22 3 1 C 2 33 4 2 A 0 44 5 2 B 1 55 6 2 C 2 66 7 3 A 0 12 8 3 B 1 13 9 4 A 0 14 10 4 B 1 15 11 4 C 2 16

I need to a variation on select cat, sum(thevalue) from TABLE1 group by cat

which results in

CAT THEVALUE A 81 B 105 C 115

the wrinkle is that each item in the group by (A,B,C) needs to consider 1 row from each parentid. in other words if the system is calculating the C row, and a parentid is missing a C entry then the system needs to use the B entry for that parentid.

so to calculate the A row we simply do. this is simple because there is an A row for every parentid

PARENTID CAT THEVALUE 1 A 11 2 A 44 3 A 12 4 A 14 SUM 81

the C row is the tricky part. there is no C for parentid 3, so in that case I need to use the next lower ranked item, which is a B

PARENTID CAT THEVALUE 1 C 33 2 C 66 3 B 13 4 C 16 SUM 128

so after all is said and done the final result needs to look like this

CAT THEVALUE A 81 B 105 C 128

Try:

Code Snippet

use tempdb

go

create table dbo.t1 (

ID int not null,

PARENTID int not null,

CAT char(1) not null,

CATRANK int not null,

THEVALUE int not null

)

go

insert into dbo.t1 values(1, 1, 'A', 0, 11)

insert into dbo.t1 values(2, 1, 'B', 1, 22)

insert into dbo.t1 values(3, 1, 'C', 2, 33)

insert into dbo.t1 values(4, 2, 'A', 0, 44)

insert into dbo.t1 values(5, 2, 'B', 1, 55)

insert into dbo.t1 values(6, 2, 'C', 2, 66)

insert into dbo.t1 values(7, 3, 'A', 0, 12)

insert into dbo.t1 values(8, 3, 'B', 1, 13)

insert into dbo.t1 values(9, 4, 'A', 0, 14)

insert into dbo.t1 values(10, 4, 'B', 1, 15)

insert into dbo.t1 values(11, 4, 'C', 2, 16)

go

select

c.cat,

sum(d.thevalue) as sum_thevalue

from

(

select

parentid,

cat,

catrank

from

(

select distinct

cat, catrank

from

dbo.t1

) as a

cross join

(

select distinct

parentid

from

dbo.t1

) as b

) as c

inner join

dbo.t1 as d

on d.parentid = c.parentid

and d.catrank = (

select max(e.catrank)

from dbo.t1 as e

where e.parentid = c.parentid and e.catrank <= c.catrank

)

group by

c.cat

order by

c.cat

go

drop table dbo.t1

go

AMB

|||Oh Snap! that was fast ... ty I will look at this

No comments:

Post a Comment