Vaynard Vaynard - 2 months ago 7
SQL Question

How to summerize Parent data on the same tables?

I have Temp table(in stored procedure) lets say #tempTable which holds an id, id_name and amount , the Detail id(4 chars id) is the only row which has non NULL amount value.

id id_name Amount
1 Main1 NULL
11 Header1 NULL
1101 Detail1 2
1102 Detail2 7
12 Header2 NULL
1201 Detail3 5
1202 Detail4 3


What i want is a query to summarize amount with id nested in it's own id like :


amount 11 = amount 1101 + amount 1102 (because 11 is in 2 first detail's left chars)

amount 12 = amount 1201 + amount 1202 (because 12 is in 2 first detail's left chars)

and

amount 1 = amount 11 + amount 12 (because 1 is in first detail's left chars)


id id_name Amount
1 Main1 17
11 Header1 9
1101 Detail1 2
1102 Detail2 7
12 Header2 8
1201 Detail3 5
1202 Detail4 3


What i've done

SELECT t.id
, t.id_name
, CASE LEN(t.id)
WHEN 2
THEN (SELECT SUM(t2.Amount) FROM #tempTable t2 WHERE LEFT(t2.id,2) = t.id) and LEN(t2.id) > 2)
WHEN 1
THEN (SELECT SUM(t2.Amount) FROM #tempTable t2 WHERE LEFT(t2.id,1) = t.id) and LEN(t2.id) = 2)
ELSE t1.amount
END
FROM #tempTable t


But it leaves me with

id id_name Amount
1101 Detail1 2
1102 Detail2 7
1201 Detail3 5
1202 Detail4 3


how supposed i solve this?

ps : sorry for my bad english, it's not my native language, nor second or even third :D..

Update
To answer Lajos Arpad Comment

this

Answer

You can use the following script to get the desired result. The sub select within the main select is ignoring the NULL values in amounts.

SELECT t.id
, t.id_name
, CASE LEN(t.id)
    WHEN 2 
         THEN ISNULL((SELECT SUM(t2.Amount) FROM #tempTable t2 WHERE t2.id = t.id) and LEN(t2.id) = 2),0)
    WHEN 1 
         THEN ISNULL((SELECT SUM(t3.Amount) FROM #tempTable t3 WHERE t3.id= t.id) and LEN(t3.id) = 1),0)
    ELSE ISNULL(t1.amount,0)
END
FROM #tempTable t
Comments