# Opening and Closing Balance

on
I have a table with a number of transactions showing date/time,product_id, and quantity. How do I create a query that will show two new columns for opening balance and closing balance for each product_Id.

ie. It would create something like this..

Date/Time    Product_Id     Qty      OPENINGBALANCE      CLOSING BALANCE
01/01/09        AAA             10                  0                                  10
02/01/09        AAA              10                10                                  20
03/01/09        BBB               5                   0                                   5
04/01/09        AAA              -5                 20                                  15

Thanks
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
How would you calculate opening balance and closing balance?

Commented:
Opening Balance is total quantity before transaction ( ie sum of all quantities for the product prior to date/time of current record) and closing balance is the total quantity after the transaction ( ie opening balance + quantity of current transaction).

Commented:
I got it using two queries. Still working on one.

Commented:
The two query solution
Query1:
SELECT YourTable.Product_Id, Sum(YourTable.Qty) AS OPENINGBALANCE
FROM YourTable
GROUP BY YourTable.Product_Id;

Query2:
SELECT YourTable.*, Query2.OPENINGBALANCE, [OPENINGBALANCE]-[Qty] AS [CLOSING BALANCE]
FROM YourTable INNER JOIN Query2 ON YourTable.Product_Id = Query2.Product_Id;

Still thinking about a one query solution.
Commented:
This do it?

SELECT a.[Date/Time], a.Product_Id, a.Qty, (SELECT Sum(b.Qty) FROM myTable AS b WHERE b.Product_Id=a.Product_Id AND b.[Date/Time]<a.[Date/Time]) as OPENINGBALANCE, a.Qty + OPENINGBALANCE AS [CLOSING BALANCE] FROM myTable AS a ORDER BY a.[Date/Time],a.Product_Id;

Commented:
Ray,
genius again

Commented: