ChrisM@Work (4/9/2013)
sudhirnune (4/9/2013)
Yes they has SAL.Thanks.
DROP TABLE #Sampledata
CREATE TABLE #Sampledata (NAME VARCHAR(25), PAYMENT_TYPE VARCHAR(10), PAYMENT DECIMAL(5,2))
INSERT INTO #Sampledata (NAME, PAYMENT_TYPE, PAYMENT)
SELECT 'SUDHIR', 'SAL', 30.3 UNION ALL
SELECT 'SUDHIR', 'ADV', 10.3 UNION ALL
SELECT 'SUDHIR', 'ALL_1', 10 UNION ALL
SELECT 'SUDHIR', 'ALL_2', 10 UNION ALL
SELECT 'SUDHIR', 'ALL_3', 10 UNION ALL
SELECT 'MADHAV', 'SAL', 34 UNION ALL
SELECT 'MADHAV', 'ALL_1', 24 UNION ALL
SELECT 'MADHAV', 'BONUS', 10
SELECT
s.NAME,
[SAL] = s.Payment-x.ADV,
[BONUS1] = (s.Payment-x.ADV) * 1.1, -- BONUS from formula
[BONUS2] = x.BONUS,-- BONUS from Target Table
[COMM] = x.[COMM+] - x.[COMM-]
FROM #Sampledata s
OUTER APPLY (
SELECT
[ADV] = SUM(CASE WHEN PAYMENT_TYPE = 'ADV' THEN PAYMENT ELSE 0 END),
[COMM+] = SUM(CASE WHEN PAYMENT_TYPE IN ('ALL_1','ALL_2') THEN PAYMENT ELSE 0 END),
[COMM-] = SUM(CASE WHEN PAYMENT_TYPE = 'ALL_3' THEN PAYMENT ELSE 0 END),
[BONUS] = SUM(CASE WHEN PAYMENT_TYPE = 'BONUS' THEN PAYMENT ELSE 0 END)
FROM #Sampledata si
WHERE si.NAME = s.NAME
AND si.PAYMENT_TYPE <> 'SAL'
) x
WHERE s.PAYMENT_TYPE = 'SAL'
ORDER BY s.NAME DESC
Nicely Done Chris.