Select Statement

Select Statement

• Mike Turner - MSFT

  I was talking about cost of good, not selling price.

  the structure store common field as for an item_txns table would store. see my reply to Andy

  
  

• eran koren

  Thanks for the reply. I am sorry if i sounded being forcefully. But i was trying to explain my problem.

  " run a series of queries to calculate the Cost for each month - ONE ROW AT A TIME! "

  I think I can not do this too. The previous sales transaction would have 0 as its cost. Did you mean to use Select Statement to do that If it can, what is the Select Statement would like to be

  The best thing i can do is Scan the record and calculate it by adding the qty and cost and calculate the average if this is purchase txn, and if the current record is sales transaction then store the previous calculated average cost to this sales transaction.

  The reason i dont store the cost of good sold is because cogs is calculated value and other reason is to have a true accurate moving average. I also know this would have an impact on performance when the table has many records

  
  

• StefanoUD

  "but just want the actual CoG at a specific moment in time. "

  That is true. I want a moving average COGS at  a specific moment in time (for the the past 'N' transactions or period of 'N' or 'N' months).

  When user is entering the sales transactions and post the transactions, the cost of good sold is 0.This will to be calculated on users query the cogs report or when querying the financila report I would need to know the Cogs.

  By this time I would create a view the same as above table you give and apply the calculated cogs at a specific mement in accourdance to the date chronologic. The date transactions of the sale would determine which cogs for a specific moment it takes

  By doing this i would get a true moving average even the users at a later point edit sales transaction or purchase transaction. Or entry a past purchase transaction (prior date) which of course  affects the cost of good for subsequent transactions from the date of the purchase transactions up to the most current transactions

  I want the behaviour of true moving average.

  "if i can calculate the cost of good at a point prior to a sales "

  but using true moving average method. That why i want to create a view with the same struct as above.

  date     ctxntype       SourceNo       qty         cost         QtyBal         Balance   avrg

  1 jan      purchase     P001               5          5000                5           5000      1000

  2 jan      sales          S001               -4          *-4000               1            1000     1000

  3 jan      Purchase    P002               10          12750             11           13750    1250

  *The cosf of good sale would have to be calculated on the fly by building this view.

  If only i could calculate the moving average cost when users post the sales s001 using Select Statement. Being able to calculate moving average here is instead of storing 0 i would store the calculated value here  Incase if users turn of the feature of calculating the moving average on query report.

  I know this value will be invalid if users edit any sales transaction or purchase transaction prior s001.

  if users want a more correct moving average then  i would have to recalculate on querying the report

   

   

   

   

  
  

• Phil Doucette

  Does that means that I can only do this only by writing vfp code by scanning the whole record and calculates for each records

  How about if separate it in several View

  The problem is only if i can calculate the cost of good at a point prior to a sales txn then i can calculate the cost .

  Using this feature i would always get the correct of cost of good sold using moving average method whn users at a later time entry invoice that happens at a prior period

  If i store the calculated cost on item table, and users enter invoice for prior period or prior date the moving average would be the last average, But i want to enable the users to get the real moving average for that time

  
  

• Rob-C

  Hi
  
  What happens when the price changes Won't you be able to tell what was the price when it was sold, will you
  
  Please show the structure of other tables and we try to find out something.
  
  
  
  

• NTx Beta

  >> The cost of good sale would have to be calculated on the fly by building this view.

  But as I keep telling you, you cannot do this in SQL and I simply don't know how else to say it..you can't do that. You can calculate it as a snapshot (i.e. for one month) but you cannot use a View (which is just an SQL statement) to calculate a series of values on the fly. 

  All you can do is use the view to create an empty column and then run a series of queries to calculate the Cost for each month - ONE ROW AT A TIME!

  This is, as I keep telling you, an example of row-based processing which cannot be handled using a Set-Oriented (i.e. SQL) approach. If you don't like it, I am sorry, but there is no other answer no matter how much you want there to be one.

  
  
  

• mball

  You keep referring to the 'moving average', however, you also say: "if i can calculate the cost of good at a point prior to a sales txn"

  This implies that you don't want a "moving average" at all, but just want the actual CoG at a specific moment in time. That is easy enough, all you need to do is to sum the actual quantity and cost, calculate the average and group by item.

  SELECT item, SUM(qty) AS tqty, SUM(cost) AS tcost, AVG( qty/cost) AS acost ;
     FROM cost ;
   GROUP BY itemid ;
   INTO CURSOR curAvgCost

  However, this is not a "moving average"

  A Moving Average is where you consider costs for the past 'N' transactions or period of 'N' months and, when a new transaction arises you drop the oldest out to add in the new one - in other words the cost is always based on the most recent 'N' transactions (usually n = 12 months)

  You can even calculate the current 'moving average' cost by applying a date range to the basic query above.

  But, as I have said, what you cannot do is to calculate the true moving average cost which is, of course, varying with time as transactions are included and excluded from the calculation. A true 3-month moving average cost would look like this:

  Month  Qty  Cost    Avge 
  Jan    100  $200   $2.00  (Jan only)
  Feb     50  $110   $2.06  (Jan, Feb)
  Mar    100  $225   $2.14  (Jan, Feb, Mar)
  Apr     50  $110   $2.13  (Feb, Mar, Apr)
  May     50  $125   $2.30  (Mar, Apr, May)
  and so on

  This is the table you cannot construct using SQL and for which you would have to use Code and process each record individually

  
  
  

• sriksdave

  >> Basically i want to create a view that calculate the stock end balance using moving average. On entry invoice the prg would create txn on a table item_txns that store all txns that affects the balance.

  >> i want to use Select statement that can calculate the Cost of Good Sold and Stock Balance

  You cannot do this in a single SQL statement. SQL is a set-based query language so unless you can define the set you cannot handle the calculation. The nearest you could get would be to use a conditional SUM() and group by Item. That would give you the total balance but you can only do a moving average if you process the data one row at a time and re-calculate the average as you go. This is the thing you can't do in SQL.

  
  
  
• Select Statement