Stock Processing

[sazd1]

Hi Student2
I am working on similar kind of thing for stock calculation but could not find any solution to my problem even after putting my problem to different forums.
I saw your post that you have solved this problem of stock calculation.
i need help for that too.
I am using vb express and MsAccess as database.
I am trying to write a query for the calculation of Stock.
My tables are as under:
PurchaseTable(PTId, PDate,ItemId, Description, Quantity, Price, Amount)
SalesTable(STId, SDate,ItemId, Description, Quantity, Price, Amount)

I want a query for stockcalculation to show data as under between some date range or at any given date:

ItemId, QuantityPurchased, QuantitySold, Balance(StockQuantity+QuantityPurchased - QuantitySold)

From your thread i grasped some idea that i will have to make a separate table for StockQuantity as opening stock so that it may be added to newstockpurchased.
Please advise me and if you could give me the query you are using with the tables for this issue it will be highly appreciable.
thanks

** Edit **
Split from Ms Access advanced calculation query, stock level? :S

[kstevens]

Dont forget to account for null values in your calculations
such as:

NZ(Qty, 0) - NZ(Sold, 0)

[sazd1]

Hi Stevens
Thanks for your reply and guidance.
Infact I need help to write a query for stock calculations as I am using Vb express and MsAccess as database.
I want a query to use in Vb express which will work on the tables and retrieve the balance of stock at any given date.
Please advise.

[FishVal]

@sazd1
What does "StockQuantity" mean at all. Something beside purchased and sold?

[sazd1]

StockQuantity means the OriginalStock or beginningStock or OpeningBalanceStock. I want that there should be an opening stock which will add up to the StockPurchased and subtracted from StockSold.
Thanks for your reply

[FishVal]

Is it supposed to be one value per item (a kind of seeding) or, maybe, a single item could have multiple "StockQuantity"'s related to different date.
In other words, could it be so, that "StockQuantity" relevant for a particular item at a particular date is that the latest for this item before that date?

[sazd1]

Hi
Yes you have rightly pointed out that confusion i am having because there should be one opening balance before calculation of stock at any date:
Like if i want to have stock details between 01-09-2009 to 03-09-2009 there should be an opening stock value on 31-08-2009 which will be accounted for the purchases during the period and the sales during the period .
i hope i have been able to clear my point.

[FishVal]

But in this case opening stock value on 31-08-2009 is just purchases minus sales earlier than 31-08-2009.
Having a table of stock openings you run into a problem of "same data stored in multiple places".
The only reason to do it is to enhance performance while trade-off is that you will need to keep stock openings table consistent to data stored in purchases and sales tables.
Are there that many records in purchases and sales tables?

[NeoPa]

Sazd1,

You have hijacked somebody-else's thread, so I have split this into its own.

Please make sure not to do so again. If you have a question then please post it in its own thread. References to other threads are permitted where necessary of course, but taking another thread over is not.

Administrator.

[sazd1]

Hi Neopa
Thanks for your correction. I never had any intention to Hijack anyone's thread. I just thought that i may extend the thread to have answer to a similar query of mine.
Anyways thanks so much for your guidance.

[sazd1]

Hi Fishval
I tried something like this to have balance of stock at any date or interval between two dates

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").ToStr ing()) 
ls.SubItems.Add(dr.Item("QuantitySold").ToString() ) 
ls.SubItems.Add(dr.Item("Balance").ToString()) 
ListView1.Items.Add(ls) 
End While 
End If

But i could not get the desired results. Please advise what i am doing wrong with this.

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.

[NeoPa]

@sazd1
I'm sure you did not, but I hope that you understand better now.

Welcome to Bytes!

[FishVal]

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
 

• Storing both ItemID and Description in those tables is redundant. Do you have something like Items table in your database? If so, then whether [ItemID] field in PurchaseTable and SalesTable tables is FK related to Items table?
• Joining SalesTable with PurchaseTable is completely wrong since it multiplies (produce all possible combinations) records with the same ItemID (join criteria) thus giving 2*2=4 records of each item which of course results in double sum.
• Parameters you've used have no influence since they satisfy all records returned by the join because only PurchaseTable date field is checked.

• PurchaseTable and SalesTable tables have to be aggregated prior to any join. At this point they should be also filtered by relevant date range.
• Since you need stock opening, you have to make to aggregating queries per table. One aggregating in the dates range and one aggregating before the earliest date in the range.
• Then you could join all the four queries on ItemID and, voila, you have dataset where each record has unique ItemID and contains all the necessary data to calculate stock balance - quantity purchase and sold before the date range and the same for the date range.

Regards,
Fish.

[sazd1]

Hi FishVal
Thanks for your eloborated reply with guidance to me as a newbee to programming.
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 tried following query but it gave error "Syntax error in FROM clause"

