Howdy, Stranger!

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

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.