Min/Max values in time interval

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Min/Max values in time interval

Author

Topic

junkocplcomsg
Starting Member

6 Posts

Posted - 2010-01-04 : 00:53:34

My foreign exchange db contains som 40 currency pairs (like EUR/USD) with its date/time high-, low-, close values - one record per minute, in total circa 1.5 mio entries. I use the data to draw a candlestick graph

sample data from my table: tblCurrencyData
cdDbfxTime cdCurPairID cdCurOpen cdCurHigh cdCurLow cdCurClose
04.01.10 05.40.00 1 1.4298 1.4301 1.4298 1.4301
04.01.10 05.15.00 1 1.4293 1.4294 1.4293 1.4294
04.01.10 04.52.00 1 1.4293 1.4293 1.4293 1.4293
04.01.10 04.33.00 1 1.4288 1.4289 1.4288 1.4288

I need as well to draw a graphs with intervals for say 5, 10, 30, 60 minutes as well as 4,6,24 hours, 7 days, 28 days

my select query is as follows - and it works for period in excess of 60 minutes*, I trust that there must be some easier and nicer solution. (this code is for 6 hours intervals) - the idea is that I am rebuilding a new time entry ...

this query works
SELECT a.cdDbfxTime, a.cdCurOpen, a.cdCurPairID, b.CurHigh, b.CurLow, b.dbfxmins
FROM dbo.tblCurrencyData a INNER JOIN
(SELECT TOP 100 PERCENT MIN(cdDbfxTime) AS mintime, MAX(cdDbfxTime) AS maxTime, MIN(cdCurHigh) AS CurLow, MAX(cdCurHigh) AS CurHigh, DATEPART(yy, cdDbfxTime) * 1000000 + DATEPART(mm, cdDbfxTime) * 10000 + DATEPART(dd, cdDbfxTime) * 100 + DATEPART(HH, cdDbfxTime) / 6 AS dbfxmins, cdCurPairID
FROM dbo.tblCurrencyData
WHERE (cdCurPairID = 1) AND (cdDbfxTime > '2009-01-01')
GROUP BY DATEPART(yy, cdDbfxTime) * 1000000 + DATEPART(mm, cdDbfxTime) * 10000 + DATEPART(dd, cdDbfxTime) * 100 + DATEPART(HH, cdDbfxTime) / 6, cdCurPairID
ORDER BY DATEPART(yy, cdDbfxTime) * 1000000 + DATEPART(mm, cdDbfxTime) * 10000 + DATEPART(dd, cdDbfxTime) * 100 + DATEPART(HH, cdDbfxTime) / 6) b ON a.cdCurPairID = b.cdCurPairID AND a.cdDbfxTime = b.mintime

I should be plsd to hear you further suggestions - thanks in advance

* i cant us this for period less than 60 minutes as my datepart would be as follows:

(DATEPART(yy, cdDbfxTime) * 100000000 + DATEPART(mm, cdDbfxTime) * 1000000 + DATEPART(dd, cdDbfxTime) * 10000 + DATEPART(HH, cdDbfxTime) * 100 +DATEPART(nn, cdDbfxTime)) / 30) AS dbfxmins
- as i get an integer overflow error.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-04 : 05:38:20

[code]DECLARE @MinuteInterval INT = 30 -- 1440 is whole day, 2880 is two days, 60 is full hour

SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, cdDbfxTime) / 30 * 30, 0) AS IntervalTime,
MIN(cdDbfxTime) AS MinTime,
MAX(cdDbfxTime) AS MaxTime,
MIN(cdCurHigh) AS CurLow,
MAX(cdCurHigh) AS CurHigh
FROM tblCurrencyData
WHERE cdCurPairID = 1
AND cdDbfxTime > '20090101'
GROUP BY DATEADD(MINUTE, @MinuteInterval * DATEDIFF(MINUTE, 0, cdDbfxTime) / @MinuteInterval, 0)
ORDER BY DATEADD(MINUTE, @MinuteInterval * DATEDIFF(MINUTE, 0, cdDbfxTime) / @MinuteInterval, 0)[/code]

N 56°04'39.26"
E 12°55'05.63"

junkocplcomsg
Starting Member

6 Posts

Posted - 2010-01-04 : 09:13:33

Dear Peso,
Thanks you for your very swift reply, highly appreciated. Your above post has saved me quite some time. I did however need the nested "select", in order to get the closing value in the interval.

My code now reads as follows:

CREATE PROCEDURE dbo.[spIntervalMinMaxClose]

@intCurID integer,
@intIntervalInMinutes integer

AS

SELECT b.IntervalTime, a.cdDbfxTime, a.cdCurPairID, a.cdCurClose, b.cdCurHigh, b.cdCurLow
FROM dbo.tblCurrencyData a INNER JOIN
(SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, cdDbfxTime) / @intIntervalInMinutes * @intIntervalInMinutes , 0) AS IntervalTime,
MAX(cdDbfxTime) AS MaxTime,
MIN(cdCurLow) AS cdCurLow, MAX(cdCurHigh) AS cdCurHigh,
cdCurPairID
FROM dbo.tblCurrencyData
WHERE (cdCurPairID = @intCurID)
AND (cdDbfxTime > CONVERT(DATETIME, '2010-01-04 00:00:00', 102))
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, cdDbfxTime) / @intIntervalInMinutes * @intIntervalInMinutes , 0), cdCurPairID) b
ON a.cdCurPairID = b.cdCurPairID AND a.cdDbfxTime = b.MaxTime
order by cdDbfxTime desc
GO

Thanks again/Lars

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources