If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
How to show time for day's stock market high/low
Original code would hang but I finally got this to work:
SELECT [Date], [Time], [High] FROM [TestTable] WHERE [High] = (SELECT Max([TestTable].High) FROM [TestTable] AS T WHERE T.[Date]=[TestTable].Date); Some sample raw data (Date,Time,Open,High,Low,Close,Volume): 20090102;16:03:00;3694;3706;3694;3702;44 20090102;16:04:00;3700;3702;3697;3700;85 20090102;16:05:00;3699;3702;3698;3700;22 20090102;16:06:00;3699;3699;3697;3699;26 20090102;16:07:00;3700;3702;3700;3702;10 20090102;16:08:00;3704;3708;3704;3708;14 20090102;16:09:00;3707;3715;3707;3710;48 20090102;16:10:00;3709;3715;3705;3708;86 20090102;16:11:00;3706;3706;3696;3703;207 "John W. Vinson" wrote: On Fri, 9 Apr 2010 22:17:01 -0700, Seano wrote: Many thanks, John. I managed to get this working, and I feel closer to the answer, but alas it returns the Max(High) for every minute in the database, not the highest High for the day. I'm after the highest High in every 24-hour period and the Time (minute) that occurred. Apologies for not explaining it properly (see my reply to Karl Dewey for further clarification). Then you either didn't follow John Spencer's advice, or your table is not as you described it: his subqueries will in fact retrieve the high and the low for the day. Please post the exact SQL of the query that you are using, and perhaps a few rows of example data. -- John W. Vinson [MVP] . |
#12
|
|||
|
|||
How to show time for day's stock market high/low
On Sat, 10 Apr 2010 03:23:01 -0700, Seano
wrote: Original code would hang but I finally got this to work: SELECT [Date], [Time], [High] FROM [TestTable] WHERE [High] = (SELECT Max([TestTable].High) FROM [TestTable] AS T WHERE T.[Date]=[TestTable].Date); An alternative would use a Top Values subquery: SELECT [Date], [High], (SELECT TOP 1 X.[Time] FROM tablename AS X WHERE X.[Date] = tablename.[Date] ORDER BY X.[High], X.[Time] DESC) AS TimeOfHigh, [Low], (SELECT TOP 1 X.[Time] FROM tablename AS X WHERE X.[Date] = tablename.[Date] ORDER BY X.[Low] DESC, X.[Time] DESC); -- John W. Vinson [MVP] |
#13
|
|||
|
|||
How to show time for day's stock market high/low
Your SQL is retrieving the high for each record in TestTable.
HOW many records do you have? Do you have an index on the Date field? The query will take a long time with many records because it is running the query in the where clause ONCE for every record in the table. If you have a lot of records you may have to use two queries to get the results you want. If you didn't have a field name Date that requires square brackets to be used, you could do this all with a subquery in the from clause. First Query (Save this as qMaxHigh) SELECT [TestTable].[Date] , Max([TestTable].[High]) as TheHigh FROM [TestTable] GROUP BY [TestTable].[Date] Now you can use that saved query and your original table. SELECT [TestTable].[Date] , [TestTable].[Time] , [TestTable].[High] FROM [TestTable] INNER JOIN qMaxHigh ON [TestTable].[Date] = qMaxHigh.[Date] AND [TestTable].[High] = qMaxHigh.TheHigh John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Seano wrote: Original code would hang but I finally got this to work: SELECT [Date], [Time], [High] FROM [TestTable] WHERE [High] = (SELECT Max([TestTable].High) FROM [TestTable] AS T WHERE T.[Date]=[TestTable].Date); Some sample raw data (Date,Time,Open,High,Low,Close,Volume): 20090102;16:03:00;3694;3706;3694;3702;44 20090102;16:04:00;3700;3702;3697;3700;85 20090102;16:05:00;3699;3702;3698;3700;22 20090102;16:06:00;3699;3699;3697;3699;26 20090102;16:07:00;3700;3702;3700;3702;10 20090102;16:08:00;3704;3708;3704;3708;14 20090102;16:09:00;3707;3715;3707;3710;48 20090102;16:10:00;3709;3715;3705;3708;86 20090102;16:11:00;3706;3706;3696;3703;207 "John W. Vinson" wrote: On Fri, 9 Apr 2010 22:17:01 -0700, Seano wrote: Many thanks, John. I managed to get this working, and I feel closer to the answer, but alas it returns the Max(High) for every minute in the database, not the highest High for the day. I'm after the highest High in every 24-hour period and the Time (minute) that occurred. Apologies for not explaining it properly (see my reply to Karl Dewey for further clarification). Then you either didn't follow John Spencer's advice, or your table is not as you described it: his subqueries will in fact retrieve the high and the low for the day. Please post the exact SQL of the query that you are using, and perhaps a few rows of example data. -- John W. Vinson [MVP] . |
#14
|
|||
|
|||
How to show time for day's stock market high/low
You guys are brilliant! Your code worked a treat on 700,000 lines of my
database in under 10 seconds. Many thanks, and also to John W V, for your patience and efforts. "John Spencer" wrote: Your SQL is retrieving the high for each record in TestTable. HOW many records do you have? Do you have an index on the Date field? The query will take a long time with many records because it is running the query in the where clause ONCE for every record in the table. If you have a lot of records you may have to use two queries to get the results you want. If you didn't have a field name Date that requires square brackets to be used, you could do this all with a subquery in the from clause. First Query (Save this as qMaxHigh) SELECT [TestTable].[Date] , Max([TestTable].[High]) as TheHigh FROM [TestTable] GROUP BY [TestTable].[Date] Now you can use that saved query and your original table. SELECT [TestTable].[Date] , [TestTable].[Time] , [TestTable].[High] FROM [TestTable] INNER JOIN qMaxHigh ON [TestTable].[Date] = qMaxHigh.[Date] AND [TestTable].[High] = qMaxHigh.TheHigh John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Seano wrote: Original code would hang but I finally got this to work: SELECT [Date], [Time], [High] FROM [TestTable] WHERE [High] = (SELECT Max([TestTable].High) FROM [TestTable] AS T WHERE T.[Date]=[TestTable].Date); Some sample raw data (Date,Time,Open,High,Low,Close,Volume): 20090102;16:03:00;3694;3706;3694;3702;44 20090102;16:04:00;3700;3702;3697;3700;85 20090102;16:05:00;3699;3702;3698;3700;22 20090102;16:06:00;3699;3699;3697;3699;26 20090102;16:07:00;3700;3702;3700;3702;10 20090102;16:08:00;3704;3708;3704;3708;14 20090102;16:09:00;3707;3715;3707;3710;48 20090102;16:10:00;3709;3715;3705;3708;86 20090102;16:11:00;3706;3706;3696;3703;207 "John W. Vinson" wrote: On Fri, 9 Apr 2010 22:17:01 -0700, Seano wrote: Many thanks, John. I managed to get this working, and I feel closer to the answer, but alas it returns the Max(High) for every minute in the database, not the highest High for the day. I'm after the highest High in every 24-hour period and the Time (minute) that occurred. Apologies for not explaining it properly (see my reply to Karl Dewey for further clarification). Then you either didn't follow John Spencer's advice, or your table is not as you described it: his subqueries will in fact retrieve the high and the low for the day. Please post the exact SQL of the query that you are using, and perhaps a few rows of example data. -- John W. Vinson [MVP] . . |
|
Thread Tools | |
Display Modes | |
|
|