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!

Categories

SQL query for Stock Calculation

sazd1sazd1 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

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.