summarising where date is less than other records 1

[collierd2]

Hello

Using SQL 2000

If I have the following sample data:

Date Quantity In Quantity Out
200801 100 0
200802 100 100
200803 500 200
200804 300 200
200805 400 400

At day 1 the stock is 0
Then we get stock in and move stock out

How do I create a view that shows Date, Starting Balance,
Quantity In, Quantity Out

e.g.

Date Starting Balance Quantity In Quantity Out
200801 0 100 0
200802 100 100 100
200803 100 300 200
200804 200 300 200
200805 300 400 400
etc...

Thanks

Damian.

 

[bborissov]

NOT TESTED!

SELECT YourTable.Date,
       ISNULL(SUM(Tbl1.Balance),0) AS [Starting Balance],
       QuantityIn,
       QuantityOut
FROM YourTable
LEFT JOIN (SELECT Date, (QuantityIn-QuantityOut) AS Balance
                  FROM YourTable) Tbl1
     ON YourTable.Date > Tbl1.Date
GROUP BY YourTable.Date, QuantityIn, QuantityOut

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[collierd2]

Little bit of tweaking (as this was only sample code) and it worked

Thanks