I think you need to stop converting the source data to an excel-like pivot table and do your calculations inline.

It will make things much simpler.

Unfortunately, you did not provide a test sample, so fo my demonstration I used the one made up by Chris.

I changed a small part there - added a PayDate column: I belive you have something like this in your table.

CREATE TABLE #Sampledata (

PayDate smalldatetime,

NAME VARCHAR(25),

PAYMENT_TYPE VARCHAR(10),

PAYMENT DECIMAL(5,2)

)

You may embed all your formula variations into a single view:

SELECT

DATEADD(mm, DATEDIFF(mm, 0, PayDate) , 0) PayMonth, s.NAME,

SUM(CASE WHEN PAYMENT_TYPE = 'SAL' THEN PAYMENT ELSE 0 END) - SUM(CASE WHEN PAYMENT_TYPE = 'ADV' THEN PAYMENT ELSE 0 END) [NORMAL-SAL],

SUM(CASE WHEN PAYMENT_TYPE = 'ALL_1' THEN PAYMENT ELSE 0 END) + SUM(CASE WHEN PAYMENT_TYPE = 'ALL_2' THEN PAYMENT ELSE 0 END) - SUM(CASE WHEN PAYMENT_TYPE = 'ALL_3' THEN PAYMENT ELSE 0 END) [NORMAL-COMM],

SUM(CASE WHEN PAYMENT_TYPE = 'SAL' THEN PAYMENT ELSE 0 END) *1.1 [NORMAL-BONUS],

SUM(CASE WHEN PAYMENT_TYPE = 'SAL' THEN PAYMENT ELSE 0 END) [MONTH-SAL],

SUM(CASE WHEN PAYMENT_TYPE = 'ALL_1' THEN PAYMENT ELSE 0 END) + SUM(CASE WHEN PAYMENT_TYPE = 'ALL_2' THEN PAYMENT ELSE 0 END) [MONTH-COMM],

SUM(CASE WHEN PAYMENT_TYPE = 'SAL' THEN PAYMENT ELSE 0 END) + SUM(CASE WHEN PAYMENT_TYPE = 'ALL_1' THEN PAYMENT ELSE 0 END) + SUM(CASE WHEN PAYMENT_TYPE = 'ALL_2' THEN PAYMENT ELSE 0 END) [YEARLY-BONUS]

FROM #Sampledata s

GROUP BY DATEADD(mm, DATEDIFF(mm, 0, PayDate) , 0), s.NAME

Than you may just select appropriate columns from the view for appropriate cases.

You may chose using dynamic SQL for selecting only required columns from this view, but I'd suggest to move this part to UI/Report module.

That's what they are for.