T1: relationID, meterID, parentID, childIDT2: dataID, meterID, date, amount
T1 T2----------------- -------------------------1 | 1 | null | 2 * 1 | 1 | 01,01,2013 | 100 *2 | 1 | null | 3 * 2 | 2 | 01,01,2013 | 60 *3 | 2 | 1 | 4 3 | 3 | 01,01,2013 | 40 *4 | 2 | 1 | 5 4 | 4 | 01,01,2013 | 355 | 3 | 1 | 6 5 | 5 | 01,01,2013 | 256 | 3 | 1 | 7 6 | 6 | 01,01,2013 | 157 | 4 | 2 | null 7 | 7 | 01,01,2013 | 258 | 5 | 2 | null9 | 6 | 3 | null 10 | 7 | 3 | null
DECLARE @T1 TABLE (relationID INT, meterID INT, parentID INT, childID INT)DECLARE @T2 TABLE (dataID INT, meterID INT, [date] DATE, amount INT)INSERT INTO @T1SELECT 1 , 1 , null , 2 UNION ALL SELECT 2 , 1 , null , 3 UNION ALL SELECT 3 , 2 , 1 , 4 UNION ALL SELECT 4 , 2 , 1 , 5 UNION ALL SELECT 5 , 3 , 1 , 6 UNION ALL SELECT 6 , 3 , 1 , 7 UNION ALL SELECT 7 , 4 , 2 , nullUNION ALL SELECT 8 , 5 , 2 , nullUNION ALL SELECT 9 , 6 , 3 , nullUNION ALL SELECT 10 , 7 , 3 , nullINSERT INTO @T2SELECT 1 , 1 , '2013-01-01' , 100UNION ALL SELECT 2 , 2 , '2013-01-01' , 60UNION ALL SELECT 3 , 3 , '2013-01-01' , 40UNION ALL SELECT 4 , 4 , '2013-01-01' , 35UNION ALL SELECT 5 , 5 , '2013-01-01' , 25UNION ALL SELECT 6 , 6 , '2013-01-01' , 15UNION ALL SELECT 7 , 7 , '2013-01-01' , 25
SELECT a.meterID, b.[date] ,MeterAmt=MAX(b.amount) ,SumOfChildren=SUM(c.amount) FROM @T1 aINNER JOIN @T2 b ON a.meterID = b.meterIDLEFT JOIN @T2 c ON a.childID = c.meterID GROUP BY a.meterID, b.[date]