• 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.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