Alternative SQL
Frank Zhou, January 17, 2008 - 1:26 pm UTC
Other approaches
Rob van Wijk, January 17, 2008 - 4:58 pm UTC
multi date rows to one rows
maher shaqalaih, January 19, 2008 - 12:55 pm UTC
hi tom,
i have a table like this which contain holiday dates ,some of these holidays can be continous, and other not like following:
CREATE TABLE HOLIDAY_DAYS(THEDATE DATE);
INSERT INTO HOLIDAY_DAYS ( THEDATE ) VALUES (
TO_Date( '01/01/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO HOLIDAY_DAYS ( THEDATE ) VALUES (
TO_Date( '01/02/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO HOLIDAY_DAYS ( THEDATE ) VALUES (
TO_Date( '01/03/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO HOLIDAY_DAYS ( THEDATE ) VALUES (
TO_Date( '01/04/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO HOLIDAY_DAYS ( THEDATE ) VALUES (
TO_Date( '01/10/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO HOLIDAY_DAYS ( THEDATE ) VALUES (
TO_Date( '01/01/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO HOLIDAY_DAYS ( THEDATE ) VALUES (
TO_Date( '01/10/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO HOLIDAY_DAYS ( THEDATE ) VALUES (
TO_Date( '01/11/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO HOLIDAY_DAYS ( THEDATE ) VALUES (
TO_Date( '01/12/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
COMMIT;
select * from HOLIDAY_DAYS;
THEDATE
--------
01/01/2007
02/01/2007
03/01/2007
04/01/2007
10/01/2007
01/01/2008
10/01/2008
11/01/2008
12/01/2008
i want to make out put like this:
THEDATE END_DATE
--------- ----------
01/01/2007 04/01/2007
10/01/2007 10/01/2007
01/01/2008 01/01/2008
10/01/2008 12/01/2008
January 19, 2008 - 11:01 pm UTC
Your output appears to be made up.
Is there some logic behind it, and if so, would you be so kind as to share it with us.
multi date rows to one rows
maher shaqalaih, January 20, 2008 - 2:11 am UTC
Hi tom,
Our system stores the holidays as one day each time and this is why the continues holiday appears multi row.
Now we want to know the whole holiday from the start to the end. This is because if an employee want to take his holiday from the day before the holiday until the day after the holiday and he wants to insert his holiday as a singular two days, I want to prevent him and insert it as a complete one.
e.g.
select * from HOLIDAY_DAYS;
THEDATE
--------
02/01/2007
03/01/2007
04/01/2007
10/01/2007
01/01/2008
10/01/2008
11/01/2008
12/01/2008
He wants to take 01/01/2007 and 05/01/2007 as two singular holiday days and the system should record his holiday as 6 days starting from 01/01/2007 until 05/01/2007.
January 20, 2008 - 7:58 am UTC
sorry, this still does not compute.
Look - start over, your dates:
INSERT INTO HOLIDAY_DAYS ( THEDATE ) VALUES (
TO_Date( '01/12/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
COMMIT;
they are MONTH-DAY-YEAR.
You are talking like Jan-01-2007 to May-01-2007 are six days apart.
What "system".
"the continues (continuous) holiday appears multi row" - not a clue what that means
Your description is clear as mud.
Dates
a reader, January 20, 2008 - 9:57 am UTC
Hi Tom
His dates are input'ed as MM/DD/YYYY but the print output format is DD/MM/YYYY, which makes his question valid again :-)
The holidays in makes perfect sense
January 20, 2008 - 12:11 pm UTC
hey, if I say "i don't get it, then I don't get it"
so, no, the question is ill phrased, incomplete, not clear.
He can either
a) clean it up entirely start over, phrase the question clearly in terms everyone can understand and have an example that is CRYSTAL CLEAR as to what we are looking at
b) forget about having us look at it seriously.
His call.
A bid for a "solution"
Mette Stephansen, January 20, 2008 - 1:18 pm UTC
This is my bid for a solution (not very good at analytics, so please help improve - but it was fun trying though):
with days as(
select * from
(select a.* ,
case when lead(thedate, 1, thedate) OVER (ORDER BY thedate) = thedate+1 then null else '*' end slut_d ,
case when lag(thedate, 1, thedate) OVER (ORDER BY thedate) = thedate-1 then null else '*' end start_d
from holiday_days a)
where start_d = '*' or slut_d = '*')
select thedate, thedate
from days
where slut_d = '*' and start_d = '*'
union
select
case when slut_d = '*' then lag(thedate,1,thedate) over (order by thedate) else thedate end start_date ,
case when start_d = '*' then lead(thedate,1,thedate) over (order by thedate) else thedate end end_date
from days
where slut_d is null or start_d is null;
- first filter the start & end of interval records out
- then find the dates for start/stop
best regards
multi date rows to one rows
maher shaqalaih, January 21, 2008 - 1:56 am UTC
hi Mette Stephansen ,areader
thanks very much for your answer that was exactly what i want.
thedate end_date
----------- ---------------
01/01/2007 04/01/2007
10/01/2007 10/01/2007
01/01/2008 01/01/2008
10/01/2008 12/01/2008
thanks very very much for Mette Stephansen .
Method using the MODEL Clause
David, January 30, 2008 - 2:58 am UTC
The MODEL clause can be very effective for solving this type of problem at Oracle 10g. Using your data set, then:
SELECT fdate, tdate
FROM dt
MODEL
PARTITION BY (tdate)
DIMENSION BY (0 AS KEYY)
MEASURES (fdate,
tdate AS MAXDT)
RULES ITERATE (100) UNTIL (fdate[ITERATION_NUMBER] = maxdt[0])
(
fdate[ITERATION_NUMBER] = fdate[0] + ITERATION_NUMBER
)
ORDER BY fdate;
FDATE TDATE
--------- ---------
01-JAN-08 05-JAN-08
02-JAN-08 05-JAN-08
03-JAN-08 05-JAN-08
04-JAN-08 05-JAN-08
05-JAN-08 05-JAN-08
10-JAN-08 15-JAN-08
11-JAN-08 15-JAN-08
12-JAN-08 15-JAN-08
13-JAN-08 15-JAN-08
14-JAN-08 15-JAN-08
15-JAN-08 15-JAN-08
02-FEB-08 04-FEB-08
03-FEB-08 04-FEB-08
04-FEB-08 04-FEB-08
14 rows selected.
Ensure that the number of iterations is sufficiently large so that the UNTIL clause always terminates each iteration.
Regards
David