Stock Processing

[justwandering]

Cost of ending inventory = Beginning inventory + Cost of purchases - Cost of goods sold

yes that's correct..

[ChipR]

Okay, let me know if I'm off track on any of this.

Beginning inventory - you've figured that out already
Cost of purchases - Sum(Qty*Rate) where action = purchase
Cost of goods sold - Sum(Qty*Rate) where action = sale

These are constrained by date, of course. The only information I can see that you may be missing is the Rate on sales, but shouldn't that be filled in at the time of sale?

[sazd1]

Hi Justwandering
I am trying to add OpeningBalance column in my query. But I am having problem with the
WHERE a.TransactionDa te < @START
Please advise where i can put this, because Where clause is used after From clause and i have to use two WHERE clauses one for OPENINGBALANCE and the other for the stock position between two dates. Please go through my query i have highlighted the wrong WHERE clause portion, and advise how to place it in correct position. Thanks

Dim cmdText As String = "Select a.ItemId,a.Description, " & _
        "(Sum(QuantityPurchased) - Sum(QuantitySold)) As OpeningBalance [B]WHERE a.TransactionDate < @START[/B], " & _
        "Sum(QuantityPurchased) AS QuantityPurchased, " & _
        "Sum(QuantitySold) AS QuantitySold, " & _
        "(Sum(QuantityPurchased) - Sum(QuantitySold)) AS Balance " & _
        "From " & _
        "(" & _
        "SELECT pt.PDate as TransactionDate, pt.ItemId, pt.Description, pt.Quantity AS QuantityPurchased, " & _
        "0 AS QuantitySold FROM PurchaseTable1 pt " & _
        "UNION ALL " & _
        "Select st.SDate as TransactionDate, st.ItemId, st.Description, 0 AS QuantityPurchased, " & _
        "st.Quantity AS QuantitySold From SalesTable1 st " & _
        ") a " & _
        "WHERE a.TransactionDate Between @START and @END " & _
        "GROUP BY a.ItemId, a.Description"

[justwandering]

Friends, I am a bit occupied for the next 2 days, but will come back.

[FishVal]

...
I am trying to add OpeningBalance column in my query. But I am having problem with the
WHERE a.TransactionDa te < @START
Please advise where i can put this, because Where clause is used after From clause and i have to use two WHERE clauses one for OPENINGBALANCE and the other for the stock position between two dates. Please go through my query i have highlighted the wrong WHERE clause portion, and advise how to place it in correct position. Thanks
....

There is nothing to correct in your query since it is not correct in all points.
If you want to make any calculation in query, then you need to obtain such dataset that calculation arguments appear in the same record. This could be done by first aggregating purchases and sales tables by [ItemID] and then joining thus obtained datasets by equal [ItemID].
On the other hand you may union purchases and sales tables but quantity field in sales table has to be negated to make summing correct.

P.S. It will be better if you break down logic of your task into several sequential steps thus going from one stage to another when one has been completed.
Here is an example of what it could be like:

• Query returning purchases sum per item till particular date.
• Query returning sales sum per item till particular date.
• Join the above two queries to get dataset where each record contains unique itemid, sales sum for this particular item till particular date, purchases sum for this particular item till particular date.
• Add callculated field to the above query which subtract purchases sum from sales sum. Thus you get dataset which is list of itemid's with correspondent stock openings for the particular date.
• Congratulate yourself and go further.
• ??????
• PROFIT !!!

[sazd1]

Hi Fishval
Thanks for your guidance.
I would like to add here that i tried the following code for calculation of stock balance and it worked so fine giving me the correct results for a date range.

Dim cmdText As String = "Select a.ItemId,a.Description,Sum(QuantityPurchased) AS QuantityPurchased, " & _
        "Sum(QuantitySold) AS QuantitySold, " & _
        "(Sum(QuantityPurchased) - Sum(QuantitySold)) AS Balance " & _
        "From " & _
        "(" & _
        "SELECT pt.PDate as TransactionDate, pt.ItemId, pt.Description, pt.Quantity AS QuantityPurchased, " & _
        "0 AS QuantitySold FROM PurchaseTable1 pt " & _
        "UNION ALL " & _
        "Select st.SDate as TransactionDate, st.ItemId, st.Description, 0 AS QuantityPurchased, " & _
        "st.Quantity AS QuantitySold From SalesTable1 st " & _
        ") a " & _
        "WHERE a.TransactionDate Between @START and @END " & _
        "GROUP BY a.ItemId, a.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

Now I want to add one column of Opening balance so that when user gives a date range the query should calculate the OpeningBalance prior to startDate so that correct balance is displayed.
I am having problem with this column addition in the query.
Justwandering advised me to try something like

"(Sum(QuantityPurchased) - Sum(QuantitySold)) As OpeningBalance WHERE a.TransactionDate < @START, "

i am now in a fix to adjust this in the above query.
Please advise how i can add this feature in the above query.
Thanks again for your guidance.

[FishVal]

Ah, well.

Taking my words back. Just overlooked those zero constants in the bunch of concatenated strings.
Well, if you want to go this way, then you just need to move date checking (between start and end) to those unioned subqueries and add two more subqueries to the union to get records before start for stock opening calculation. This will certainly require to add two more zero constants.

Example of subquery before grouping:

