SQL query for Stock Calculation

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

cmd.Parameters.AddWithValue("@START", OleDb.OleDbType.Date).Value = TextBox1.Text
cmd.Parameters.AddWithValue("@END", OleDb.OleDbType.Date).Value = TextBox2.Text

Dim dr As OleDb.OleDbDataReader

If con.State = ConnectionState.Closed Then con.Open()
dr = cmd.ExecuteReader
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

While dr.Read
Dim ls As New ListViewItem(dr.Item("ItemId").ToString())
ls.SubItems.Add(dr.Item("Description").ToString())
ls.SubItems.Add(dr.Item("QuantityPurchased").ToString())
ls.SubItems.Add(dr.Item("QuantitySold").ToString())
ls.SubItems.Add(dr.Item("Balance").ToString())
ListView1.Items.Add(ls)
End While
End If

Please advise what i am doing wrong with this query. thanks

Sign In or Register to comment.

Howdy, Stranger!

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

Categories