SQL query for Stock Calculation - Programmers Heaven

#### Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

# SQL query for Stock Calculation

Posts: 1Member
I am using Vb Express with MsAccess as database.

My Tables are now as under:
ItemsTable
ITId ItemId Description
1 1 Coca Cola Normal
2 2 Coca Cola Zero

PurchaseTable
PId PDate ItemId Price Quantity Amount
1 28/8/2009 1 1,00 10 10,00
2 28/8/2009 2 1,00 5 5,00
3 29/8/2009 1 1,00 5 5,00
4 29/8/2009 2 1,00 10 10,00
5 30/8/2009 1 1,00 10 10,00
6 30/8/2009 2 1,00 5 5,00

SalesTable
SId SDate ItemId Price Quantity Amount
1 30/8/2009 1 2,70 2 5,40
2 30/8/2009 2 2,70 3 7,10
3 31/8/2009 1 2,70 1 2,70
4 31/8/2009 2 2,70 2 2,70

I am looking for SQL query to work out the balance of Stock at any date or interval of dates. I tried something like this but it did not give the desired results:

Dim cmdText As String = "SELECT pt.ItemId, pt.Description, SUM(pt.Quantity)AS QuantityPurchased, SUM(st.Quantity) AS QuantitySold, (SUM(pt.Quantity) - SUM(st.Quantity)) AS Balance FROM PurchaseTable pt INNER JOIN SalesTable st ON pt.ItemId=st.ItemId WHERE pt.PDate Between @START and @END GROUP BY pt.ItemId, pt.Description"

If con.State = ConnectionState.Closed Then con.Open()
Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(cmdText, con)
cmd.CommandType = CommandType.Text

If con.State = ConnectionState.Closed Then con.Open()
If Not dr.HasRows Then
MessageBox.Show("No Records Found for Date: " & TextBox1.Text)
Else
MessageBox.Show("Record found for Date: " & TextBox1.Text)
ListView1.Items.Clear()
ListView1.ForeColor = Color.DarkRed
ListView1.GridLines = True