SELECT Quantity AS PurchasedBefore, 0 AS SoldBefore, 0 AS PurchasedWithin, 0 AS SoldWithin FROM PurchaseTable1 WHERE PDate<@START
UNION ALL
SELECT 0 AS PurchasedBefore, Quantity AS SoldBefore, 0 AS PurchasedWithin, 0 AS SoldWithin FROM SalesTable1 WHERE PDate<@START
UNION ALL
SELECT 0 AS PurchasedBefore, 0 AS SoldBefore, Quantity AS PurchasedWithin, 0 AS SoldWithin FROM PurchaseTable1 WHERE PDate Between @START and @END
UNION ALL
SELECT 0 AS PurchasedBefore, 0 AS SoldBefore, 0 AS PurchasedWithin, Quantity AS SoldWithin FROM SalesTable1 WHERE PDate Between @START and @END

But, if you go this way,then why not to allow aggregating to do balance.

SELECT Quantity AS BalanceBefore, 0 AS BalanceWithin FROM PurchaseTable WHERE PDate<@START
UNION ALL
SELECT -Quantity AS BalanceBefore, 0 AS BalanceWithin FROM SalesTable WHERE PDate<@START
UNION ALL
SELECT 0 AS BalanceBefore, Quantity AS BalanceWithin FROM PurchaseTable WHERE PDate Between @START and @END
UNION ALL
SELECT 0 AS BalanceBefore, -Quantity AS BalanceWithin FROM SalesTablel WHERE PDate Between @START and @END

[sazd1]

Hi Fishval
Thanks for your reply. I will try to implement this and will let you know the results. Thanks again.

[sazd1]

Hi Neopa, Fishval & Justwandering.
Thanks to all for giving me suggestions and guidance to solve this issue which was on my head for last couple of months.
I specially want to thank Fishval for guiding me with a query due to which i finally was able to get those so desired results of Stock calculations.
The following query produced the long waited results for me.Any further improvement or alternate of this query will be highly appreciated.
Thanks once again.

Dim cmdText As String = "Select a.ItemId,a.Description, SUM(PQuantityBefore) AS PQuantityBefore, " & _
        "SUM(SQuantityBefore) AS SQuantityBefore, " & _
        "(Sum(PQuantityBefore) - Sum(SQuantityBefore)) AS BalanceBefore, " & _
        "Sum(QuantityPurchased) AS QuantityPurchased, Sum(QuantitySold) AS QuantitySold, " & _
        "(Sum(PQuantityBefore) - Sum(SQuantityBefore) + Sum(QuantityPurchased) - Sum(QuantitySold)) AS Balance " & _
        "From " & _
        "(" & _
        "SELECT pt.ItemId, pt.Description, pt.Quantity AS PQuantityBefore, " & _
        "0 AS SQuantityBefore, 0 AS QuantityPurchased,0 AS QuantitySold FROM PurchaseTable1 pt " & _
        "WHERE pt.PDate < @START " & _
        "UNION ALL " & _
        "SELECT st.ItemId, st.Description, 0 AS PQuantityBefore, st.Quantity AS SQuantityBefore, " & _
        "0 AS QuantityPurchased,0 AS QuantitySold FROM SalesTable1 st " & _
        "WHERE st.SDate < @START " & _
        "UNION ALL " & _
        "SELECT pt.ItemId, pt.Description, 0 AS PQuantityBefore,0 AS SQuantityBefore, " & _
        "pt.Quantity AS QuantityPurchased, 0 AS QuantitySold FROM PurchaseTable1 pt " & _
        "WHERE pt.PDate Between @START and @END " & _
        "UNION ALL " & _
        "SELECT st.ItemId, st.Description, 0 AS PQuantityBefore,0 AS SQuantityBefore, " & _
        "0 AS QuantityPurchased, st.Quantity AS QuantitySold FROM SalesTable1 st " & _
        "WHERE st.SDate Between @START and @END " & _
        ") a " & _
        "GROUP BY a.ItemId, a.Description"

[FishVal]

You are quite welcome.

[justwandering]

Thanks for the link, that explained very nicely.
When you say goods on hand, isn't that the same as ending inventory as of a certain date?
Cost of ending inventory = Beginning inventory + Cost of purchases - Cost of goods sold

Oh well.. It took a while to get here, but I glad that I got the logic now..
based on earlier theories, here's my updated thought:

1. Value of Opening Stock = Value of Total Purchases - (Value of Sales - Profit or Loss), where Date < StartDate

2. Quantity of Opening Stock = Total Quantity Purchased - Total Qty Sold, , where Date < StartDate

3. Purchase Value = Sum of Purchase in amount where date between start and end date.

4. Purchase Qty = Sum of Purchase in Qty where date between start and end date.

5. Sale Value = Sum of Sales in Value where date between start and end date.

6. Sale Qty = Sum of Sales in Qty where date between start and end date.

7. Closing Stock Qty = Sum Purchase Qty - Sum of Sales in Qty where <= end date.

8. Closing Stock Value = Sum of Purchase value - (Total Value of Sales - total Profit or Loss), where Date <= EndDate

Just to add a last point about Cost of Sales : I am calculating P/L for each txn, on a FIFO basis.

Having followed the table structure, I proposed earlier, I am now able to calculate stock on both Average and FIFO method.

THE ICING on the cake is the single sleek query that does the job quite efficiently.

Hope this serves as a good reference point for those in need.