Posted by Mohd Al Junaibi on 11/29/06 09:39

Thanks Ed,


> We need to see these calculations.

They are 9 calculations. Too large to post. So I'll post one of them
(in this case @StrReceipts):

select @StrReceipts = sum(LOADEDCOST) From Matrectrans A , item B
Where ( issuetype = 'RECEIPT' )
and ( Tostoreloc = @StoreName1 )
and ( issue = 'N' )
and (transdate > @startDate)
and (transdate <= @endDate)
and ( A.itemnum = B.itemnum )
and ( B.in9 <> 'I' or B.in9 is null )
and ( A.gldebitacct not like '%249001' or A.gldebitacct is null )
and ( A.gldebitacct not like '%249002' or A.gldebitacct is null )
and ( A.glcreditacct not like '%249001' or A.glcreditacct is null )
and ( A.glcreditacct not like '%249002' or A.glcreditacct is null )


> Possible approach:
>
> // Populate the STORELOC column
> insert into invsum (STORELOC)
> select inventory.location
> from item
> join inventory on item.itemnum = inventory.itemnum
> join companies on inventory.location = cmp.company
> where not (item.in9 = 'I')
> // alternative: where coalesce(item.im9,'') <> 'I'
> and inventory.location <> 'WHHQ'
> and cmp.registration2 not in
> ('MAIN','DISPOSAL','SURPLUS','PROJECT','COMPLETED')
> group by inventory.location
>
> // Populate the first couple calculations
> update invsum
> set receipts = it_receipts, returns = it_returns
> from invsum join (
> select storeloc,
> sum(case when amount > 0 then amount end) receipts,
> sum(case when amount < 0 then amount end) returns
> from invtran
> group by storeloc
> ) on invsum.storeloc = invtran.storeloc
>

Thanks for the code, I will try it out and update the query
accordingly. Your efforts are very much appreciated.

• Next in forum: Design Solution Required
• Prev in forum: Re: Fiscal year search
• Thread view: Re: Inserting Multiple Rows into one table (with calculated fields)

[Reply to this message]