April 19, 2013 at 7:46 am
Here's a tricky one:
I have to show values for each of our customers. Data comes from a table something like this:
[font="Courier New"]Customer_id Value
1 100
2 150
3 1000
4 300
.
.
.
10 120
11 150
12 1000
13 200[/font]
etc
Most customers just have one account, but some have multiple "sibling" (or "child") accounts. Those with "children" are mapped in a table like:
[font="Courier New"]Customer_id (Parent) Child_id
1 10
1 11
1 12
2 13
3 14
3 15
5 16[/font]
etc
What I need to do is display a list showing all accounts and their own values, but if an account is a Parent it needs to show the value of itself and all its children added together:
[font="Courier New"]Customer_id Value
1 1370 (100 + 120 + 150 + 1000
2 350 (150 + 200)
3 1000
.
.
.
10 120
11 150
12 1000
13 200[/font]
If a Parent doesn't exist in the Parent-Child mapping table all we show is its own value
[font="Courier New"]Customer_id Vaue
4 300[/font]
This seems like it requires a convoluted query!
Any ideas?
Thanx!
April 19, 2013 at 8:13 am
Try looking at recursive CTE or adjacency model to start. There's plenty of good articles on SSC on what you want to do.
[Edit]
Oops yeah that works. Didn't read the req. clearly, I thought you wanted to append the values like a string. Summing it is a lot easier... 🙂
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
April 19, 2013 at 8:21 am
Something like this
DECLARE@tbl_Customer TABLE
(
Customer_idINT,
ValueNUMERIC(18,2)
)
DECLARE@tbl_Relation TABLE
(
Customer_idINT,
Child_idINT
)
INSERT@tbl_Customer
SELECT1, 100 UNION ALL
SELECT2, 150 UNION ALL
SELECT3, 1000 UNION ALL
SELECT4, 300 UNION ALL
SELECT10, 120 UNION ALL
SELECT11, 150 UNION ALL
SELECT12, 1000 UNION ALL
SELECT13, 200
INSERT@tbl_Relation
SELECT1, 10 UNION ALL
SELECT1, 11 UNION ALL
SELECT1, 12 UNION ALL
SELECT2, 13 UNION ALL
SELECT3, 14 UNION ALL
SELECT3, 15 UNION ALL
SELECT5, 16
SELECTC.Customer_id, C.Value + COALESCE( SUM( CR.Value ), 0 ) AS Value
FROM@tbl_Customer AS C
LEFT OUTER JOIN@tbl_Relation AS R ON C.Customer_id = R.Customer_id
LEFT OUTER JOIN @tbl_Customer AS CR ON R.Child_id = CR.Customer_id
GROUP BY C.Customer_id, C.Value
ORDER BY C.Customer_id
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 19, 2013 at 9:40 am
Thanks, Kingston, I'll give that a try...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply