Need Help for SQL query

SQL , vb express , MsAccess Abu Dhabi, United Arab Emirates
  • 8 years ago

    I am working on Stock calculations in Vb Express with MsAccess as database. I have following tables.

    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 tried something like as under but it did not give me 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 and how i can get correct balance of Stock at any date or interval of dates. Thanks.

  • 8 years ago

    I am working on Stock calculations in Vb Express with MsAccess as database. I have following tables.

    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 tried something like as under but it did not give me 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 and how i can get correct balance of Stock at any date or interval of dates. Thanks.

  • 8 years ago

    i have read your question, but the solution seems to be more complex than you think in advance. first of all, you will need to make clear what is understood as a balance "between two dates". i mean logically. to my knowledge a balance always expresses an amount at a certain point of time like an account balance or an inventory balance. it might be that you are trying to express two different things as the same thing (balance).

  • 8 years ago

    ... a balance can not be a delta amount between two dates but is always a current state amount.

  • 8 years ago

    The problem i am having is as under: Data in PurchaseTable

    PId PDate ItemId Description Price Quantity Amount 1 28/8/2009 1 Coca Cola Normal 1,00 10 10,00 2 28/8/2009 2 Coca Cola Zero 1,00 5 5,00 3 29/8/2009 1 Coca Cola Normal 1,00 5 5,00 4 29/8/2009 2 Coca Cola Zero 1,00 10 10,00

    Data in Sales Table is as under:

    SId SDate ItemId Description Price Quantity Amount 1 30/8/2009 1 Coca Cola Normal 2,70 2 5,40 2 30/8/2009 2 Coca Cola Zero 2,70 3 7,10 3 31/8/2009 1 Coca Cola Normal 2,70 1 2,70 4 31/8/2009 2 Coca Cola Zero 2,70 2 2,70

    The result of query with date range of 28-08-2009 to 29-08-2009 and also with the date range of 28-08-2009 to 31-08-2009 is displayed as under:

    Coca Cola Normal 30 6 24 Coca Cola Zero 30 10 20

    whereas actually for date range of 28-08-2009 to 29-08-2009 the result should be as under:

    Coca Cola Normal 15 0 15 Coca Cola Zero 15 0 15

    And with the date range of 28-08-2009 to 31-08-2009 the result should be as under:

    Coca Cola Normal 15 3 12 Coca Cola Zero 15 5 10

    Pleae advise what i am doing wrong with the query.

  • 8 years ago

    hi sazd1,

    you will see the mistake instantly, if you ungroup your query. every entry in the purchase table with the same itemid (2 entries) is combined with every entry in the sales table (2 entries). Therefore, you have got an amount twice as high as you expected.

    you need to make three queries out of this query in order to calculate the figure as you want it. you need to group the purchase and the sales table and then join both tables.

    qryPurchaseGrouped: SELECT Purchase.ItemID, Sum(Purchase.Price) AS Price, Sum(Purchase.Quantity) AS Quantity, Sum(Purchase.Amount) AS Amount FROM Purchase WHERE (((Purchase.PDate) Between #8/28/2009# And #8/31/2009#)) GROUP BY Purchase.ItemID;

    qryPurchaseGrouped: SELECT Sales.ItemID, Sum(Sales.Price) AS Price, Sum(Sales.Quantity) AS Quantity, Sum(Sales.Amount) AS Amount FROM Sales WHERE (((Sales.SDate) Between #8/28/2009# And #8/31/2009#)) GROUP BY Sales.ItemID;

    qryBalance: SELECT qryPurchaseGrouped.ItemID AS PItemID, qryPurchaseGrouped.Price AS PPrice, **qryPurchaseGrouped.Quantity AS PQuantity, qryPurchaseGrouped.Amount AS PAmount, qrySalesGrouped.ItemID AS SItemID, qrySalesGrouped.Price AS SPrice, qrySalesGrouped.Quantity AS SQuantity, qrySalesGrouped.Amount AS SAmount, [PQuantity]-[SQuantity] AS QuantityDelta FROM qryPurchaseGrouped LEFT JOIN qrySalesGrouped ON qryPurchaseGrouped.ItemID = qrySales_Grouped.ItemID;

    The left join in this last query is quite important since you would not get any results if you did not sell an item.

    Hope this helps. But please note, that this is still not a balance since you would ignore every item in stock before the start date.

    Osman

  • 8 years ago

    ... sorry. the second query should be called qrySalesGrouped

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“PHP is a minor evil perpetrated and created by incompetent amateurs, whereas Perl is a great and insidious evil perpetrated by skilled but perverted professionals.” - Jon Ribbens