weighted average stock rate

• mukahmed20

  SSC Rookie

  Points: 48

  More actions

  May 7, 2010 at 3:40 am

  #224216

  Hi,

  I have 2 tables

  1. StockIn (ItemId, date, Quantity, Rate)

  2. StockOut (ItemId, date, Quantity)

  in 2nd table i dont have Rate column, this column should be auto calculated using SQL query.

  let suppose i have below records in my tables:

  ItemId Date Qty Rate

  1 1/4/2010 50 2

  1 2/4/2010 100 3

  1 3/4/2010 40 2.5

  ItemId Date Qty Rate

  1 2/4/2010 50

  1 3/4/2010 100

  1 4/4/2010 40

  what i need is to calculate the Rate for 2nd table automatically, using the Quantity and Rate from 1st table, create rate for 1st record of the 2nd table, then using the records from first table and adding calculated rate from 2nd table creates new rate for 2nd row of the table 2. this rate calculation is a weighted average, sum(qty * rate) / sum(qty) from both tables.

  the problem is createing a rate for end table on running basis.

• Nagesh S-432384

  SSCrazy

  Points: 2231

  More actions

  May 7, 2010 at 4:19 am

  #1163923

  give some example for the rate column of second table.

  Nags

  Nag
  ------------------------------------------------
  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537