CODEWITHSUNDEEP
t-sqlselecttimesql-server-2008-r2sql-server-express
Yugmorf
Yugmorf

Reputation: 340

tsql return last price in each 10min time interval

I have a table comprising three columns: Date, Time, and Price. The 'time' column is approximately minute intervals, but not every minute interval is represented. I wish to create a new table comprising Date, FlooredTime, LastPriceInInterval, in which FlooredTime = the Time at the beginning of each 10min interval, and LastPriceInInterval is the price at the max available time within each interval. 

Old Table:                          New Table:

Date    Time            Price       Date        FlooredTime LastPriceInInterval
2012-05-10  02:50:00    1352.7      2012-05-10  02:40:00    1353.0
2012-05-10  02:46:00    1353.0      2012-05-10  02:30:00    1353.5
2012-05-10  02:45:00    1352.8              
2012-05-10  02:44:00    1353.2              
2012-05-10  02:43:00    1353.1              
2012-05-10  02:42:00    1353.2              
2012-05-10  02:40:00    1353.4              
2012-05-10  02:39:00    1353.5              
2012-05-10  02:38:00    1354.6              
2012-05-11  03:31:00    1355.0              
2012-05-11  03:29:00    1354.0

This is what i have so far, but now i'm stuck. It seems that the inner select statement doesn't like the use of Max in the where clause; Max(datepart(minute,m1.Time)%10). Would greatly appreciate knowing how to achieve the desired outcome using permitted syntax.

SELECT TOP 1000 Date
   ,DATEADD(minute,-DATEPART(minute,Time)%10 ,Time) as FlooredTime
   ,(Select Price from dbo.MyData
         where m3.Date = m1.Date
         and DATEPART(hour, m3.Time) = DATEPART(hour, m1.Time)
         and datepart(minute,m3.Time)/10 = Floor(datepart(minute,m1.Time)/10)
         and datepart(minute,m3.Time)%10 = Max(datepart(minute,m1.Time)%10)
         ) as LastPriceInInterval
FROM dbo.MyData
where DATEPART(minute,Time)%10 = 0
order by Date Desc, Time Desc

Thanks!

A late edit - I'll later use the results as the source table in a Merge expression.

Upvotes: 2

Views: 1050

Answers (2)

Andriy M
Andriy M

Reputation: 77717

You could rank rows in the reversed order of Time within their respective 10-minute intervals, then pick the top-ranked ones:

WITH ranked AS (
  SELECT
    *,
    FlooredTime = DATEADD(MINUTE, -DATEDIFF(MINUTE, 0, Time) % 10, Time),
    TimeRank    = ROW_NUMBER() OVER (
      PARTITION BY DATEDIFF(MINUTE, 0, Time) / 10
      ORDER BY Time DESC
    )
  FROM MyData
)
SELECT
  Date,
  FlooredTime,
  LastPriceInInterval = Price
FROM ranked
WHERE TimeRank = 1
ORDER BY
  Date DESC,
  FlooredTime DESC
;

Here's a SQL Fiddle demo for the query.

Upvotes: 1

dan radu
dan radu

Reputation: 2782

Could you identify the intervals first, get the maximum time for each interval, then join with the table to get the price? Using a CTE you could easily achieve this:

;WITH intervals(Date, T1, MaxTime) AS (
SELECT
    Date
    , DATEADD(minute, -DATEPART(minute, Time)%10, Time)
    , MAX(Time) AS MaxTime
FROM dbo.MyData
GROUP BY Date
    , DATEADD(minute, -DATEPART(minute, Time)%10, Time)
)
SELECT t.Date AS Date, i.T1 AS Time, t.Price
FROM dbo.MyData t
INNER JOIN intervals i ON t.Date = i.Date AND t.Time = i.MaxTime
ORDER BY Date DESC, Time DESC

Upvotes: 2

Related Questions