Analytic functions using window date range
602422Apr 3 2008 — edited Apr 7 2008Here are my requirements:
For each FLT# Get the MIN and MAX CRSG_DT within 2½ hours period into the same bucket for each day
I am using Oracle 10gR2
CREATE TABLE TST_ANAL
(
FLT_NBR VARCHAR2(10),
CRSG_DT DATE ,
TNBR VARCHAR2(14)
)
INSERT INTO TST_ANAL ( FLT_NBR, CRSG_DT, TNBR) VALUES (
'0002947', TO_Date( '03/01/2007 07:31:57 PM', 'MM/DD/YYYY HH:MI:SS AM'), 'D2007070013438');
INSERT INTO TST_ANAL ( FLT_NBR, CRSG_DT, TNBR) VALUES (
'0002947', TO_Date( '03/01/2007 08:31:02 PM', 'MM/DD/YYYY HH:MI:SS AM'), 'D2007070013446');
INSERT INTO TST_ANAL ( FLT_NBR, CRSG_DT, TNBR) VALUES (
'0002947', TO_Date( '03/01/2007 05:30:34 PM', 'MM/DD/YYYY HH:MI:SS AM'), 'D2007070013440');
INSERT INTO TST_ANAL ( FLT_NBR, CRSG_DT, TNBR) VALUES (
'0002947', TO_Date( '03/01/2007 05:32:07 PM', 'MM/DD/YYYY HH:MI:SS AM'), 'D2007070013427');
INSERT INTO TST_ANAL ( FLT_NBR, CRSG_DT, TNBR) VALUES (
'0002947', TO_Date( '03/01/2007 05:32:40 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'D2007070013433');
INSERT INTO TST_ANAL ( FLT_NBR, CRSG_DT, TNBR) VALUES (
'0002947', TO_Date( '03/01/2007 05:35:40 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'D2007070013429');
INSERT INTO TST_ANAL ( FLT_NBR, CRSG_DT, TNBR) VALUES (
'0002947', TO_Date( '03/01/2007 07:32:45 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'D2007070013434');
INSERT INTO TST_ANAL ( FLT_NBR, CRSG_DT, TNBR) VALUES (
'0002947', TO_Date( '03/01/2007 07:32:50 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'D200707001323');
COMMIT;
The desired output:
FLT_NBR CRSG_DT MIN MAX Bucket
2947 3/1/2007 5:32 3/1/2007 5:32 3/1/2007 7:32 1
2947 3/1/2007 5:35 3/1/2007 5:32 3/1/2007 7:32 1
2947 3/1/2007 7:32 3/1/2007 5:32 3/1/2007 7:32 1
2947 3/1/2007 7:32 3/1/2007 5:32 3/1/2007 7:32 1
2947 3/1/2007 17:30 3/1/2007 17:30 3/1/2007 19:31 2
2947 3/1/2007 17:32 3/1/2007 17:30 3/1/2007 19:31 2
2947 3/1/2007 19:31 3/1/2007 17:30 3/1/2007 19:31 2
2947 3/1/2007 20:31 3/1/2007 20:31 3/1/2007 20:31 3
I am sure an Analytic query is the optimal solution. I need help creating the query. So far, I have attempted to use the min() and then both First_value() over partition clause but I do not know how to specify the window clause using a logical date range to calculate the 2 1/2 hours.
A lot of documentation states that I can use an expression for the range between but I cannot get one even compile
Need Help or documentation that provide some assistance
Thanks