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?

  seqNum StartTime Price Symbol Difference Avg

  1 27/10/2014 08:17 0.012 WST/INR 0.009 0.0215

  2 28/10/2014 08:17 0.021 WST/INR 0.009

  3 29/10/2014 08:17 0.03 WST/INR 0.001

  4 30/10/2014 08:17 0.031 WST/INR 0.01

  5 31/10/2014 08:17 0.041 WST/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