October 8, 2014 at 9:55 pm
Hi,
I have the open, close, high & low prices in a table. These are 0 minutes data, Would want to generate 5min, 15 min, 30 min, 60 min, 120min, 240 min, daily etc data. the table structure is
CREATE TABLE [dbo].[0mindata]
(
[PDateTime] [datetime] NOT NULL,
[Opening] [decimal](5, 2) NOT NULL,
[Closing] [decimal](5, 2) NOT NULL,
[High] [decimal](5, 2) NOT NULL,
[Low] [decimal](5, 2) NOT NULL,
)
Input data
date | Open| Close| high| low
10/1/14 9:15 AM| 20.04| 20.04| 20.04| 20.04
10/1/14 9:16 AM| 20.12| 20.12| 20.12| 20.12
10/1/14 9:17 AM| 20.08| 20.08| 20.08| 20.08
10/1/14 9:18 AM| 20.09| 20.09| 20.09| 20.09
10/1/14 9:19 AM| 20.14| 20.14| 20.14| 20.14
10/1/14 9:19 AM| 20.12| 20.12| 20.12| 20.12
10/1/14 9:20 AM| 20.13| 20.13| 20.13| 20.13
10/1/14 9:21 AM| 20.22| 20.22| 20.22| 20.22
10/1/14 9:22 AM| 20.21| 20.21| 20.21| 20.21
10/1/14 9:23 AM| 20.11| 20.11| 20.11| 20.11
10/1/14 9:24 AM| 20.17| 20.17| 20.17| 20.17
10/1/14 9:25 AM| 20.14|20.14| 20.14| 20.14
Output
date | Open| Close| high| low
10/1/14 9:20 AM| 20.04| 20.13| 20.14| 20.04
10/1/14 9:25 AM| 20.22| 20.14| 20.22| 20.11
This is the result for a 5 min chart data. Open price of 9.15 AM as open, close price of 9.20 AM as Close, max of 9.15-9.20 AM as high & min of 9.15 -9.20 AM as low & like wise. the timeframe can vary like 1 min, 5 min, 10min, etc..
Balasundar S.P
October 9, 2014 at 12:57 am
You could divide the number of minutes by an integer division. For example, you want to have intervals of 5 minutes.
Divide the number of minutes by 5.
0 / 5 = 0
1 / 5 = 0
2 / 5 = 0
3 / 5 = 0
4 / 5 = 0
5 / 5 = 1
6 / 5 = 1
...
This will give you buckets to group on. Simply search for the first, last, min and max values within a group (should be pretty straightforward with windowing functions).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 9, 2014 at 10:00 am
Quick window function solution, note that it averages entries within the same minute, could be handled differently as Koen mentioned. This solution creates sliding 5 and 10 minutes windows on the set, extending it should be a breeze.
😎
USE tempdb;
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID(N'[dbo].[0mindata]') IS NOT NULL DROP TABLE [dbo].[0mindata];
CREATE TABLE [dbo].[0mindata]
(
[PDateTime] [datetime] NOT NULL,
[Opening] [decimal](5, 2) NOT NULL,
[Closing] [decimal](5, 2) NOT NULL,
[High] [decimal](5, 2) NOT NULL,
[Low] [decimal](5, 2) NOT NULL,
);
INSERT INTO [dbo].[0mindata] ([PDateTime],[Opening],[Closing],[High],[Low])
VALUES
('10/1/14 9:15 AM',20.04,20.04,20.04,20.04)
,('10/1/14 9:16 AM',20.12,20.12,20.12,20.12)
,('10/1/14 9:17 AM',20.08,20.08,20.08,20.08)
,('10/1/14 9:18 AM',20.09,20.09,20.09,20.09)
,('10/1/14 9:19 AM',20.14,20.14,20.14,20.14)
,('10/1/14 9:19 AM',20.12,20.12,20.12,20.12)
,('10/1/14 9:20 AM',20.13,20.13,20.13,20.13)
,('10/1/14 9:21 AM',20.22,20.22,20.22,20.22)
,('10/1/14 9:22 AM',20.21,20.21,20.21,20.21)
,('10/1/14 9:23 AM',20.11,20.11,20.11,20.11)
,('10/1/14 9:24 AM',20.17,20.17,20.17,20.17)
,('10/1/14 9:25 AM',20.14,20.14,20.14,20.14);
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,BASE_DATA AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY ZM.PDateTime
ORDER BY ZM.PDateTime DESC
) AS ZM_DRID
,ZM.PDateTime
,AVG(ZM.Opening) OVER
(
PARTITION BY ZM.PDateTime
) AS Opening
,AVG(ZM.Closing) OVER
(
PARTITION BY ZM.PDateTime
) AS Closing
,AVG(ZM.High ) OVER
(
PARTITION BY ZM.PDateTime
) AS High
,AVG(ZM.Low ) OVER
(
PARTITION BY ZM.PDateTime
) AS Low
FROM dbo.[0mindata] ZM
)
SELECT
BD.PDateTime
,BD.Opening
,BD.Closing
,BD.High
,BD.Low
/* 5 Min */
,MIN(BD.Opening) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY BD.PDateTime DESC
ROWS BETWEEN CURRENT ROW
AND 4 FOLLOWING
) AS MIN_5M_Opening
,MAX(BD.Opening) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY BD.PDateTime DESC
ROWS BETWEEN CURRENT ROW
AND 4 FOLLOWING
) AS MAX_5M_Opening
,MIN(BD.Closing) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY BD.PDateTime DESC
ROWS BETWEEN CURRENT ROW
AND 4 FOLLOWING
) AS MIN_5M_Closing
,MAX(BD.Closing) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY BD.PDateTime DESC
ROWS BETWEEN CURRENT ROW
AND 4 FOLLOWING
) AS MAX_5M_Closing
,MIN(BD.High) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY BD.PDateTime DESC
ROWS BETWEEN CURRENT ROW
AND 4 FOLLOWING
) AS MIN_5M_High
,MAX(BD.High) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY BD.PDateTime DESC
ROWS BETWEEN CURRENT ROW
AND 4 FOLLOWING
) AS MAX_5M_High
,MIN(BD.Low) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY BD.PDateTime DESC
ROWS BETWEEN CURRENT ROW
AND 4 FOLLOWING
) AS MIN_5M_Low
,MAX(BD.Low) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY BD.PDateTime DESC
ROWS BETWEEN CURRENT ROW
AND 4 FOLLOWING
) AS MAX_5M_Low
/* 10 Min */
,MIN(BD.Opening) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY BD.PDateTime DESC
ROWS BETWEEN CURRENT ROW
AND 9 FOLLOWING
) AS MIN_10M_Opening
,MAX(BD.Opening) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY BD.PDateTime DESC
ROWS BETWEEN CURRENT ROW
AND 9 FOLLOWING
) AS MAX_10M_Opening
,MIN(BD.Closing) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY BD.PDateTime DESC
ROWS BETWEEN CURRENT ROW
AND 9 FOLLOWING
) AS MIN_10M_Closing
,MAX(BD.Closing) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY BD.PDateTime DESC
ROWS BETWEEN CURRENT ROW
AND 9 FOLLOWING
) AS MAX_10M_Closing
,MIN(BD.High) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY BD.PDateTime DESC
ROWS BETWEEN CURRENT ROW
AND 9 FOLLOWING
) AS MIN_10M_High
,MAX(BD.High) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY BD.PDateTime DESC
ROWS BETWEEN CURRENT ROW
AND 9 FOLLOWING
) AS MAX_10M_High
,MIN(BD.Low) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY BD.PDateTime DESC
ROWS BETWEEN CURRENT ROW
AND 9 FOLLOWING
) AS MIN_10M_Low
,MAX(BD.Low) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY BD.PDateTime DESC
ROWS BETWEEN CURRENT ROW
AND 9 FOLLOWING
) AS MAX_10M_Low
FROM BASE_DATA BD
WHERE BD.ZM_DRID = 1
Results
PDateTime Opening Closing High Low MIN_5M_Opening MAX_5M_Opening MIN_5M_Closing MAX_5M_Closing MIN_5M_High MAX_5M_High MIN_5M_Low MAX_5M_Low MIN_10M_Opening MAX_10M_Opening MIN_10M_Closing MAX_10M_Closing MIN_10M_High MAX_10M_High MIN_10M_Low MAX_10M_Low
----------------------- ----------- ----------- ----------- ----------- --------------- --------------- --------------- --------------- ------------ ------------ ----------- ----------- ---------------- ---------------- ---------------- ---------------- ------------- ------------- ------------ ------------
2014-10-01 09:25:00.000 20.140000 20.140000 20.140000 20.140000 20.110000 20.220000 20.110000 20.220000 20.110000 20.220000 20.110000 20.220000 20.080000 20.220000 20.080000 20.220000 20.080000 20.220000 20.080000 20.220000
2014-10-01 09:24:00.000 20.170000 20.170000 20.170000 20.170000 20.110000 20.220000 20.110000 20.220000 20.110000 20.220000 20.110000 20.220000 20.040000 20.220000 20.040000 20.220000 20.040000 20.220000 20.040000 20.220000
2014-10-01 09:23:00.000 20.110000 20.110000 20.110000 20.110000 20.110000 20.220000 20.110000 20.220000 20.110000 20.220000 20.110000 20.220000 20.040000 20.220000 20.040000 20.220000 20.040000 20.220000 20.040000 20.220000
2014-10-01 09:22:00.000 20.210000 20.210000 20.210000 20.210000 20.090000 20.220000 20.090000 20.220000 20.090000 20.220000 20.090000 20.220000 20.040000 20.220000 20.040000 20.220000 20.040000 20.220000 20.040000 20.220000
2014-10-01 09:21:00.000 20.220000 20.220000 20.220000 20.220000 20.080000 20.220000 20.080000 20.220000 20.080000 20.220000 20.080000 20.220000 20.040000 20.220000 20.040000 20.220000 20.040000 20.220000 20.040000 20.220000
2014-10-01 09:20:00.000 20.130000 20.130000 20.130000 20.130000 20.080000 20.130000 20.080000 20.130000 20.080000 20.130000 20.080000 20.130000 20.040000 20.130000 20.040000 20.130000 20.040000 20.130000 20.040000 20.130000
2014-10-01 09:19:00.000 20.130000 20.130000 20.130000 20.130000 20.040000 20.130000 20.040000 20.130000 20.040000 20.130000 20.040000 20.130000 20.040000 20.130000 20.040000 20.130000 20.040000 20.130000 20.040000 20.130000
2014-10-01 09:18:00.000 20.090000 20.090000 20.090000 20.090000 20.040000 20.120000 20.040000 20.120000 20.040000 20.120000 20.040000 20.120000 20.040000 20.120000 20.040000 20.120000 20.040000 20.120000 20.040000 20.120000
2014-10-01 09:17:00.000 20.080000 20.080000 20.080000 20.080000 20.040000 20.120000 20.040000 20.120000 20.040000 20.120000 20.040000 20.120000 20.040000 20.120000 20.040000 20.120000 20.040000 20.120000 20.040000 20.120000
2014-10-01 09:16:00.000 20.120000 20.120000 20.120000 20.120000 20.040000 20.120000 20.040000 20.120000 20.040000 20.120000 20.040000 20.120000 20.040000 20.120000 20.040000 20.120000 20.040000 20.120000 20.040000 20.120000
2014-10-01 09:15:00.000 20.040000 20.040000 20.040000 20.040000 20.040000 20.040000 20.040000 20.040000 20.040000 20.040000 20.040000 20.040000 20.040000 20.040000 20.040000 20.040000 20.040000 20.040000 20.040000 20.040000
October 10, 2014 at 1:50 am
Koen, i am not very familiar with windowing functions. If you could give me the select query would be of great help..
October 10, 2014 at 1:57 am
balasundar.sp (10/10/2014)
Koen, i am not very familiar with windowing functions. If you could give me the select query would be of great help..
You should see this as an opportunity to learn.
Why don't you give it a try first and then come back with specific questions, instead of going for the pre-chewed solution?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 10, 2014 at 2:42 am
Koen Verbeeck (10/10/2014)
balasundar.sp (10/10/2014)
Koen, i am not very familiar with windowing functions. If you could give me the select query would be of great help..You should see this as an opportunity to learn.
Why don't you give it a try first and then come back with specific questions, instead of going for the pre-chewed solution?
HINT: Reading the already posted solutions might prove to be helpful!
😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply