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