March 1, 2006 at 5:13 am
I am having problem building this query.
I have 3 tables Ch_master, Ch_detail and Ch_PlaceMaster
Ch_master has columns
Ch_id Ch_name Ch_PlaceId
1 AD 100
2 UY 101
3 RE 102
Ch_detail has columns
Ch_id Ch_Amount
1 1000
2 2000
3 3000
Ch_PlaceMaster has columns
Ch_PlaceId Ch_PCode CH_ParentId
100 ABC 100
101 CTY 100
102 UYT 100
The parent place id will be passed as a parameter to the stored proc and I want records is such a way that the first column would be of the parent place and then the below records are of its child(the 1st column TotalAmount should be the total of amount of the parent and its child and the 2nd column would be the amount of the individual place, which also means that the totalamount column for all parent places and its child places would be same until the next batch of parent child combination starts) like below
TotalAmount Amount
6000 1000
6000 2000
6000 3000
Any help....Thanks a lot...
March 1, 2006 at 7:20 am
Calculate the summary data and join with the detail table:
SELECT @PArentID AS PArentID, Parent.TotalAmount,D.Amount
FROM Ch_detail D, Ch_PlaceMaster E, Ch_PlaceMaster F,
(SELECT SUM(Ch_Amount) AS TotalAmount FROM Ch_detail A, Ch_PlaceMaster B, Ch_PlaceMaster C
WHERE C.CH_ParentID=@ParentID AND C.Ch_PlaceID=B.CH_PlaceID AND B.CH_ID=A.CH_ID) Parent
WHERE F.CH_ParentID=@ParentID AND F.Ch_PlaceID=E.CH_PlaceID AND E.CH_ID=D.CH_ID
Add other columns from the tables if you want.
March 1, 2006 at 7:21 am
This is what I came up with
/* Ex. Setup code */
-- SET NOCOUNT ON
--
-- Create table Ch_master (Ch_id tinyint, Ch_name char(2), Ch_placeid tinyint)
-- Create table Ch_detail (Ch_id tinyint, Ch_amount smallint)
-- Create table Ch_PlaceMaster (ch_placeid tinyint, Ch_PCode char(3), CH_ParentId tinyint)
-- GO
--
-- INSERT Ch_master VALUES (1,'AD',100)
-- INSERT Ch_master VALUES (2,'UY',101)
-- INSERT Ch_master VALUES (3,'RE',102)
--
-- INSERT Ch_detail VALUES (1,1000)
-- INSERT Ch_detail VALUES (2,2000)
-- INSERT Ch_detail VALUES (3,3000)
--
-- INSERT Ch_PlaceMaster VALUES (100,'ABC',100)
-- INSERT Ch_PlaceMaster VALUES (101,'CTY',100)
-- INSERT Ch_PlaceMaster VALUES (102,'UTY',100)
-- GO
SELECT
SUM(CD2.Ch_amount) TotalAmount,
CD.Ch_amount Amount
FROM
dbo.Ch_master M
INNER JOIN
dbo.Ch_detail CD
ON
M.Ch_id = CD.Ch_id
INNER JOIN
dbo.Ch_PlaceMaster PM
INNER JOIN
dbo.Ch_PlaceMaster PM2
INNER JOIN
dbo.Ch_master M2
INNER JOIN
dbo.Ch_detail CD2
ON
M2.Ch_id = CD2.Ch_id
ON
PM2.ch_placeid = M2.ch_placeid
ON
PM.CH_ParentId = PM2.CH_ParentId
ON
M.ch_placeid = PM.ch_placeid
GROUP BY
M.ch_placeid,
CD.Ch_amount
ORDER BY
M.ch_placeid
Make the assumption you want to list the values in order Ch_PlaceID with regards to parent child where parent is first (here 100), first child is second (101 here) and so on.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply