I have posted this question and received an answer, however I now have a new problem. Here;s the original question:
I have a table that has trading transactions and contains a timestamp field and a ASK price each time the price changes (tick data).
The timestamp has the format:1/1/2009 10:42:00.000000 PM
The records are sequential but do not have a consistent gap. some are the same time, some are 2 seconds apart, some 10 seconds apart.
I wish to load the beginning ask price and the ending ask price for each minute into another table (MIN_Table)
The answer that worked great:
SELECT TO_CHAR (ticktime, 'dd/mm/yyyy hh24:mi'),
MAX (ask) KEEP (DENSE_RANK FIRST ORDER BY id) Open,
MAX (ask) KEEP (DENSE_RANK LAST ORDER BY id) Close
GROUP BY TO_CHAR (ticktime, 'dd/mm/yyyy hh24:mi');
I place this into an INSERT INTO statement and this works for a table containing 1 minute transactions.
I can also make this work for a 1 hour table (thanks to help here) by changing the TO_CHAR to hh24:hh24
But I need to be able to generate data to fill the following individual tables:
1 min (got it)
1 hour(got it)
Does anyone have a solution for the other time frame tables that I can use in an insert statement?