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.
I need to a variation on select cat, sum(thevalue) from TABLE1 group by cat
which results in
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
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
so after all is said and done the final result needs to look like this
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