Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Setbased solution required. Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, January 31, 2008 1:26 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, November 6, 2008 6:37 AM Points: 7, Visits: 45
 Hi.i am trying to formulate a setbased solution which would speed things up dramatically:As part of a large report, I have to to calculate various expenses that have incurred.I achieve this by executing a separate storedproc which updates the temp tables that are created by the Main storedproc.There are 50 different Expense types with different selection criteria, so Ive created a table to keep all these expenses and there selection criteria.Ie:Insert Into fst_Expenses_Template(Descr, Where_SD_GB, Where_TRA)Select ‘Admin Fees’,’ E3_field = 132 ,’ E1_exp_level1 = 0132’ Union All Select ‘Broker fees’,’ E3_field = 135 ,’ E1_exp_level1 = 0135’ Union All Select ‘Legal Fees’,’ E3_field = 139 ,’ E1_exp_level1 = 0139’ Union All …To process each expensetype, I repetitively read (in WHILE loop) from this Expenses "template" table and build up a query-string which will Update a separate temp table with the results. (see code below which ive simplified for readability).Could this loop structure be formulated Set-based ?`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*/`Thanks!.
Post #449837
 Posted Friday, February 1, 2008 2:20 PM
 SSC Eights! Group: General Forum Members Last Login: Friday, January 6, 2012 2:39 PM Points: 954, Visits: 683
 The only suggestion I can give at this point since all of the real code is masked in the table tha holds the where clause, but here goes;You might be able to do the union all's at first.select 'TheFirstSum' as Field, sum(TheSumField) As Value...Group by ...union allselect 'TheSecondSum' as Field, sum(TheSumField) as Value...Group by ...etc...Then using pivot that out using case/group by's unless you are using 2005 then just pivot.GroupbyField1, GroupByField2,TheFirstSum,TheSecondSum,etc...That can then be joined to do your update in one pass. Without details, not sure if it will be faster. Can you supply some test cases? Spend some time to get us something that we can work with.
Post #450778
 Posted Friday, February 1, 2008 2:34 PM
 SSCrazy Group: General Forum Members Last Login: Tuesday, April 14, 2015 6:45 AM Points: 2,403, Visits: 3,431
 I think you can do this, if you redesign the Template table`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` N 56°04'39.16"E 12°55'05.25"
Post #450785
 Posted Monday, February 4, 2008 8:54 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, November 6, 2008 6:37 AM Points: 7, Visits: 45
 HI. Thanks. the above solution has worked great!Ron.
Post #451189
 Posted Monday, February 4, 2008 8:58 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, April 14, 2015 6:45 AM Points: 2,403, Visits: 3,431
 Thank you for the feedback. N 56°04'39.16"E 12°55'05.25"
Post #451192

 Permissions

 Copyright © 2002-2016 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.