Opening and Closing Balance

doolinn
doolinn used Ask the Experts™
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
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
How would you calculate opening balance and closing balance?

Author

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).
I got it using two queries. Still working on one.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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;

Ray,
genius again

Commented:
Thanks, glad to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial