DECLARE @REP_count TinyInt , @intRow TinyInt, @commonWhere_SD_GB Varchar (60), @commonWhere_TRA Varchar (60), @descr Varchar(50), @SQL Varchar(5000)INSERT INTO #EXPENSE_Tbl SELECT * FROM fst_Expenses_Template SET @REP_count = @@ROWCOUNTSET @intRow = 1/*-----------------------------------Loop for each of the 45 expense types-------------------------------------*/WHILE @intRow <= @REP_count BEGIN SELECT @descr = Descr, @commonWhere_SD_GB = Where_SD_GB, @commonWhere_TRA = Where_TRA FROM fst_Expenses_Template --#EXPENSE_Tbl WHERE Num = @intRow SET @SQL=' UPDATE #EXPENSE_Tbl SET SD_GB_End = ( SELECT SUM(gb_field_end) FROM fst_tbl_SD WHERE ' + @commonWhere_SD_GB + ' ), SD_GB_Start = ( SELECT SUM(gb_field) FROM fst_tbl_SD WHERE ' + @commonWhere_SD_GB + ' ), TRA_G9 = ( SELECT SUM(g9_income_base) FROM fst_tbl_Transactions WHERE ' + @commonWhere_TRA + ' ), TRA_H5 = ( SELECT SUM(h5_Income_FX_gl) FROM fst_tbl_Transactions WHERE ' + @commonWhere_TRA + ' ), TRA_H77 = ( SELECT SUM(h77_gls) FROM fst_tbl_Transactions WHERE ' + @commonWhere_TRA + ' ) WHERE #EXPENSE_Tbl.Descr = ''' + @descr + ''' ' Exec (@SQL) SET @intRow= @intRow + 1END/*...calcs on #EXPENSE_Tbl*/

CREATE TABLE Template ( Descr VARCHAR(20), E3_Field INT, E1_Exp_Level1 VARCHAR(4) )INSERT TemplateSELECT 'Admin Fees', 132, '0132' UNION All SELECT 'Broker fees', 135, '0135' UNION ALL SELECT 'Legal Fees', 139, '0139'UPDATE eSET e.SD_GB_End = COALESCE(E3.SD_GB_End, 0), e.SD_GB_Start = COALESCE(E3.SD_GB_Start, 0), e.TRA_G9 = COALESCE(E1.TRA_G9, 0), e.SD_GB_End = COALESCE(E1.TRA_H5, 0), e.SD_GB_End = COALESCE(E1.TRA_H77, 0)FROM Expense AS eLEFT JOIN ( SELECT t.Descr, SUM(x.GB_Field_End) AS SD_GB_End, SUM(x.GB_Field) AS SD_GB_Start FROM fst_tbl_SD AS x INNER JOIN Template AS t ON t.E3_Field = x.E3_Field GROUP BY t.Descr ) AS E3 ON E3.Descr = e.DescrLEFT JOIN ( SELECT t.Descr, SUM(x.G9_Income_Base) AS TRA_G9, SUM(x.H5_Income_FX_GL) AS TRA_H5, SUM(x.H77_Gls) AS TRA_H77 FROM fst_tbl_SD AS x INNER JOIN Template AS t ON t.E1_Exp_Level1 = x.E1_Exp_Level1 GROUP BY t.Descr ) AS E1 ON E1.Descr = e.Descr