please help to create a sql statement.

• sushil_dwid

  Ten Centuries

  Points: 1241

  More actions

  November 6, 2014 at 1:42 am

  #312379

  Hi Could you please help to create a sql statement on below requirement

  I have data like below

  CREATE TABLE dbo.Data

  (

  seqNum int,

  StartTime datetime,

  price float,

  Symbol varchar(30)

  );

  -- Populate the table with values.

  INSERT INTO dbo.Data (seqNum,StartTime,price,Symbol)

  select 1, getdate()-10,'0.0120000','WST/INR'

  union all

  select 2, getdate()-9,'0.0210000','WST/INR'

  union all

  select 3, getdate()-8,'0.0300000','WST/INR'

  union all

  select 4, getdate()-7,'0.03100000','WST/INR'

  union all

  select 5, getdate()-6,'0.04100000','WST/INR'

  -----------------I want a sql statement which calculate difference of price movement of different consecutive Start date and then calculate the average of populated difference. Could you please help me?

  seqNumStartTime PriceSymbolDifferenceAvg

  127/10/2014 08:17 0.012WST/INR0.009 0.0215

  228/10/2014 08:17 0.021WST/INR0.009

  329/10/2014 08:17 0.03 WST/INR0.001

  430/10/2014 08:17 0.031WST/INR0.01

  531/10/2014 08:17 0.041WST/INR

• uravindarreddy

  Ten Centuries

  Points: 1390

  More actions

  November 6, 2014 at 2:08 am

  #1756895

  Try Below Query!!!

  select R.seqNum, r.StartTime, r.price, (l.price - r.price) as Diff

  , AVG((l.price - r.price)) over(partition by month(r.starttime)) as average

  from dbo.Data R

  left join dbo.Data L

  on R.seqNum = L.seqNum - 1

• sushil_dwid

  Ten Centuries

  Points: 1241

  More actions

  November 6, 2014 at 2:18 am

  #1756900

  Thanks for your help