How to sort calcuation by date and transaction id?

[maheshwag]

I have Confusion against utilize If,Else Statement against calculation of stock By date. And sort the same by date.

There is real challenge to calculate running total between equal date:


My Table Schema Is:

 
TransID    int,        Auto Increment
Date       datetime,
Inwards    decimal(12,2)
Outward    decimal(12,2)
 

Suppose If I have Records as Below:

TransID Date(DD/MM/YYYY)  Inward        Outward     
 
1       03/02/2011                        100                                       
2       12/04/2010                        200               
3       03/02/2011          400                          
 

Than Result Should be:

TransID Date(DD/MM/YYYY)    Inward  Outward     Balance
 
    2         12/04/2010             200         -200          
    1         03/02/2011             100         -300                                  
    3         03/02/2011     400                  100           
 

I wants to calculate Inward - outwards = Balance and Balance count as running total as above. but the condition that it should be as per date order by Ascending

How to sort and calculate it by date and transID?

What is transact SQL IN SQL_SERVER-2000?.

[gpl]

SQL is not really the place to do this, it should be a function of the front end application, or report.

You could do it by a cursor, or you could use a correlated sub query to calculate the current balance by summing the columns for all rows where the date is less than or equal to the current date. Note that this can be very slow when you have more than a few rows to look at

Something like this (Ive called the table stocktable as you didnt supply a name)

  Select TransID,
         Date, 
         Inwards, 
         Outward, 
         (
                  Select sum(inwards) - sum(outward) 
                  From stocktable 
                  where date <= s.date
         ) as Balance
 
  From   stocktable s
  Order by  Date,
            TransID

[maheshwag]

hi,
gpl

First of all you have to use COALESCE and ISNULL function in your statement which will count any nullable.

and finally check the result of your statement which is not correct or not calculated by Ascending date.

[Jerry Winston]

@maheshwag

It looks like you posted the functions to bridge gpl's code to your solution(COALESCE and ISNULL).
Is this question answered?
What was the script for your final solution?

[maheshwag]

hi
Jerry Winston,
it's not solution which is i suggested to gpl because his solution return wrong result. I just advise him to use ISNULL Or COALESCE function which is useful in this case otherwise if some columns has null value than nothing to return and ultimately your counting will get wrong.

[gpl]

I fail to see how isnull/coalesce would be useful as in this case, a null does not need to be counted .. if we were using count or avg, then yes it would be necessary.

How did the results get ordered ? You requested it be sorted ascending, but the example showed it descending.

Please post your final query so I can see where I went wrong :)

[Rabbit]

gpl, the where clause in your subquery should be

date < s.date OR
(date = s.date AND TransID <= s.TransID)

[gpl]

Rabbit
I take your point, my solution only gives a total for the day (repeated for each transID)
Nice correction

[maheshwag]

gpl

Yes the use of COALESCE OR ISNULL is usefull in your query i explain how.

if i am going to use your above query without ISNULL OR COALESCE Function than the result of out put is as below:

TransID Date                    InWard  Outwards Balance
3    2010-02-04 00:00:00.000     NULL     567.88      NULL
2    2011-02-06 00:00:00.000     225.66     NULL     -342.22
4    2011-02-06 00:00:00.000     NULL     115.67     -457.89
 
 

Now I use COALESCE OR ISNULL Funtion in my query than result should work perfectly see How?.

Select transID, 
         Date,  
         input,  
         output,  
         ( 
                  Select sum(isnull(input,0)) - sum(isnull(output,0))  
                  From stock  
                  where date < s.date OR
          (date=s.date AND transID <= s.transID)     
         ) as Balance 
 
  From   stock s 
  Order by  date, 
            transID 
 
 

Now the result is:

transID Date                    Inwards Outwards Balance 
3    2010-02-04 00:00:00.000    NULL    567.88     -567.88
2    2011-02-06 00:00:00.000    225.66    NULL     -342.22
4    2011-02-06 00:00:00.000    NULL    115.67     -457.89
 
 

So if you not going to use COALESCE OR ISNULL Function in your query than Your first Nullable Columns should not return the Result/Counting of Balance Column.

[maheshwag]

hi
rabbit

Nice judge the query and Solution I am appreciate that here i solve it by another way which i would like to share with our community which is as below:

 
select *, 
cast(null as decimal(12,2)) 
as balance into #trans 
from stock  
 
-- create an index to aid performance 
 
create clustered index #cix_trans on #trans(date, transid)  
 
--set up a loop to go through all record in the temp table 
--in preference to using CURSORs 
 
declare @date datetime, 
@id int, 
@balance decimal(12,2)  
 
select top 1 @date = date, 
 
@id = transid, 
@balance = 0 
from #trans 
order by date,transid  
 
while @@ROWCOUNT > 0 
begin   
update #trans set @balance = balance = @balance + coalesce(input, -output)   
where transid = @id  
 
-- next record 
 
select top 1 
@date = date, 
@id = transid 
from #trans 
where (date = @date and transid > @id) 
or (date > @date) 
order by date, transid 
end  
 
-- show the output 
select  
transID, 
date= convert(varchar,convert(datetime,date,103),103),  
input,  
output,  
balance from #trans 
order by convert(datetime,date,103), transID  
 
-- clean  up 
drop table #trans; 
 

[gpl]

maheshwag
Good point, the first row would not have a current value to be subtracted from / added to without the isnull/coalesce