CODEWITHSUNDEEP
sqlsybase
jeff abc
jeff abc

Reputation: 3

Running Total Sybase

I am writing a sql script that has following requirements:- 1) It should be able to get every record from Asset_Transaction table and for every asset calculate "Running Total" On Each Record for number of units. This running total column is calculated, by summing up the Units for all Asset_Transactions where the Transaction_datetime is less than or equal to the Transaction_datetime of the Asset_id being displayed. I have achieved that, and i think it is working ok.

However my second Requirement is:-

2) Calculate the Value of Units on that date, which is get the sell_unitPrice from Unit_Price table for the date on each asset_transaction and for that particular asset and multiply it with number of units ("Running Total") which were added . I am Struggling on Step 2

SCHEMA FOR Sybase DATABASE Click ON LINK--

Code i have written so far.

set nocount on
   go

     declare mla_exceptions scroll cursor for
                 select distinct mla.asset_id  from    asset_transaction mla
go

Print 'asset_id, Amount, Transaction Name, Total Units, Transaction Datetime'

declare @ml_asset double precision

open mla_exceptions

fetch first mla_exceptions

              into @ml_asset  

              while (@@sqlstatus = 0)

              begin  
              select    mla.asset_id , ',',
               -- mla.transaction_datetime, ',',
              mla.amount, ',',
              tt.name, ',',
             (select sum (units) from asset_transaction where transaction_datetime <= mla.transaction_datetime  and asset_id = @ml_asset  and status = 'A' ) 'Running Total Units', ',',
              transaction_datetime 
          from    asset_transaction mla noholdlock 
          Left outer join transaction_type tt on tt.transaction_type_id = mla.transaction_type_id  where   mla.asset_id = @ml_asset 
order by mla.asset_id

fetch next mla_exceptions
 into @ml_asset 
end

close mla_exceptions

deallocate cursor mla_exceptions

go

Upvotes: 0

Views: 926

Answers (1)

kelley805
kelley805

Reputation: 11

Congratulations. I admire your tenacity to solve requirement 1. Unfortunately the performance will be poor because you are reading the same table twice within itself and trying to control the locks with the noholdlock.

What if the same asset id exists multiple times? You will have calculated the sum of the same asset multiple times.

Here is a better SQL solution. Create a temp table with the name #asset_sum and columns asset_id and asset_sum. Then join this temp table with the original table.

The result is no cursor, fewer locks and no redundancy.

I will check back later to give you a detailed answer.

Upvotes: 1

Related Questions