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

Ed Murphy wrote:
> 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')

Oops, append the following:

group by it.storeloc

[Back to original message]