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 from
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 query
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 in
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/Forums.aspx/sql-server/200711/1
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 what
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 [CLOSE]
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 off
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/Forums.aspx/sql-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.0001.6213 1.61991.64 1.615 1.633
2000-01-04 00:00:00.0001.6351.6311.64151.6311.6405
2000-01-05 00:00:00.0001.64171.63871.64611.63761.6427
2000-01-06 00:00:00.0001.64411.64171.65111.64171.646
2000-01-07 00:00:00.0001.64621.64391.64991.6361.6394
2000-01-09 00:00:00.0001.6391.63871.63961.63521.6357
2000-01-10 00:00:00.0001.63711.63511.64011.63321.6388
2000-01-11 00:00:00.0001.641.63821.65041.63821.6485
2000-01-12 00:00:00.0001.65091.64781.65091.6441.6465
Now the thing is its picking up the new date on Sundays and creating an extra
day for each. It should look like this
DATEOR HIGHOR LOWHIGHLOWCLOSE
2000.01.031.62131.61991.641.6151.633
2000.01.041.6351.6311.64151.6311.6405
2000.01.051.64171.63871.64611.63761.6427
2000.01.061.64411.64171.65111.64171.646
2000.01.071.64621.64391.64991.63521.6357
2000.01.101.63711.63511.64011.63321.6388
2000.01.111.641.63821.65041.63821.6485
2000.01.121.65091.64781.65091.6441.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.072:301.64541.64621.64391.6453 <--Start of
Friday
2000.01.073:001.64541.64791.64471.645
2000.01.073:301.64531.64721.64521.6458
2000.01.074:001.64581.64991.64531.6484
2000.01.074:301.64831.64861.64611.6479
2000.01.075:001.6481.64821.6461.6465
2000.01.075:301.64621.64671.64421.6453
2000.01.076:001.64521.64571.64411.6449
2000.01.076:301.64481.64531.6441.645
2000.01.077:001.64491.64631.64441.6458
2000.01.077:301.64581.64651.64321.644
2000.01.078:001.64381.64531.64221.6451
2000.01.078:301.64551.64781.64181.6427
2000.01.079:001.64281.64421.64131.6418
2000.01.079:301.64191.6421.64011.6408
2000.01.0710:001.64021.64051.63741.6387
2000.01.0710:301.63861.64011.6381.6384
2000.01.0711:001.63841.63881.63681.6385
2000.01.0711:301.63821.63951.63791.6385
2000.01.0712:001.63761.63871.6371.637
2000.01.0712:301.63721.63811.63621.6377
2000.01.0713:001.63761.6381.63691.6376
2000.01.0713:301.63731.63781.6361.6369
2000.01.0714:001.6371.63771.63661.6371
2000.01.0714:301.63791.63831.63631.6383
2000.01.0715:001.63891.63891.63731.6379
2000.01.0715:301.63811.63911.63741.6387
2000.01.0716:001.63871.64031.63781.6403
2000.01.0716:301.63971.63971.63851.6392
2000.01.0717:001.63931.63981.63931.6394
2000.01.0918:001.63871.6391.63871.639
2000.01.0918:301.63881.63921.63831.6384
2000.01.0919:001.63841.63921.63791.6389
2000.01.0919:301.63921.63941.63811.6389
2000.01.0920:001.63881.63961.63841.639
2000.01.0920:301.6391.63941.63821.6393
2000.01.0921:001.63941.63941.63821.6384
2000.01.0921:301.63851.6391.63711.638
2000.01.0922:001.63851.63871.63781.6379
2000.01.0922:301.63781.63871.63751.6379
2000.01.0923:001.6381.63831.63781.6379
2000.01.0923:301.63861.63931.63751.6376
2000.01.100:001.6381.63881.63741.6375
2000.01.100:301.63761.63921.63731.6379
2000.01.101:001.6381.63871.63711.6379
2000.01.101:301.63771.63831.63541.6356
2000.01.102:001.63581.63721.63521.6357 <--End Of Friday
2000.01.102:301.63571.63711.63511.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
|||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 altered
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] is
correct. So what I have now is this as sp_intraday_daily and once I run that
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/Forums.aspx/sql-server/200711/1
|||PeterF
It can be a job that running this command somehow?
Check it out with people who have permission to do that
"PeterF" <peterfairchild@.bluewin.ch> wrote in message
news:%2300bFPYaIHA.4696@.TK2MSFTNGP05.phx.gbl...
> I'm new to the group.
> I have a client who came in on Monday morning and tried to connect to his
> database. Over the weekend, it had become single user. I got him going
> (back to multi user) but I am trying to figure out how it happened.
> Thanks for your help,
> Kind regards
> Peter
No comments:
Post a Comment