Impossible Task On Sql Calculation Where Date Is Equal

**

LOOK AT MY ANSWER WHICH IS REFERE AS NEW QUESTION ON SAME PROBLEM.

**

I have Confusion against utilize If,Else Statement against calculation of stock By date. And sort the sa开发者_开发技巧me by date.

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

1. If date is equal
2. If date is greater than
3. If date is less than

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**?.

You can use a brute O(n²) approach of self-joining the table to itself, or if the table is large, it is better to iteratively calculate the balance. The choices for SQL Server 2000 are limited, but the approach I will show uses a loop that traverses the table in date, transid order rather than using cursors.

Here is a sample table for discussion

create table trans (transid int identity, date datetime, inwards decimal(12,2), outward decimal(12,2))
insert trans select '20110203', null, 100
insert trans select '20100412', null, 200
insert trans select '20110203', 400, null  -- !same date as first

This is the T-SQL batch that will give you the output you require. If you merely wanted to update a balance column in the table (if it had such an extra column), change all references to #trans to the table itself.

-- fill a temp table with the new column required
select *, cast(null as decimal(12,2)) as balance
into #trans
from trans

-- 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(inwards, -outward)
    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 *
from #trans
order by date, transid
;

-- clean  up
drop table #trans;

Output

2   2010-04-12 00:00:00.000 NULL    200.00  -200.00
1   2011-02-03 00:00:00.000 NULL    100.00  -300.00
3   2011-02-03 00:00:00.000 400.00  NULL    100.00

EDIT

If you need to show the final output using the date formatted to dd/mm/yyyy, use this

-- show the output
select transid, convert(char(10), date, 103) date, inwards, outward, balance
from #trans
order by #trans.date, transid
;

Is Balance (Inward-Outward) ?

select TransID,
   Date,
  Inward,
  Outward, 
  (select sum(Inward - Outward) 
        from tbl_name b1 
        where b1.TransID <= b.TransID) as RunB 
 from   tbl_name b 
 order by Date desc,TransID desc

This would order by Date in descending order - if the dates are same they are ordered by TransID

Edit:

Assume a Transaction table as this

select * from Transactions

1   NULL    100.00  2010-01-01 00:00:00.000
2   NULL    200.00  2010-01-02 00:00:00.000
3   400.00  NULL    2010-01-03 00:00:00.000
4   50.00   NULL    2010-01-03 00:00:00.000
5   NULL    100.00  2010-01-04 00:00:00.000

If you do this query ie, sort by TransactionIDs you would get this!

select TransID,
   Date,
  isNull(Inward,0.0),
  isNull(Outward,0.0), 
  (select sum(isNull(Inward,0.0) - isNull(Outward,0.0)) 
        from Transactions b1 
        where b1.TransID <= b.TransID) as RunB 
 from  Transactions b 
 order by Date asc,TransID asc

1   2010-01-01 00:00:00.000 0.00    100.00  -100.00
2   2010-01-02 00:00:00.000 0.00    200.00  -300.00
3   2010-01-03 00:00:00.000 400.00  0.00    100.00
4   2010-01-03 00:00:00.000 50.00   0.00    150.00
5   2010-01-04 00:00:00.000 0.00    100.00  50.00

Instead if you use this query - sort by date you would get this? Is this what you meant Mahesh?

select TransID,
   Date,
  isNull(Inward,0.0),
  isNull(Outward,0.0), 
  (select sum(isNull(Inward,0.0) - isNull(Outward,0.0)) 
        from Transactions b1 
        where b1.Date <= b.Date) as RunB 
 from  Transactions b 
 order by Date asc,TransID asc

1   2010-01-01 00:00:00.000 0.00    100.00  -100.00
2   2010-01-02 00:00:00.000 0.00    200.00  -300.00
3   2010-01-03 00:00:00.000 400.00  0.00    150.00
4   2010-01-03 00:00:00.000 50.00   0.00    150.00
5   2010-01-04 00:00:00.000 0.00    100.00  50.00

The difference in queries being -> (b1.Date <= b.Date) and (b1.TransID <= b.TransID)

Take this first bit of cyberkiwi's solution:

select *, cast(null as decimal(12,2)) as balance
into #trans
from stock

and change it so the nvarchar dates from your table are converted to datetime values. That is, expand the * into the actual set of columns, replacing the date column with the converting expression.

Given the structure you've shown in your original question, it may look like this:

select
  TransID,
  convert(datetime, substring(Date,7,4) + substring(Date,4,2) + substring(Date,1,2)) as Date,
  Inward,
  Outward,
  cast(null as decimal(12,2)) as balance
into #trans
from stock

Leave the rest of the script untouched.

Now this is Solution with date in format dd/MM/yyyy of above question.

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;