September 27, 2010 at 10:47 pm
Please down load text file attached 'TestData.txt', set up table.
GO
CREATE TABLE [dbo].[DIM_Data_OHLC_test](
[Symbol] [varchar](10) NOT NULL,
[Date] [datetime] NOT NULL,
[Close] [float] NULL,
[RecID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_DIM_Data_OHLC_test] PRIMARY KEY NONCLUSTERED
(
[Symbol] ASC,
[Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
go
BULK INSERT [dbo].[DIM_Data_OHLC_test]
FROM 'c:\testData.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\r'
)
You see in the datathis:
GOOG,01/03/2000,113.25
GOOG,01/04/2000,56.78
What I am after is when the price of 113.25 on 01/03/2000 is different to the following data 56.78 on the 01/04/2000 (by Symbol and date ASC) is less than 50% or (56.78/113.25) < 50%. Thus price gas fallen more than 50% in one day. Or more likely there has been a stock split of 2:1
NOTE:
1) My table has 2.5 Million records, 1000 symbols, averaging 3000 records per symbol. The scan I am after is total table.
2) My table has the primary key as in th example.
3) Dates are not perfect sequential nor is the RecID. So a sort is required before scanning.
So in the above the final output to a SELECT query would be
GOOG,01/03/2000,113.25
How can this be done ??:-)
September 27, 2010 at 11:06 pm
Using ROW_NUMBER:
WITH CTE AS (SELECT
Symbol,
[Date],
[Close],
ROW_NUMBER() OVER (PARTITION BY Symbol ORDER BY [Date]) AS RN
FROM dbo.DIM_Data_OHLC_test)
SELECT
CTE1.Symbol,
CTE1.[Date],
CTE1.[Close]
FROM CTE CTE1
INNER JOIN CTE CTE2
ON CTE1.Symbol = CTE2.Symbol
AND CTE1.RN = (CTE2.RN - 1)
WHERE
(CTE2.[Close] / CTE1.[Close]) < 0.5;
Of course it doesn't return the row you said it should because 56.78/113.25 is actually: 0.501368653421634 (50.14%) which is greater than 50% so it doesn't qualify.
Hopefully you have approraite indexes or it may take a while to run on 2.5 million records.
September 27, 2010 at 11:45 pm
I figured that you probably want when the value on the next day is less than or equal to 50%, and to account for small differences rounded. If that is the case this should work and does return the GOOG row:
WITH CTE AS (SELECT
Symbol,
[Date],
[Close],
ROW_NUMBER() OVER (PARTITION BY Symbol ORDER BY [Date]) AS RN
FROM dbo.DIM_Data_OHLC_test)
SELECT
CTE1.Symbol,
CTE1.[Date],
CTE1.[Close]
FROM CTE CTE1
INNER JOIN CTE CTE2
ON CTE1.Symbol = CTE2.Symbol
AND CTE1.RN = (CTE2.RN - 1)
WHERE
ROUND(CTE2.[Close] / CTE1.[Close], 2) <= 0.5;
September 28, 2010 at 1:16 am
Thanks
The CTE1 and CTE2
Are you sure you have the order round the correct way, that is the 01/04/2000 record is divided by the 01/03/2000, that is new date divided by the older date ??:-)
September 28, 2010 at 9:44 am
Digs (9/28/2010)
Are you sure you have the order round the correct way, that is the 01/04/2000 record is divided by the 01/03/2000, that is new date divided by the older date ??:-)
Yes, I'm pretty sure I do. Have you tested it?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply