Stock Data - values from diff time intervals

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)

Stock Data - values from diff time intervals

Author

Topic

junkocplcomsg
Starting Member

6 Posts

Posted - 2010-05-12 : 21:55:16

Appreciate your assistance

My FX Data (similar to Stock data) comes in one minute intervals, and each item contains a date, open, high, low and close (OHLC), I need to be able to convert this data to intervals for 5, 15, 60 120 240 minutes intervals

My data looks like this


cdCurPairID  cdDbfxTime	        cdCurOpen  cdCurHigh  cdCurLow	cdCurClose
14	     10.05.10 05:12:00	1.432	   1.4322     1.4314	1.4318
14	     10.05.10 05:11:00	1.4319	   1.4322     1.4317	1.4318
14	     10.05.10 05:10:00	1.4314     1.432      1.4314	1.4318
14	     10.05.10 05:09:00	1.4318	   1.432      1.4313	1.4314
14	     10.05.10 05:08:00	1.4315	   1.432      1.4315	1.4318
14	     10.05.10 05:07:00	1.4321	   1.4323     1.4316	1.4316
14	     10.05.10 05:06:00	1.4325	   1.4326     1.432	1.4322
14	     10.05.10 05:05:00	1.4323	   1.4327     1.4322	1.4325
14	     10.05.10 05:04:00	1.4323	   1.4323     1.432	1.4321
14	     10.05.10 05:03:00	1.4326	   1.4326     1.4323	1.4324
14	     10.05.10 05:02:00	1.4323	   1.4327     1.4322	1.4325
14	     10.05.10 05:01:00	1.4321	   1.433      1.4321	1.4322
14	     10.05.10 05:00:00	1.4325	   1.4326     1.4319	1.432
14	     10.05.10 04:59:00	1.4323	   1.4326     1.4323	1.4326
14	     10.05.10 04:58:00	1.4327	   1.4327     1.4324	1.4324
14	     10.05.10 04:57:00	1.4323	   1.4326     1.4323	1.4326
14	     10.05.10 04:56:00	1.4327	   1.4328     1.4323	1.4323
14	     10.05.10 04:55:00	1.4326	   1.4334     1.4325	1.4329
14	     10.05.10 04:54:00	1.4329	   1.433      1.4327	1.4327
14	     10.05.10 04:53:00	1.4329	   1.4329     1.4328	1.4329

which I have implemented as follows:

//


ALTER PROCEDURE dbo.backup_spIntervalMinMaxClose2

@intCurID integer,
@intIntervalInMinutes integer,
@dtFromDate datetime,
@intMaxRows integer

AS 

set rowcount @intMaxRows 

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(cdDbfxTime) as minTime 
                       MIN(cdCurLow) AS cdCurLow, MAX(cdCurHigh) AS cdCurHigh, cdCurPairID
              FROM     dbo.tblCurrencyData
              WHERE    (cdCurPairID = @intCurID) AND (cdDbfxTime > @dtFromDate)
              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

//

This returned dataset is only correct for the High, Low, and Close, but need as well the "open" value to be correct

if the last few lines in my SQL code is change from:
"ON a.cdCurPairID = b.cdCurPairID AND a.cdDbfxTime = b.MaxTime

changed to
"ON a.cdCurPairID = b.cdCurPairID AND a.cdDbfxTime = b.MinTime"

then I am getting the correct "open" value (If I add as well "a.cdCurClose") - but then I am missing the "close" value

so basically I need

The Open value from b.minTime
and
the Close value from b.MaxTime

I should appreciate your assistance.

Thanks in Advance/Lars

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 10:43:21

try the logic used in below link (only logic dont use code as it is as it wont work in sql 2000)

http://visakhm.blogspot.com/2010/02/aggregating-data-over-time-slots.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

junkocplcomsg
Starting Member

6 Posts

Posted - 2010-05-13 : 22:17:57

Dear Visakh16,

Thank you for the link and your suggestion; I must admit that I did not fully understand your blog entry nor its logic. It appeared to be give a similar result as my own above attempt.

If I wanted totals I would simply amend my above query to include as well as

-> b.TotalTransactions (in outer query)
-> SUM(cdTransactions) as TotalTransactions (the inner query)

(my table includes as well "cdTransactions" field, but you could otherwise similarly count e.g. the cdDbfxTime fields)

like this


SELECT    b.IntervalTime, a.cdDbfxTime, a.cdCurPairID, a.cdCurClose, 
          b.cdCurHigh, b.cdCurLow, b.TotalTransactions
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, SUM(cdTransactions) as TotalTransactions
          FROM      dbo.tblCurrencyData
          WHERE     (cdCurPairID = @intCurID) AND (cdDbfxTime > @dtFromDate)
          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

as this point in time I am inclined to pull all my data from my SQL server into dataset (vb.net) and the make a custom function there, as do not need to return anything back to the sql server.

junkocplcomsg
Starting Member

6 Posts

Posted - 2010-05-13 : 23:39:04

I have added a yet another query to my existings queries

-> This below query does work, if anyone can optimize this, I am all ears.


ALTER PROCEDURE dbo.spIntervalMinMaxCloseVolume4

@intCurID integer,
@intIntervalInMinutes integer,
@dtFromDate datetime,
@intMaxRows integer
 
AS 

set rowcount @intMaxRows  
 
 SELECT TOP 100 PERCENT b.IntervalTime, a.cdDbfxTime, a.cdCurPairID, 
              tblCurrencyData_2.cdCurOpen AS cdCurOpen2, b.cdCurHigh, b.cdCurLow, a.cdCurClose,
              b.TotalTransactions
FROM   dbo.tblCurrencyData a 

INNER JOIN
                    (SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, cdDbfxTime) / @intIntervalInMinutes * @intIntervalInMinutes, 0) AS IntervalTime, MAX(cdDbfxTime) AS MaxTime, 
                                 Min(cdDbfxTime) AS MinTime, MIN(cdCurLow) AS cdCurLow, MAX(cdCurHigh) AS cdCurHigh, cdCurPairID, SUM(cdTransactions) AS TotalTransactions
                     FROM   dbo.tblCurrencyData
                     WHERE (cdCurPairID = @intCurID) AND (cdDbfxTime > @dtFromDate)
                     GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, 
                     cdDbfxTime) / @intIntervalInMinutes * @intIntervalInMinutes, 0), cdCurPairID) b 

ON a.cdCurPairID = b.cdCurPairID AND a.cdDbfxTime = b.MaxTime 

INNER JOIN 

 (SELECT cdCurOpen, cdCurPairID, cdDbfxTime
                     FROM    dbo.tblCurrencyData
                     WHERE (cdCurPairID = @intCurID) AND (cdDbfxTime > @dtFromDate)) tblCurrencyData_2
 
ON b.MinTime = tblCurrencyData_2.cdDbfxTime AND a.cdCurPairID = tblCurrencyData_2.cdCurPairID
ORDER BY a.cdDbfxTime DESC

//

Lars

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources