Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Setbased solution required.


Setbased solution required.

Author
Message
ronald gorin
ronald gorin
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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 = @@ROWCOUNT
SET @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 + 1
END
/*...calcs on #EXPENSE_Tbl*/


Thanks!.
Bob Fazio
Bob Fazio
SSC Eights!
SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)

Group: General Forum Members
Points: 968 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 all
select '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.
SwePeso
SwePeso
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2469 Visits: 3431
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 Template
SELECT 'Admin Fees', 132, '0132' UNION All
SELECT 'Broker fees', 135, '0135' UNION ALL
SELECT 'Legal Fees', 139, '0139'

UPDATE e
SET 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 e
LEFT 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.Descr
LEFT 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"
ronald gorin
ronald gorin
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 45
HI.
Thanks. the above solution has worked great!
Ron.
SwePeso
SwePeso
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2469 Visits: 3431
Thank you for the feedback.


N 56°04'39.16"
E 12°55'05.25"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search