Dim cmdText As String = "Select a.ItemId,a.Description,Sum(QuantityPurchased),Sum(QuantitySold), " & _ 
"(Sum(QuantityPurchased) - Sum(QuantitySold)) as Balance " & _ 
"From " & _ 
"( SELECT pt.PDate as TransactionDate, pt.ItemId, pt.Quantity AS QuantityPurchased, " & _ 
"0 AS QuantitySold FROM PurchaseTable pt " & _ 
"UNION ALL " & _ 
"Select st.SDate as TransactionDate, st.ItemId, 0 AS QuantityPurchased, " & _ 
"st.Quantity AS QuantitySold " & _ 
"From SalesTable st ) a " & _ 
"Join ItemsTable i On a.ItemId = i.ItemId " & _ 
"WHERE a.TransactionDate Between @START and @END " & _ 
"GROUP BY a.ItemId, i.Description "

Please correct this query what I am doing wrong with this. And also I will request you to please modify this query to accomodate the OpeningBalance issue. Because the programme has to display StockReport between some date range and definitely it will have to account for the balance available for the items immediate prior to the date range as very rightly advised by you.
Thanks for all your help and guidance.

[NeoPa]

You seem to be keeping me busy Sazd1.

Please read the notes by your posts where I've had to edit them. Posting code without tags is not allowed. It also makes your posts harder to read, and therefore less likely that people will want to help you.

[sazd1]

Hi NeoPa
Please accept my apologies again.
I will try not to repeat such mistakes in future and I realize that being an expret you have to tackle so many important issues.
Thanks again for your patience to me.

[NeoPa]

Your polite attitude makes it much easier.

I hope you find all the help you need :)

[FishVal]

@sazd1
The best way to correct it is totally drop it down.
As I've already said you should perform aggregate query on your tables (to get datasets where each record has distinct ItemID) prior to join.
If you have difficulties with subquery syntax, then try to create sequence of queries in you access backend.

Kind regards,
Fish.

[NeoPa]

@FishVal
Subqueries in SQL (in case it helps).

[sazd1]

Hi NeoPa
Thanks. Yes i think the topic of Subqueries will definitely help to understand how subqueries work. I will go through it and will let you know of my problems again.

[NeoPa]

Clearly, you can define QueryDefs and use them as sources in your queries, but it's also possible, using SQL, to include a subquery within your QueryDef. This is explained in more detail in the linked article.

Which approach you proceed with is then down to what you feel most comfortable with.

[justwandering]

On the subject of Stock Tracking and Processing, I recently implemented fairly sleek logic that can run off of a SQL Query using Purchase and sales from a single table and also give me the Opening and Closing Stock as of any date, which is quite helpful. The logic can also give you the Opening and Closing stock value on waited average method of Inventory.

I was wondering if anyone can help find the value of Goods based on FIFO (first in first out method)

here is what I am doing:
Table Structure:
TxnDate
StockCode
Action (Purchase or Sale)
Qty
Rate
Lot Quantity on Hand = (On every Sale reduce the LotQOH, to track FIFO)

Opening Stock = SUM(PurchaseQty-SaleQty) Where TxnDate < StartDate

Purchase = Sum(Purchase) where TxnDate Between StartDate and EndDate

Sale = Sum(Sales) where TxnDate Between StartDate and EndDate

Closing Stock = SUM(Purchase-Sale) Where TxnDate < EndDate

On the same lines,
Value of Closing Stock = Total Purchase Cost - Total Cost of Goods Sold
which in SQL terms is

Value of Closing Stock = Sum(Purchase.Qty*Rate) as TPurchaseCost, TPurchaseCost / Sum(Purchase.Qty)*Sum(Sale.Qty)

--This is = Average Cost of Goods on Hand..

The question is HOW To determine FIFO Cost

[sazd1]

Hi Justwandering
thanks for your reply.
Please check following link for FifoStock. This is absolutely amazing you will definitely enjoy that. It calculates the stock on Fifo basis.
** Edit - Illegal Links to Competing forum removed **
I am working in VB express 2008 with MsAccess as database, And i am trying to find out a query for stock calculations as you mentioned in your reply.
Please have a look on that link and as you already have developed stockCalculations it will be more easy for you to implement that because i have never worked in VBA.
And i hope when you will have a go ahead you will share it with us too.
Thanks again.

[justwandering]

@sazd1
These links don't work.. Am I missing something ?

[sazd1]

** Edit - Links removed **

Ok try the above site link and there search Fifo Stock developed by Khawar

[justwandering]

@sazd1
Looks sensible.. How may I help ?

[justwandering]

While the solution demonstrates a method of recording FIFO, it is limited to only show the CURRENT FIFO based stock valuation.

The tables cannot show the FIFO stock balance and Stock FIFO value as of a certain date, which is what I am interested in.

I've already posted the solution to calculate Opening and Closing date as of any date. The first thing and decent inventory management will offer is Stock Position as of a Date, which is not necessarily current.

[ChipR]

Sorry, but I'm not familiar with FIFO stock tracking. Can you explain how these values that you want are calculated in general? Then we will know whether your data structure supports the calculations.

[justwandering]

@ChipR
Due to limited time, I could only point you to an explanation.
http://accountinginfo.com/study/inve...entory-120.htm

Bottom line - I need to display the Cost of Goods on Hand as of any day.

[ChipR]

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

[justwandering]

@ChipR
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.TransactionDate < @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 WHERE a.TransactionDate < @START, " & _
        "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]

@sazd1
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]

@ChipR
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.