Return recordset group by a particular interval RSS

• • Reply
  

  ishq

  Member

  256 Points

  76 Posts

  Return recordset group by a particular interval

  Dec 20, 2012 01:06 PM|LINK

  Hi,
  I have a table like this

  Id        ScriptName       TradeTime,                  TradeValue
  --         ----------          ------------------             ------------ 
  2        INFY                20-12-2012 09:00:15        2307.75
  3        INFY                20-12-2012 09:00:18        2307.15
  4        INFY                20-12-2012 09:00:21        2306.85
     .
     .
     .
  16      INFY                20-12-2012 09:00:57       2306.30
  17      INFY                20-12-2012 09:01:00       2306.15
  18      INFY                20-12-2012 09:01:03       2305.95
     .
     .
     .
  57     INFY                20-12-2012 09:03:00       2300.10
  58     INFY                20-12-2012 09:03:03       2298.85
  59     INFY                20-12-2012 09:03:06       2300.35
  60     INFY                20-12-2012 09:03:09       2300.15
  61     INFY                20-12-2012 09:03:12       2300.35
     .
     .
     .
  127   INFY               20-12-2012 09:06:30       2299.60
  128   INFY               20-12-2012 09:06:33       2300.15
  129   INFY               20-12-2012 09:06:36       2300.20
     .
     .
     .
  794   INFY              20-12-2012 09:39:51       2302.00
  795   INFY              20-12-2012 09:39:54       2301.65
  796   INFY              20-12-2012 09:39:57       2302.00
  797   INFY              20-12-2012 09:40:00       2302.00

  If a trade happens one new row will be inserted in the table. I would like to take the summary of a particular interval. Suppose my interval is 5 minutes. Then my resultset should be like this

  TradeTime                    Open                   High                  Low                       Close
  ----------------------------------------------------------------------------------------
  20-12-2012 09:05:00      2307.75         2308.54             2304.26                2306.45
  20-12-2012 09:10:00      2306.55         2307.74             2306.44                2307.71
  20-12-2012 09:15:00      2307.76         2308.41             2306.47                2306.95
      .
      .
      .
  20-12-2012 11:15:00       2296.77        2298.23               2296.05               2297.95
  20-12-2012 11:20:00       2297.45        2297.87               2296.75               2297.10
  (Last time)                 (first value)       (Highest value)   (Lowest value)    (last value) of that particular interval(here 5 minutes).

  Can anyone help me to fetch a record set like this.
  Query, Stored procedure, temporary table, cursor.. use of anything is acceptable

  Thanks in advance

  Keep smiling..
  Lathika Morthodi
• • Reply
  

  Mudasir.Khan

  All-Star

  15346 Points

  3142 Posts

  Re: Return recordset group by a particular interval

  Dec 20, 2012 01:12 PM|LINK

  here is the Pseudo code please try

  Select TradeTime as LastTime Min(TradeValue) as LowestValue, Max(TradeValue) as HighestValue Where TradeTime>Now-5Min

  View Blog
• • Reply
  

  ishq

  Member

  256 Points

  76 Posts

  Re: Return recordset group by a particular interval

  Dec 20, 2012 01:31 PM|LINK

  Thanks Mudasir khan.

  But how can i write query for one particular time- 5 minutes?

  More than that, First value ,Highest value,Lowest value and close values are for each 5 minutes interval. I mean Highest value of each 5 minutes,Lowest value of each 5 minutes, first value of each 5 minutes, last value of each 5 minutes.. like that.

  Me too know the logic. But I am not able to implement that as a query or stored procedure. My logic is like

  Select lastvalue of 5 minutes(tradetime) as TradeTime ,first value of 5 minutes(TradeValue) as Open, Highest value of 5 minutes(TradeValue) as High, Lowest value of 5 minutes(TradeValue) as Low, Last value of 5 minutes(TradeValue) as Close for each 5 minutes interval..

   

  Keep smiling..
  Lathika Morthodi
• • Reply
  

  limno

  All-Star

  117336 Points

  8003 Posts

  Moderator

  MVP

  Re: Return recordset group by a particular interval

  Dec 20, 2012 04:42 PM|LINK

  Here is a code sample adn you should be able to modify to fit your need.

  --Prepare sample table
  ;with yourTable as
  (
  select 1 as i, 'aaa' as Name, dateadd(hour,9,DATEADD(day, DATEDIFF(day,0,GETDATE()),0)) TradeTime, 100 as TradeValue
  union all
  select i+1 as i, name, dateadd(minute,1,TradeTime) TradeTime,  TradeValue+i  from yourTable where i<100
  )
  
  
  --Main query
  , mycte
  as
  (
  SELECT
  datepart(hour,TradeTime) hr, datepart(minute,TradeTime)/5 mi,
  name, Min(TradeTime)  Over(partition by datepart(hour,TradeTime), datepart(minute,TradeTime)/5)  as TradeTime, TradeValue
  ,max(TradeValue) Over(partition by datepart(hour,TradeTime), datepart(minute,TradeTime)/5) as  maxVal
  ,min(TradeValue) Over(partition by datepart(hour,TradeTime), datepart(minute,TradeTime)/5) as minVal
  ,row_number() over(partition by datepart(hour,TradeTime), datepart(minute,TradeTime)/5 order by TradeTime ) as rn
  FROM yourTable)
  
  
  SELECT  name,TradeTime, MAX(CASE WHEN rn=1 THEN TradeValue END)  as OpenVal
  ,Max(maxVal) as maxVal
  ,Max(minVal) as minVal,
   MAX(CASE WHEN rn=5 THEN TradeValue END) as CloseVal FROM mycte
  group by name, TradeTime

  
  
  

  Limno
  
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
  • Marked as answer by Catherine Shan - MSFT on Dec 28, 2012 01:13 AM