Opening and Closing Balance
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
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
How would you calculate opening balance and closing balance?
ASKER
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ray,
genius again
genius again
Thanks, glad to help.