stock

• bharathiittrends

  Newbie

  Points: 3

  More actions

  December 13, 2010 at 5:26 am

  #242393

  hi techies,,,

  i m very new in data warehousing, i came across this business requirements........................

  I have one table "Sales"

  Item_id Date QTY amt BUY/SELL

  1 10/10/2010 10 250 P

  2 14/02/2010 10 700 p

  3 15/6/2010 15 750 p

  1 12/10/2010 05 125 m

  2 25/04/2010 10 500 p

  1 28/11/2010 25 1000 p

  2 30/05/2010 15 ?

  here "P" denotes plus(buy) "M" denotes minus(sold) products

  here when i m trying to sell the product (1) the result should be like

  item, quantity and the average amount(only for sale) to be sell of that specific product should come up i need SQLquery to find the solution for this What is the easiest/fastest way?????? Can you please suggest me the best query to find the solution for this?

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  December 13, 2010 at 11:56 pm

  #1262048

  though your requirement is not so clear but still i tried

  declare @t table (

  item_id int,

  dates datetime,

  qty int ,

  Amount int ,

  Status char(1)

  )

  insert into @t (item_id,dates,qty,Amount,Status)

  select 1, '10/10/2010', 10, 250, 'p'

  union select 2, '4/02/2010', 10, 700, 'm'

  union select 3, '5/6/2010' ,15, 750 ,'p'

  union select 1, '2/10/2010', 05, 125, 'm'

  union select 2, '5/04/2010', 10 ,500, 'p'

  union select 1, '8/11/2010', 25, 1000, 'p'

  select item_id,avg(amount) from @t

  where status = 'M'

  group by item_id

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)