the right direction. I have about a years worth of work in excel that I am
looking to put into a database to then create some sort of windows app to
work with. Here is a sample of what I am working with. This is coming in fro
m
an excel spreadsheet. I have figured out how to get it from the worksheet to
a database using linked servers and how to convert the date time issues.
This is what it looks like when I first import it to sql server 2005 express
.
First two column are datetime, other 4 are smallmoney, no nulls in any of
them
Date Time Open High Low Close
1/3/2000 12:00:00 AM 12/30/1899 2:30:00 AM 1.6205 1.6213 1.6199 1.6211
1/3/2000 12:00:00 AM 12/30/1899 3:00:00 AM 1.6209 1.6213 1.6162 1.6180
1/3/2000 12:00:00 AM 12/30/1899 3:30:00 AM 1.6183 1.6204 1.6176 1.6195
1/3/2000 12:00:00 AM 12/30/1899 4:00:00 AM 1.6193 1.6203 1.6180 1.6182
1/3/2000 12:00:00 AM 12/30/1899 4:30:00 AM 1.6185 1.6195 1.6166 1.6173
1/3/2000 12:00:00 AM 12/30/1899 5:00:00 AM 1.6174 1.6183 1.6166 1.6179
1/3/2000 12:00:00 AM 12/30/1899 5:30:00 AM 1.6181 1.6185 1.6168 1.6173
1/3/2000 12:00:00 AM 12/30/1899 6:00:00 AM 1.6174 1.6177 1.6168 1.6173
1/3/2000 12:00:00 AM 12/30/1899 6:30:00 AM 1.6171 1.6173 1.6150 1.6154
1/3/2000 12:00:00 AM 12/30/1899 7:00:00 AM 1.6152 1.6163 1.6150 1.6159
1/3/2000 12:00:00 AM 12/30/1899 7:30:00 AM 1.6161 1.6163 1.6152 1.6163
1/3/2000 12:00:00 AM 12/30/1899 8:00:00 AM 1.6165 1.6180 1.6150 1.6180
1/3/2000 12:00:00 AM 12/30/1899 8:30:00 AM 1.6183 1.6205 1.6175 1.6195
Now I can do the convert deal to move the time from Time and merge with Date
so that I get rid of the 12:00:00AM on each date. Then I can change the Time
so that it is only char(5). What I would like to be able to do is run a quer
y
or sp that will create 'days' from this 30 minute data. The start of the day
would be '02:30' and end would be '02:00' and these 'days' would contain a
max(high), min(low), close('02:00')
Then all this would go into a new table as Daily info that I will do more to
.
Now my plan was to create one main database that would contain each symbol i
n
its own table for intraday info and another table for daily info and so on.
At this point I have no PK or FK set between any of them which is bad news I
know, but what I need to see for my own mind to start understanding all this
is how things will relate before I start applying things I dont understand.
Any help in getting me going in the right direction would be wonderful.
Thanks.
Rob
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200711/1On Wed, 07 Nov 2007 15:28:40 GMT, RBS via droptable.com wrote:
(snip)
>This is what it looks like when I first import it to sql server 2005 expres
s.
>First two column are datetime, other 4 are smallmoney, no nulls in any of
>them
>Date Time Open High Low Close
>1/3/2000 12:00:00 AM 12/30/1899 2:30:00 AM 1.6205 1.6213 1.6199 1.6211
>1/3/2000 12:00:00 AM 12/30/1899 3:00:00 AM 1.6209 1.6213 1.6162 1.6180
(snip)
>Now I can do the convert deal to move the time from Time and merge with Dat
e
>so that I get rid of the 12:00:00AM on each date. Then I can change the Tim
e
>so that it is only char(5). What I would like to be able to do is run a que
ry
>or sp that will create 'days' from this 30 minute data. The start of the da
y
>would be '02:30' and end would be '02:00' and these 'days' would contain a
>max(high), min(low), close('02:00')
Hi Rob,
The first issue is combining date and time. You say that you already
solved this, but allow me to digress :-). In an ideal world, you would
be able to just add date and time. Unfortunately, SQL Server uses
1/1/1900 as its base for datetime calculations, and Excel uses
12/31/1899, so adding the imported "time" to the "date" would result in
a two-day loss. This can be remedied by adding the two back in - so the
shortest way to get the combined datetime is to use
SET DatePlusTime = Date + Time + 2
The second step is aggregating your data to retain one row per day. This
is complicated by the fact that your days end at 2 AM instead of
midnight.
The usual trick to calculate a day from a datetime uses DATEADD and
DATEDIFF to find the number of days since a chosen base date, and add
that number of days to that base date, as follows:
SELECT DATEADD(day,
DATEDIFF(day, '20070101', CURRENT_TIMESTAMP),
'20070101');
In this case, we need the first two hours of the day to be part of the
previous day, so we subtract a little over two hours from the datetime
first, before trimming off the time:
SELECT DATEADD(day,
DATEDIFF(day,
'20070101',
DATEADD(minute, -121, CURRENT_TIMESTAMP)),
'20070101');
The final part, actually aggregating, is relatively easy - at least for
the MAX(high) and MIN(low). But finding the closing value (and the
opening value? didn't you forget to include that in the description?) is
a bit harder. Fortunately, the new ranking functions introduced in SQL
Server 2005 can help out. Just dish out row numbers to all rows within
each day; the first will have the opening quote and the last will have
the closing quote.
Putting this all together, I think you need something such as this:
WITH
DateTimeCombined AS
(SELECT Date + Time + 2 AS DatePlusTime,
Open, High, Low, Close
FROM ImportedTable),
DayAdded AS
(SELECT DATEADD(day,
DATEDIFF(day,
'20070101',
DATEADD(minute, -121, DatePlusTime)),
'20070101') AS Day,
DatePlusTime, Open, High, Low, Close
FROM DateTimeCombined),
Ranked AS
(SELECT Day, Open, High, Low, Close,
ROW_NUMBER() OVER (PARTITION BY Day
ORDER BY DatePlusTime) AS rn
FROM DayAdded)
SELECT Day,
MAX(CASE WHEN rn = 1 THEN Open END) AS Open,
MAX(High) AS High,
MIN(Low) AS Low,
MAX(CASE WHEN rn = COUNT(*) THEN Close END) AS Close
FROM Ranked
GROUP BY Day;
If you really don't need the open quote, change ORDER BY DatePlusTime to
ORDER BY DatePlusTime DESC, and change the test of rn = COUNT(*) to a
test for rn = 1 in the part where the close quote is found.
(Untested. See www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Ok, so I have not tried either of the suggested ways yet, but I have a
question regarding the date and time issue. I am not sure I understand the
issue of losing 2 days in the import process. I get it in theory due to the
date format not matching the time format and can visually see the difference
in the two base or starting formats. Now without checking line for line wha
t
you are telling me is that when I import say from my 2000 sheet that even
though I still come out with 12350ish rows I will lose dates if I use:
Update 'Moron_At_SQL'
SET DATE = DATE + CONVERT(CHAR(8),TIME,108)
Now in my mind that takes my TIME column and replaces the 12:00:00AM in DATE
column with something like 02:30 and so on. Now as for the [OPEN] each
day,
that is actually going to be defined by the HIGH and LOW of the 02:30 rows.
<--Was hoping to figure that one out on my own based on finding the [CL
OSE]
each day.
At this point I have roughly 8 years worth of data on lets say 15 symbols.
So thats approx 100,000 rows per symbol on the 30 minute time frame which is
one of the reasons I need to get away from doing everything in excel. Ok of
f
to try out these options from Hugo now, many thanks for the help so far.
Rob
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200711/1|||Alright, I am back with results for the two suggested methods. Suggestion 1
didnt quite work out so hot however, #2 worked pretty well. This is the
result of #2 with a slight change as shown.
WITH
DateTimeCombined AS
(SELECT Date + Time +2 AS DatePlusTime,
[Open], High, Low, [Close] FROM dbo.test),
DayAdded AS
(SELECT DATEADD(day,
DATEDIFF(day,
'20070101',
DATEADD(minute,-121, DatePlusTime)),
'20070101') AS Date,
DatePlusTime, [Open], High, Low, [Close]
FROM DateTimeCombined),
Ranked AS
(SELECT Date, [Open], High, Low, [Close],
ROW_NUMBER() OVER (PARTITION BY Date
ORDER BY DatePlusTime) AS rn,
COUNT(*) OVER (PARTITION BY Date) AS cn
FROM DayAdded)
SELECT Date,
MAX(CASE WHEN rn = 1 THEN HIGH END) AS OpeningRngHigh,
MAX(CASE WHEN rn = 1 THEN LOW END) AS OpeningRngLow,
MAX(High) AS High,
MIN(Low) AS Low,
MAX(CASE WHEN rn = cn THEN [Close] END) AS [Close]
FROM Ranked
GROUP BY Date
Now that gives me the following
Date OpeningRngHigh OpeningRngLow High Low Close
2000-01-03 00:00:00.000 1.6213 1.6199 1.64 1.615 1.633
2000-01-04 00:00:00.000 1.635 1.631 1.6415 1.631 1.6405
2000-01-05 00:00:00.000 1.6417 1.6387 1.6461 1.6376 1.6427
2000-01-06 00:00:00.000 1.6441 1.6417 1.6511 1.6417 1.646
2000-01-07 00:00:00.000 1.6462 1.6439 1.6499 1.636 1.6394
2000-01-09 00:00:00.000 1.639 1.6387 1.6396 1.6352 1.6357
2000-01-10 00:00:00.000 1.6371 1.6351 1.6401 1.6332 1.6388
2000-01-11 00:00:00.000 1.64 1.6382 1.6504 1.6382 1.6485
2000-01-12 00:00:00.000 1.6509 1.6478 1.6509 1.644 1.6465
Now the thing is its picking up the new date on Sundays and creating an extr
a
day for each. It should look like this
DATE OR HIGH OR LOW HIGH LOW CLOSE
2000.01.03 1.6213 1.6199 1.64 1.615 1.633
2000.01.04 1.635 1.631 1.6415 1.631 1.6405
2000.01.05 1.6417 1.6387 1.6461 1.6376 1.6427
2000.01.06 1.6441 1.6417 1.6511 1.6417 1.646
2000.01.07 1.6462 1.6439 1.6499 1.6352 1.6357
2000.01.10 1.6371 1.6351 1.6401 1.6332 1.6388
2000.01.11 1.64 1.6382 1.6504 1.6382 1.6485
2000.01.12 1.6509 1.6478 1.6509 1.644 1.6465
Even though there is data on Sunday, it is still considered part of the
previous Fridays 'day' until the new 02:30 row starts on Monday am. Here is
a sample of Friday to Monday data if that helps.
Date Time Open High Low Close
2000.01.07 2:30 1.6454 1.6462 1.6439 1.6453 <--Start of
Friday
2000.01.07 3:00 1.6454 1.6479 1.6447 1.645
2000.01.07 3:30 1.6453 1.6472 1.6452 1.6458
2000.01.07 4:00 1.6458 1.6499 1.6453 1.6484
2000.01.07 4:30 1.6483 1.6486 1.6461 1.6479
2000.01.07 5:00 1.648 1.6482 1.646 1.6465
2000.01.07 5:30 1.6462 1.6467 1.6442 1.6453
2000.01.07 6:00 1.6452 1.6457 1.6441 1.6449
2000.01.07 6:30 1.6448 1.6453 1.644 1.645
2000.01.07 7:00 1.6449 1.6463 1.6444 1.6458
2000.01.07 7:30 1.6458 1.6465 1.6432 1.644
2000.01.07 8:00 1.6438 1.6453 1.6422 1.6451
2000.01.07 8:30 1.6455 1.6478 1.6418 1.6427
2000.01.07 9:00 1.6428 1.6442 1.6413 1.6418
2000.01.07 9:30 1.6419 1.642 1.6401 1.6408
2000.01.07 10:00 1.6402 1.6405 1.6374 1.6387
2000.01.07 10:30 1.6386 1.6401 1.638 1.6384
2000.01.07 11:00 1.6384 1.6388 1.6368 1.6385
2000.01.07 11:30 1.6382 1.6395 1.6379 1.6385
2000.01.07 12:00 1.6376 1.6387 1.637 1.637
2000.01.07 12:30 1.6372 1.6381 1.6362 1.6377
2000.01.07 13:00 1.6376 1.638 1.6369 1.6376
2000.01.07 13:30 1.6373 1.6378 1.636 1.6369
2000.01.07 14:00 1.637 1.6377 1.6366 1.6371
2000.01.07 14:30 1.6379 1.6383 1.6363 1.6383
2000.01.07 15:00 1.6389 1.6389 1.6373 1.6379
2000.01.07 15:30 1.6381 1.6391 1.6374 1.6387
2000.01.07 16:00 1.6387 1.6403 1.6378 1.6403
2000.01.07 16:30 1.6397 1.6397 1.6385 1.6392
2000.01.07 17:00 1.6393 1.6398 1.6393 1.6394
2000.01.09 18:00 1.6387 1.639 1.6387 1.639
2000.01.09 18:30 1.6388 1.6392 1.6383 1.6384
2000.01.09 19:00 1.6384 1.6392 1.6379 1.6389
2000.01.09 19:30 1.6392 1.6394 1.6381 1.6389
2000.01.09 20:00 1.6388 1.6396 1.6384 1.639
2000.01.09 20:30 1.639 1.6394 1.6382 1.6393
2000.01.09 21:00 1.6394 1.6394 1.6382 1.6384
2000.01.09 21:30 1.6385 1.639 1.6371 1.638
2000.01.09 22:00 1.6385 1.6387 1.6378 1.6379
2000.01.09 22:30 1.6378 1.6387 1.6375 1.6379
2000.01.09 23:00 1.638 1.6383 1.6378 1.6379
2000.01.09 23:30 1.6386 1.6393 1.6375 1.6376
2000.01.10 0:00 1.638 1.6388 1.6374 1.6375
2000.01.10 0:30 1.6376 1.6392 1.6373 1.6379
2000.01.10 1:00 1.638 1.6387 1.6371 1.6379
2000.01.10 1:30 1.6377 1.6383 1.6354 1.6356
2000.01.10 2:00 1.6358 1.6372 1.6352 1.6357 <--End Of Friday
2000.01.10 2:30 1.6357 1.6371 1.6351 1.6358 <--Start of Monday
And yes, I now see the whole adding the date stuff for the 2 days I think.
Without that DATEADD it just starts to label all the results starting at
1/1/2000 instead of 1/3/2000.
Thanks
Rob
Message posted via http://www.droptable.com|||On Fri, 09 Nov 2007 12:23:08 GMT, RBS via droptable.com wrote:
(snip)
>Now the thing is its picking up the new date on Sundays and creating an ext
ra
>day for each.
(snip)
>Even though there is data on Sunday, it is still considered part of the
>previous Fridays 'day' until the new 02:30 row starts on Monday am. Here i
s
>a sample of Friday to Monday data if that helps.
(snip)
>2000.01.07 16:30 1.6397 1.6397 1.6385 1.6392
>2000.01.07 17:00 1.6393 1.6398 1.6393 1.6394
>2000.01.09 18:00 1.6387 1.639 1.6387 1.639
>2000.01.09 18:30 1.6388 1.6392 1.6383 1.6384
>2000.01.09 19:00 1.6384 1.6392 1.6379 1.6389
>2000.01.09 19:30 1.6392 1.6394 1.6381 1.6389
>2000.01.09 20:00 1.6388 1.6396 1.6384 1.639
>2000.01.09 20:30 1.639 1.6394 1.6382 1.6393
>2000.01.09 21:00 1.6394 1.6394 1.6382 1.6384
>2000.01.09 21:30 1.6385 1.639 1.6371 1.638
>2000.01.09 22:00 1.6385 1.6387 1.6378 1.6379
>2000.01.09 22:30 1.6378 1.6387 1.6375 1.6379
>2000.01.09 23:00 1.638 1.6383 1.6378 1.6379
>2000.01.09 23:30 1.6386 1.6393 1.6375 1.6376
>2000.01.10 0:00 1.638 1.6388 1.6374 1.6375
>2000.01.10 0:30 1.6376 1.6392 1.6373 1.6379
>2000.01.10 1:00 1.638 1.6387 1.6371 1.6379
>2000.01.10 1:30 1.6377 1.6383 1.6354 1.6356
>2000.01.10 2:00 1.6358 1.6372 1.6352 1.6357 <--End Of Friday
>2000.01.10 2:30 1.6357 1.6371 1.6351 1.6358 <--Start of Monday
Hi Rob,
Based on this sample data, I'd say that you need to subtract two extra
days for either all data collected on sunday or on monday up until 2:00
AM, or for all data that has its calculated date on sunday.
I think the former version is easiest to implement. Just change the
first of the CTE expressions to:
WITH
DateTimeCombined AS
(SELECT Date + Time +2 AS DatePlusTime,
[Open], High, Low, [Close] FROM dbo.test),
DayAdded AS
(SELECT DATEADD(day,
DATEDIFF(day,
'20070101',
DATEADD(minute,-121, DatePlusTime))
- CASE WHEN DATENAME(dw,DatePlusTime) = 'Sunday'
OR (DATENAME(dw,DatePlusTime) = 'Monday'
AND DATEPART(hh,DatePlusTime) <= 2)
THEN 2
ELSE 0
END,
'20070101') AS Date,
DatePlusTime, [Open], High, Low, [Close]
FROM DateTimeCombined),
The rest of your query can remain unchanged.
Note that I did not test the above.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Hugo Kornelis wrote:
>(snip)
>Hi Rob,
>Based on this sample data, I'd say that you need to subtract two extra
>days for either all data collected on sunday or on monday up until 2:00
>AM, or for all data that has its calculated date on sunday.
>I think the former version is easiest to implement. Just change the
>first of the CTE expressions to:
>WITH
> DateTimeCombined AS
> (SELECT Date + Time +2 AS DatePlusTime,
> [Open], High, Low, [Close] FROM dbo.test),
> DayAdded AS
> (SELECT DATEADD(day,
> DATEDIFF(day,
> '20070101',
> DATEADD(minute,-121, DatePlusTime))
> - CASE WHEN DATENAME(dw,DatePlusTime) = 'Sunday'
> OR (DATENAME(dw,DatePlusTime) = 'Monday'
> AND DATEPART(hh,DatePlusTime) <= 2)
> THEN 2
> ELSE 0
> END,
> '20070101') AS Date,
> DatePlusTime, [Open], High, Low, [Close]
> FROM DateTimeCombined),
>The rest of your query can remain unchanged.
>Note that I did not test the above.
>
Alright, here is what I have discovered. As the above change stands, it
still creates days on Sunday and some other goofy stuff like with the first
of each year. When it comes to the Sunday data, it starts using what I would
call 'off' times to calculate the end of the day[CLose]. So I have alte
red
it a bit and now have this
WITH
DateTimeCombined AS
(SELECT Date + Time + 2 AS DatePlusTime,
[Open], High, Low, [Close] FROM dbo.GU_Intraday),
DayAdded AS
(SELECT DATEADD(day,
DATEDIFF(day,
'20070101',
DATEADD(minute,-121, DatePlusTime))
- CASE WHEN DATENAME(dw,DatePlusTime) = 'Sunday'
OR (DATENAME(dw,DatePlusTime) = 'Monday'
AND DATEPART(hh,DatePlusTime)<=2
AND DATEPART(mi,DatePlusTime)<30)
OR DATEPART(dy,DatePlusTime) =1
THEN 2
ELSE 0
END,
'20070101') AS Date,
DatePlusTime, [Open], High, Low, [Close]
FROM DateTimeCombined),
Now with that I still get Sundays being created but the Friday [Close] i
s
correct. So what I have now is this as sp_intraday_daily and once I run tha
t
to insert the results to a new table, I run
DELETE FROM dbo.GU_Daily
where DATENAME(dw,Date) ='Sunday'
DELETE FROM dbo.GU_Daily
WHERE DATEPART(dy, Date) = 1
With those 2 things, I get what I will call acceptable results. If there is
a better way to do this I am open to ideas, if not then I am cool with what
I
have. Thanks for everything so far.
Rob
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200711/1
No comments:
Post a Comment