Posted by Ed Murphy on 11/29/06 07:35

Mohd Al Junaibi wrote:

> my first post here...hope it goes well. I'm currently working on
> stored procedure where I translated some reporting language into T-SQL
>
> The logic:
>
> I have a group of tables containing important values for calculation.
> I run various sum calculations on various fields in order to retrieve
> cost calculations ...etc.
>
> 1) There is a select statement which gathers all the "records" which
> need calculations.
> ex: select distinct Office from Offices where OfficeDesignation =
> 'WE' or OfficeDesignation = 'BE...etc.
> As a result I get a list of lets say 5 offices which need to be
> calculated!
>
> 2) A calculation select statement is then run on a loop for each of
> the returned 5 offices (@OfficeName cursor used here!) found above.An
> example can be like this
>
> (* note that @WriteOff is a variable storing the result):
>
> "select @WriteOff = sum(linecost * (-1))
> From Invtrans , Inventory
> Where ( transtype in ('blah', 'blah' , 'blah' ) )
> and ( storeloc = @OfficeName )
> and ( Invtrans.linecost <= 0 )
> and ( Inventory.location = Invtrans.storeloc )
> and ( Inventory.itemnum = Invtrans.itemnum )"...etc
>
> This sample statement returns a value and is passed to the variable
> @WriteOff (for each of the 5 offices mentioned in step 1). This is done
> around 9 times for each loop! (9 calculations)
>
> 3) At the end of each loop (or each office), we do an insert statement
> to a table in the database.
>
>>>> END of Logic<<
>
> Problem:
>
> This kind of dataset or report usually takes alot of time, and I need
> to have the ability to storing all the calculated variables for each
> "Office" in an "array" so that I can do ONE INSERT STATEMENT LOOP as
> opposed to doing one insert statement at a time, in a loop.
>
> Basically, a loop to calculate and save into an array, and then one
> loop for insert statements.

I believe your ideal solution will look something like this:

insert into some_other_table (storeloc, WriteOff)
select it.storeloc, -sum(linecost)
from Invtrans it
join Inventory i on it.itemnum = i.itemnum
and it.storeloc = i.location
join Offices o on it.storeloc = o.storeloc
where it.transtype in ('blah','blah','blah')
and it.linecost <= 0
and i.ItemStatus = 'Active'
and o.OfficeDesignation in ('WE','BE')

[Back to original message]