can't get this query done - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

can't get this query done

Author

Topic

jacobgold
Starting Member

5 Posts

Posted - 2012-05-15 : 23:24:15

hi

i have a data base of 2 tables

1-members(memberid, membername, groupid)

2-bills(billid ,memberid,billamount)

bill is joined to members on memberid

and members is joined to it self that some members are in a group of an other member so the child members groupid is joined to the parents member memberid

i want to write a query to get the total bill of each member

so i wrote like this

select memberid sum(billamount) from members join bill group by memberid

but that's not good enough because i want to get the child members total included in the parents total and i can't figure how to do that

can somebody help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-16 : 00:24:43

you need to use Common Table Expression for that


;With Hierarchy_CTE
AS
(
SELECT memberid,membername,groupid,CAST(memberid AS varchar(max)) AS Path
FROM members
WHERE groupid IS NULL
UNION ALL
SELECT m.memberid,m.membername,m.groupid,CAST(h.path + '/' + CAST(memberid AS varchar(10)) AS varchar(max))
FROM members m
INNER JOIN Hierarchy_CTE h
ON h.memberid = m.groupid
)

SELECT LEFT(path,CHARINDEX('/',path + '/')-1) AS MemID,SUM(billamount) AS Amt
FROM Hierarchy_CTE
GROUP BY LEFT(path,CHARINDEX('/',path + '/')-1) 
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jacobgold
Starting Member

5 Posts

Posted - 2012-05-16 : 12:29:23

it worked
is there a way i can get this without cte ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-16 : 19:43:51

you need to write a logic based on loop to do recursive iteration

Whats the issue with cte?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources