Thanks
A reader, December 28, 2003 - 8:40 am UTC
Thanks Tom for answering the question . That was a very quick response.
Regards
SV
generating date list...
DK, June 03, 2005 - 4:23 pm UTC
Tom,
I have a table t.
create table t
(start_date date,
interval varchar2(5),
count number);
insert into t (start_date, interval, count)
values (to_date('01/01/2005','MM/DD/YYYY'),'MONTH',3);
insert into t (start_date, interval, count)
values (to_date('01/15/2005','MM/DD/YYYY'),'YEAR',2);
commit;
I want to write a query which will generate dates starting with start_date, at specified interval and count number of times.
So the output from the data in the table should be,
01/01/2005
02/01/2005
03/01/2005
01/15/2005
01/15/2006
first three dates are generated from the first row in t and the next two are generated by the second row in t. How would you go about doing it in oracle8i.
June 03, 2005 - 5:34 pm UTC
do you have a reasonable upper bound for count.
do you retrieve ALL of the rows from this table or will your application really just get "one of them" and need to explode that out?
Additional Info
DK, June 04, 2005 - 9:08 am UTC
Tom,
Reasonable upper bound for count = 60
Our application will pass a range for start_date or a specific date. That means we need to retrieve a subset of rows from the table.
Thank you
June 04, 2005 - 11:52 am UTC
ops$tkyte@ORA9IR2> with nums
2 as
3 (select level R
4 from dual
5 connect by level <= 60
6 )
7 select t.start_date,
8 add_months( start_date, decode(interval,'MONTH',1,'YEAR',12) * (r-1) ) dt,
9 t.interval,
10 t.count,
11 nums.r
12 from t, nums
13 where t.count >= nums.r
14 order by t.start_date
15 /
START_DAT DT INTER COUNT R
--------- --------- ----- ---------- ----------
01-JAN-05 01-JAN-05 MONTH 3 1
01-JAN-05 01-FEB-05 MONTH 3 2
01-JAN-05 01-MAR-05 MONTH 3 3
15-JAN-05 15-JAN-05 YEAR 2 1
15-JAN-05 15-JAN-06 YEAR 2 2
I might advise against using COUNT and INTERVAL as column names as one is a builtin function and the other a datatype.
solution
pavan, June 04, 2005 - 9:18 am UTC
i found this solution.. revert me if any better solution is found
Select Decode(Interval, 'DAY',start_date+indx, 'MONTH',Add_Months(start_date,indx),
to_char(start_date,'dd')||'/'||to_char(start_date,'mm')||'/'||to_char(to_number(to_char(start_date,'yyyy')+indx)))
From
(Select t1.x1 indx, t2.x2 rfrnc
From
(Select rownum x1 From user_objects) t1,
(select rownum x2, t.* from t) t2
Where t1.x1<=t2.COUNT) Main1,
(select rownum x3, t.* From t)main2
where main1.rfrnc = main2.x3
CONNECT BY - Oracle 8i
DK, June 06, 2005 - 8:57 am UTC
Tom,
select level R
from dual
connect by level <= 60
throws ORA-01436:CONNECT BY loop in user data. This is oracle8i database.
June 06, 2005 - 9:31 am UTC
yes, this works only in 9i and above. No version, I assume software written this century.
You'll replace the reference to dual with any table that as at least 60 rows and use
select rownum r from that_table where rownum <= 60
instead
Connect by "trick"
Martin, June 06, 2005 - 10:45 am UTC
[quote]yes, this works only in 9i and above[/quote]
9IR2 to be exact.
Additional Info
Mario Jozak, June 06, 2005 - 10:46 am UTC
Before I didn't know the existance of asktom.com I taught I know pretty much about hierarchy query, and while the time gone, I taught I know more and more. Now, the situation is very diffrent, while I'm reading asktom.com every day I recognize that I know less and less about not only hierarchy querys but everything
Thanks Tom!
You rule!
p.s. you're always blocked with questions (for couple of months), and I would like to ask some. Any tips? (this is kind of a recursion - asking question about asking question :)
June 06, 2005 - 11:18 am UTC
i can only take about 10 or so new questions a day at most.... it is very much "catch as catch can"
A reader, June 07, 2005 - 8:51 am UTC
Hi Tom,
I want all employees who have joined from last 5 days.
But when there are more than 101 records,below query
is not showing some of the employees who joined today
(i mean that i have added today). I want to always show
all the employees who have joined today and then other days records (chronological order should be from current date to backwards) . How can I do this?
SELECT ENAME,JOB
FROM EMP
WHERE HIREDATE>=5
AND ROWNUM<102;
Between..AND and >= always take first date as starting point.
In my context date2 should be starting point.
SELECT .. FROM TABLE WHERE HIREDATE BETWEEN DATE2 AND DATE1;
(DATE2=JUN 7TH 2005, DATE1 =JUN 2ND 2005)
Please help!
Thanks.
June 07, 2005 - 8:56 am UTC
select * from emp where hiredate = sysdate
union all
select * from
(select * from emp where hiredate <> sysdate and hiredate >= sysdate-5
order by hiredate desc)
where rownum <= "whatever"
order by hiredate desc
A reader, June 07, 2005 - 9:09 am UTC
Thank you for your quickest reply. But I have one question (for my understanding):
why do we need a subquery in the 2nd half of the query? How is it useful?
June 07, 2005 - 9:33 am UTC
you had this requirement:
I want to always show
all the employees who have joined today and then other days records
(chronological order should be from current date to backwards)
in order to get the top-n last hired people, we need to sort AND THEN select the top-n
A reader, June 07, 2005 - 9:13 am UTC
And also in the first half we checking if = 'somedate' and so why again say >= 'somedate' in 2nd half? I didn't understand, please explain.
June 07, 2005 - 9:37 am UTC
you said
a) I want everyone hired today. that is "where hiredate=sysdate"
b) and some of the people hired within the last 5 days -- hence the range.
A reader, June 07, 2005 - 9:42 am UTC
Thanks a lot Tom, it took some time for me to understand.
A reader, June 07, 2005 - 10:40 am UTC
Hi Tom,
Is there a way we can do above task in a single query? instead of using 'union all'; using union / union all will decrease the performance right? Thanks.
June 07, 2005 - 12:58 pm UTC
union all won't necessarily decrease performance.
You said:
"i want everyone from today"
"and everyone up to a certain limit from the 5 days prior"
"sorted"
that query is likely the best approach given those requirements and UNION ALL isn't "evil"
how would this perform?
Alexander, June 07, 2005 - 12:17 pm UTC
I'm wondering if this one will perform well:
select * from emp
--- everybody hired today
where trunc(hiredate) = trunc(sysdate)
-- if less than 100 people hired toady, select some more
or row_number() over(order by hiredate desc)<101
order by hiredate desc
I've heard many times that rownum performs better than row_number(), but haven't seen any good evidence to prove it
June 07, 2005 - 1:11 pm UTC
two things here. first your query.
I went for "please use indexes to maximum benefit since we are getting so few records". so, I wanted queries to use indexes if possible. trunc(hiredate), don't do that in real life (use between, but yes I was assuming hiredate was *a date*, no time).
the analytics cannot be used in a predicate like that, you'd have to assign row_number() to ALL ROWS IN THE EMP table and then wrap that in an inline view.
I was trying to avoid all of that, get me the first rows as soon as possible.
rownum should be used whenever it can.
select *
from (select * from t order by x)
where rownum < :x;
is much preferred over
select *
from ( select t.*, row_number() over (order by x) rn from t )
where rn < :x
order by x;
the former can do a top-n optimization, the latter cannot -- rownum should be used whenver rownum *can* be used.
More very useful stuff from Ask Tom
Nicholas Swandel, February 15, 2006 - 10:24 am UTC
Ask Tom is absolutly the best resource at Oracle!
I am always getting great how to anwsers that make me more productive!
I do wish the search indexing was better. Even when I know of an article I am looking for it can be hard to find.
I like the gentle with newbie, modest, and matter of fact tone of the articles.
I just want to say I love you... (singing)
gold, September 13, 2006 - 7:26 am UTC
how about a list of Sundays between two dates
jack, October 27, 2006 - 12:33 pm UTC
how would you tweak SQL that gives list of dates to just return list of dates that fall on SUNDAY ( or any other specific weekday ) between two dates.
October 27, 2006 - 8:05 pm UTC
maybe something like this:
ops$tkyte%ORA10GR2> variable a varchar2(30)
ops$tkyte%ORA10GR2> variable b varchar2(30)
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec :a := '31-dec-2005'; :b := '25-jul-2006';
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> with data
2 as
3 (select level l
4 from dual
5 connect by level <= ceil( (to_date(:b,'dd-mon-yyyy')-to_date(:a,'dd-mon-yyyy'))/7 )+1
6 )
7 select next_day( to_date(:a,'dd-mon-yyyy')-14, 'sun')+l*7
8 from data
9 where next_day( to_date(:a,'dd-mon-yyyy')-14, 'sun')+l*7
10 between to_date(:a,'dd-mon-yyyy')
11 and to_date(:b,'dd-mon-yyyy');
NEXT_DAY(
---------
01-JAN-06
08-JAN-06
15-JAN-06
22-JAN-06
....
urgent question
swadha, February 14, 2008 - 1:44 am UTC
i have a qustion
i am given start date and end date i need to retrieve those dates that fall in between the start and end date whihc are already stored in the table
e.g.
tid start_date end_date
1 20-feb-08 28-feb-08
2 21-feb-08 27-feb-08
3 23-jan-08 25-jan=08
it should retrieve column having tid 1 and 2 only
kindly answer my request its urgent
February 14, 2008 - 9:26 am UTC
laughing out loud here.
"it should retrieve column having tid 1 and 2 only"
begs me to ask "why?"
I see some data in a table.
I see no INPUTS to this magic process that knows tid=1,2 are the answer
I also see no create table
no insert into....
nothing to help you with. It is hard to write sql when you don't know what the inputs are, and you have no structures to query.
If your inputs are some date range A and B (such that A <= B)
where end_date >= A and start_date <= B
would seem to do it (simple logic)
start_date end_date
A B | | No (start_date>b)
A | B | Yes
A | | B Yes
| A B | Yes
| A | B Yes
| | A B No (end_date<a)
Your website not readable
binu, February 20, 2008 - 12:56 am UTC
It is vey difficult read something from your websit.You should have to think about changing the content format.I like the queries and answers becuase of its high standard
February 20, 2008 - 8:20 am UTC
what isn't readable about it?
Get All Sundays
Abhijit, November 03, 2009 - 3:02 pm UTC
This code gives me the output below.
/*
Anonymous block to get all sundays between a given date range
*/
DECLARE
from_dt DATE := to_date('01/01/2009', 'mm/dd/yyyy');
to_dt DATE := to_date('01/29/2009', 'mm/dd/yyyy');
x VARCHAR2(30);
BEGIN
WHILE NOT FROM_DT > TO_DT
LOOP
IF to_date(x, 'dd-mon-yyyy') >= to_dt THEN
dbms_output.put_line('hello');
EXIT;
ELSE
--dbms_output.put_line('FROM DATE ==>' || from_dt);
x := next_day(from_dt, 'SUN');
IF to_date(x, 'DD-MON-YYYY') > to_dt THEN
dbms_output.put_line('Exit Now');
ELSE
dbms_output.put_line('Sundays -->' || x);
END IF;
END IF;
from_dt := from_dt + 7;
END LOOP;
END;
Output:
Sundays -->04-JAN-09
Sundays -->11-JAN-09
Sundays -->18-JAN-09
Sundays -->25-JAN-09
Sundays -->01-FEB-09
What change do I need to make to ensure that 01-Feb-09 does not get printed? Essentially it needs to print all Sundays between the from date and the to date.
November 09, 2009 - 2:56 pm UTC
ops$tkyte%ORA10GR2> variable x varchar2(30);
ops$tkyte%ORA10GR2> variable y varchar2(30)
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec :x := '01/01/2009';
PL/SQL procedure successfully completed.
X
--------------------------------
01/01/2009
ops$tkyte%ORA10GR2> exec :y := '01/29/2009';
PL/SQL procedure successfully completed.
Y
--------------------------------
01/29/2009
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> with data
2 as
3 (select next_day( to_date(:x,'mm/dd/yyyy')-1, 'Sun' ) + (level-1)*7
4 from dual
5 connect by next_day( to_date(:x,'mm/dd/yyyy')-1, 'Sun' ) + (level-1)*7 <= to_date( :y, 'mm/dd/yyyy' )) 6 select * from data
7 /
NEXT_DAY(TO_DAT
---------------
Sun 04-jan-2009
Sun 11-jan-2009
Sun 18-jan-2009
Sun 25-jan-2009
no code.
A reader, November 12, 2009 - 4:01 am UTC
How can get free time which physician have for any given date range for each parallel schedule id. For example if date given is 11-12-2009 to 11-13-2009 then following data should return
Date Time Parallel_Schedule_Id
11-12-2009 9:26 AM – 9:30 AM 1
11-12-2009 9:46 AM- 1:00 PM 1
11-12-2009 2:00 PM – 3:00 PM 1
11-12-2009 3:31 PM – 6:00 PM 1
11-12-2009 9:21 AM - 12:00 PM 2
11-12-2009 2:00 PM – 6:00 PM 2
11-13-2009 9:00 AM – 01:00 PM 1
11-13-2009 2:00 PM - 6:00 PM 1
11-13-2009 9:00 AM – 12:00 PM 2
11-13-2009 2:00 PM – 6:00 PM 2
CREATE TABLE Physician_Configuration
(
Physician_Configuration_Id NUMBER(8),
Physician_Id NUMBER(4),
From_Time DATE,
To_Time DATE,
Max_Parallel_Schedule NUMBER(2)
);
CREATE TABLE Physician_Configuration_Break
(
Physician_Configuration_Id NUMBER(8),
From_Time DATE,
To_Time DATE,
Parallel_Schedule_Id NUMBER(2)
);
CREATE TABLE Physician_Schedule
(
Physician_Schedule_Id NUMBER(8),
From_Time DATE,
To_Time DATE,
Parallel_Schedule_Id NUMBER(2),
Physician_Id NUMBER(4),
Patient_Id NUMBER(8)
);
SET DEFINE OFF;
Insert into PHYSICIAN_CONFIGURATION
(PHYSICIAN_CONFIGURATION_ID, PHYSICIAN_ID, FROM_TIME, TO_TIME, MAX_PARALLEL_SCHEDULE)
Values
(1, 1, TO_DATE('11/12/2009 09:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/17/2009 18:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into PHYSICIAN_CONFIGURATION
(PHYSICIAN_CONFIGURATION_ID, PHYSICIAN_ID, FROM_TIME, TO_TIME, MAX_PARALLEL_SCHEDULE)
Values
(2, 1, TO_DATE('12/18/2009 08:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('04/29/2010 18:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2);
SET DEFINE OFF;
Insert into PHYSICIAN_CONFIGURATION_BREAK
(PHYSICIAN_CONFIGURATION_ID, FROM_TIME, TO_TIME, PARALLEL_SCHEDULE_ID)
Values
(1, TO_DATE('11/12/2009 13:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/17/2009 14:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);
Insert into PHYSICIAN_CONFIGURATION_BREAK
(PHYSICIAN_CONFIGURATION_ID, FROM_TIME, TO_TIME, PARALLEL_SCHEDULE_ID)
Values
(1, TO_DATE('11/12/2009 12:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/17/2009 14:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into PHYSICIAN_CONFIGURATION_BREAK
(PHYSICIAN_CONFIGURATION_ID, FROM_TIME, TO_TIME, PARALLEL_SCHEDULE_ID)
Values
(1, TO_DATE('11/12/2009 15:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/12/2009 16:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);
Insert into PHYSICIAN_CONFIGURATION_BREAK
(PHYSICIAN_CONFIGURATION_ID, FROM_TIME, TO_TIME, PARALLEL_SCHEDULE_ID)
Values
(2, TO_DATE('12/18/2009 13:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('04/29/2010 15:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);
SET DEFINE OFF;
Insert into PHYSICIAN_SCHEDULE
(PHYSICIAN_SCHEDULE_ID, FROM_TIME, TO_TIME, PARALLEL_SCHEDULE_ID, PHYSICIAN_ID,
PATIENT_ID)
Values
(1, TO_DATE('11/12/2009 09:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/12/2009 09:26:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 1,
1);
Insert into PHYSICIAN_SCHEDULE
(PHYSICIAN_SCHEDULE_ID, FROM_TIME, TO_TIME, PARALLEL_SCHEDULE_ID, PHYSICIAN_ID,
PATIENT_ID)
Values
(2, TO_DATE('11/12/2009 09:30:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/12/2009 09:45:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 1,
2);
Insert into PHYSICIAN_SCHEDULE
(PHYSICIAN_SCHEDULE_ID, FROM_TIME, TO_TIME, PARALLEL_SCHEDULE_ID, PHYSICIAN_ID,
PATIENT_ID)
Values
(3, TO_DATE('11/12/2009 09:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/12/2009 09:20:00', 'MM/DD/YYYY HH24:MI:SS'), 2, 1,
3);
Insert into PHYSICIAN_SCHEDULE
(PHYSICIAN_SCHEDULE_ID, FROM_TIME, TO_TIME, PARALLEL_SCHEDULE_ID, PHYSICIAN_ID,
PATIENT_ID)
Values
(4, TO_DATE('11/12/2009 15:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/12/2009 15:30:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 1,
4);
COMMIT;
November 15, 2009 - 1:45 pm UTC
no keys, no sample query to join, nothing from me.
Come on - explain your data - your data is painfully obvious to YOU. To us, it is just a bunch of tables with columns and we don't know how to join.
parallel_schedule_id - a bunch of characters strung together to form words that do not having meaning to us.
A reader, November 12, 2009 - 4:02 am UTC
Version using is 11G release1 ...How can get free time which physician have for any given date range for each parallel schedule id.
A reader, November 16, 2009 - 7:30 am UTC
Sorry for not providing complete information.
Physician_cofiguration table contains configuration for physician for what date to what date he checks patient from what time to what time for example the two records stores in this table are
For date 11/12/2009 to 12/17/2009 he checks patients from time 9:00 AM to 6:00 PM
For date 12/18/2009 to 4/29/2010 he checks patients from time 8:00 AM to 6:00 PM
The MAX_PARALLEL_SCHEDULE column tells how many patients he can schedule for the same time.
PHYSICIAN_CONFIGURATION_BREAK table contains break information for physician which he has set for any date/time and Parallel Schedule configuration he has given in Physician_Configuration. For the example give for first record of Physician_configuration he has set three break intervals First from 1:00 PM to 2:00 PM , Second 3:00 PM to 4:00 PM for Parallel_Schedule_ID 1 and 12:00 PM to 2:00 PM for Pararrel_Schedule_ID 2. Pararel_Schedule_Id are not set in any table these are dependent on Physician_Configuration and shows the Max_Parallel_Schedule no (in case of first record in Physician_Configuration there is max_paralled_schedule is 3 then in physician_cofiguration_break there can be record for parallel_schedule_id 1, 2, 3). PHYSICIAN_SCHEDULE table contains patients scheduled for physician for parallel_schedule_id for specified date and specified time.
A reader, November 25, 2009 - 1:02 am UTC
CREATE TABLE WORK_SHEET
(
WORK_SHEET_ID NUMBER(10),
OPEN_TIME DATE,
CLOSE_TIME DATE,
TIME_LENGTH NUMBER
);
SET DEFINE OFF;
Insert into WORK_SHEET
(WORK_SHEET_ID, OPEN_TIME, CLOSE_TIME, TIME_LENGTH)
Values
(1, TO_DATE('11/24/2009 09:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/25/2009 18:00:00', 'MM/DD/YYYY HH24:MI:SS'), 5);
COMMIT;
Open_Time has opening time and date, close_time has closing time and date. time_length has intervals. Output required is
11/24/2009 9:00 Am
11/24/2009 9:05 Am
11/24/2009 9:10 Am
..
..
11/24/2009 6:00 Pm
11/25/2009 9:00 Am
11/25/2009 9:05 Am
11/25/2009 9:10 Am
..
..
11/25/2009 6:00 Pm
Means output will be from open_Time to End_Time starting from Date of Open_Time to date of End_time with intervals given in time_length.
November 25, 2009 - 12:42 pm UTC
ops$tkyte%ORA9IR2> select work_sheet_id,
2 to_char(open_time + (x.column_value * time_length/24/60),'mm/dd/yyyy hh:mi Am') tim
3 from work_sheet,
4 table( cast( multiset( select level-1 r
5 from dual
6 connect by level <=
7 ((close_time-open_time) * 24 * 60)/time_length+1 )
8 as sys.odciNumberList ) ) x
9 where open_time + (x.column_value * time_length/24/60) <= close_time
10 /
WORK_SHEET_ID TIM
------------- -------------------
1 11/24/2009 09:00 Am
1 11/24/2009 09:05 Am
1 11/24/2009 09:10 Am
...
1 11/25/2009 05:55 Pm
1 11/25/2009 06:00 Pm
397 rows selected.
you might need to play with line 7 depending on your time units - these were nicely divisible by 5 (so line 9 was not technically needed) - but it'll depend on the exact nature of your data.
A reader, November 26, 2009 - 1:56 am UTC
Thanks tom but there is one thing for each date the time should be from Start_Time to end_Time and then for next date time will start from Start_Time. as for following data time for 11/24/2009 should be 9:00 am to 6:00 pm and then for date 11/25/2009 it will be again from 9:00 am to 6:00 pm. There should no entries for time onward from end_time
11/24/2009 9:00 Am
11/24/2009 9:05 Am
11/24/2009 9:10 Am
..
..
11/24/2009 6:00 Pm
11/25/2009 9:00 Am
11/25/2009 9:05 Am
11/25/2009 9:10 Am
..
..
11/25/2009 6:00 Pm
November 28, 2009 - 12:42 pm UTC
HUH?
No idea what you mean.
not a clue. You asked a question, I developed a query against your data that gave you precisely the output you described what you wanted.
I don't know what you mean in this followup at all - are you changing your example or output somehow????
my output was:
WORK_SHEET_ID TIM
------------- -------------------
1 11/24/2009 09:00 Am
1 11/24/2009 09:05 Am
1 11/24/2009 09:10 Am
...
1 11/25/2009 05:55 Pm
1 11/25/2009 06:00 Pm
397 rows selected.
how does that differ from what you want???!?!?!
A reader, December 07, 2009 - 2:02 am UTC
No i am not changing example or output, if you see my question i asked output as follows:
11/24/2009 9:00 Am
11/24/2009 9:05 Am
11/24/2009 9:10 Am
..
..
11/24/2009 6:00 Pm
11/25/2009 9:00 Am
11/25/2009 9:05 Am
11/25/2009 9:10 Am
..
..
11/25/2009 6:00 Pm
The output is for 11/24/2009 from 9:00 am to 6:00 Pm and then starts 11/25/2009 from 9:00 am to 6:00 pm. Sorry i could not make it clear in question but the output was showing it.
December 07, 2009 - 2:53 pm UTC
I then have no clue what you are asking for. As far as I can tell you asked:
Output required is
11/24/2009 9:00 Am
11/24/2009 9:05 Am
11/24/2009 9:10 Am
..
..
11/24/2009 6:00 Pm
11/25/2009 9:00 Am
11/25/2009 9:05 Am
11/25/2009 9:10 Am
..
..
11/25/2009 6:00 Pm
Means output will be from open_Time to End_Time starting from Date of Open_Time to date of End_time
with intervals given in time_length.
and I responded:
ops$tkyte%ORA9IR2> select work_sheet_id,
2 to_char(open_time + (x.column_value * time_length/24/60),'mm/dd/yyyy hh:mi Am') tim
3 from work_sheet,
4 table( cast( multiset( select level-1 r
5 from dual
6 connect by level <=
7 ((close_time-open_time) * 24 *
60)/time_length+1 )
8 as sys.odciNumberList ) ) x
9 where open_time + (x.column_value * time_length/24/60) <= close_time
10 /
WORK_SHEET_ID TIM
------------- -------------------
1 11/24/2009 09:00 Am
1 11/24/2009 09:05 Am
1 11/24/2009 09:10 Am
...
1 11/25/2009 05:55 Pm
1 11/25/2009 06:00 Pm
397 rows selected.
WHICH IS EXACTLY WHAT YOU ASKED FOR - exactly.
but you wrote:
There should no entries for time onward from end_time
11/24/2009 9:00 Am
11/24/2009 9:05 Am
11/24/2009 9:10 Am
..
..
11/24/2009 6:00 Pm
11/25/2009 9:00 Am
11/25/2009 9:05 Am
11/25/2009 9:10 Am
..
..
11/25/2009 6:00 Pm
and I have no idea what you mean.
A reader, December 08, 2009 - 1:32 am UTC
Thanks for your patience and time.
By saying "there should no entries for time onward from end_time" i ment that for each date time should start from start time (9:00 Am) and end time should be (6:00 Pm) then there should no entries for 6:05 pm to 8:55 am. Next date will start with 9:00 am to 6:00 pm. I wanted that for each date duration should be between start_time to end_time and there should no records for end_time of current date.
December 10, 2009 - 8:21 am UTC
I'd say "your data model is whacked, broken, wrong"
however, you cannot figure out how to correct the query output with a simple where clause? Are you sure you cannot?
we have
9 where open_time + (x.column_value * time_length/24/60) <= close_time
what if you added to it.....
and to_char(open_time + (x.column_value * time_length/24/60),'hh24mi')
between '0900' and '1800'
ops$tkyte%ORA9IR2> select work_sheet_id,
2 to_char(open_time + (x.column_value * time_length/24/60),'mm/dd/yyyy hh:mi Am') tim
3 from work_sheet,
4 table( cast( multiset( select level-1 r
5 from dual
6 connect by level <=
7 ((close_time-open_time) * 24 * 60)/time_length+1 )
8 as sys.odciNumberList ) ) x
9 where open_time + (x.column_value * time_length/24/60) <= close_time
10 and to_char(open_time + (x.column_value * time_length/24/60),'hh24mi') between '0900' and '1800'
11 /
WORK_SHEET_ID TIM
------------- -------------------
1 11/24/2009 09:00 Am
1 11/24/2009 09:05 Am
...
1 11/24/2009 05:50 Pm
1 11/24/2009 05:55 Pm
1 11/24/2009 06:00 Pm
1 11/25/2009 09:00 Am
1 11/25/2009 09:05 Am
...
1 11/25/2009 05:55 Pm
1 11/25/2009 06:00 Pm
218 rows selected.
To Reader above
Raj, December 09, 2009 - 4:31 am UTC
How about a query like this?
scott@orcl> variable sdate varchar2(20)
scott@orcl> variable edate varchar2(20)
scott@orcl> variable mint number
scott@orcl> exec :sdate := '01-Dec-2009 09:00:00';
PL/SQL procedure successfully completed.
scott@orcl> exec :edate := '02-Dec-2009 18:00:00';
PL/SQL procedure successfully completed.
scott@orcl> exec :mint := 5
PL/SQL procedure successfully completed.
scott@orcl>
scott@orcl> with data as (select to_date(:sdate,'dd-Mon-yyyy hh24:mi:ss') sdate,
2 to_date(:edate,'dd-Mon-yyyy hh24:mi:ss') edate, :mint mint from dual
3 )
4 select * from (
5 select sdate + ((level-1)*mint/24/60) sdate, edate from data
6 connect by level <= (edate - sdate)*24*60 )
7 where edate >= sdate
8 and to_number(to_char(sdate,'hh24mi')) between 900 and 1800
9 /
SDATE EDATE
-------------------- --------------------
01-Dec-2009 09:00:00 02-Dec-2009 18:00:00
01-Dec-2009 09:05:00 02-Dec-2009 18:00:00
01-Dec-2009 09:10:00 02-Dec-2009 18:00:00
01-Dec-2009 09:15:00 02-Dec-2009 18:00:00
01-Dec-2009 09:20:00 02-Dec-2009 18:00:00
01-Dec-2009 09:25:00 02-Dec-2009 18:00:00
........
01-Dec-2009 17:50:00 02-Dec-2009 18:00:00
01-Dec-2009 17:55:00 02-Dec-2009 18:00:00
01-Dec-2009 18:00:00 02-Dec-2009 18:00:00
02-Dec-2009 09:00:00 02-Dec-2009 18:00:00
02-Dec-2009 09:05:00 02-Dec-2009 18:00:00
.......
02-Dec-2009 11:40:00 02-Dec-2009 18:00:00
02-Dec-2009 11:45:00 02-Dec-2009 18:00:00
.......
02-Dec-2009 17:55:00 02-Dec-2009 18:00:00
02-Dec-2009 18:00:00 02-Dec-2009 18:00:00
218 rows selected.
scott@orcl>
Now I may have missed some boundary condition checking, may be Tom can point out those.
A reader, December 10, 2009 - 8:38 am UTC
thanks. I'd say "your data model is whacked, broken, wrong" how can be model managed properly.
We need to keep date range and also start and end time. What you suggest keep separate fields for start date , end date and start time and end time. currently open_time and close_time have both time and date.
CREATE TABLE WORK_SHEET
(
WORK_SHEET_ID NUMBER(10),
OPEN_TIME DATE,
CLOSE_TIME DATE,
TIME_LENGTH NUMBER
);
December 10, 2009 - 3:17 pm UTC
if you wanted non-contiguous things like that, it would call for separate attributes typically
date range
start/stop times for the date range.
what you have works, but is not intuitive or clear.
Another Approach
Robert Frick, March 05, 2010 - 5:52 pm UTC
Developed this to generate a list of descending dates for the next two years. I got on this path thanks to the previous entries. Thanks !
select
to_char(sysdate - level,'Mon'),
to_char(sysdate - level,'YYYY')
from dual connect by level <= 730
group by to_char(sysdate - level,'YYYYMM'),
to_char(sysdate - level,'Mon'),
to_char(sysdate - level,'YYYY')
order by to_char(sysdate - level,'YYYYMM') desc
March 09, 2010 - 10:33 am UTC
ops$tkyte%ORA10GR2> select to_char( add_months(trunc(sysdate,'mm'),level-1), 'Mon YYYY')
2 from dual connect by level <= 25
3 order by add_months(trunc(sysdate,'mm'),level-1)
4 /
TO_CHAR(
--------
Mar 2010
Apr 2010
May 2010
Jun 2010
Jul 2010
Aug 2010
Sep 2010
Oct 2010
Nov 2010
Dec 2010
Jan 2011
Feb 2011
Mar 2011
Apr 2011
May 2011
Jun 2011
Jul 2011
Aug 2011
Sep 2011
Oct 2011
Nov 2011
Dec 2011
Jan 2012
Feb 2012
Mar 2012
25 rows selected.
might be better.
Generating dates between two date ranges
Ron F, April 01, 2010 - 1:17 pm UTC
Thought that this might also be a terser way to get the same results for Sundays between a range, based on Oct , 2006 post
select to_date ('2005-12-30', 'yyyy-mm-dd') + (level - 1) as desired_date
from dual
where to_char(to_date('2005-12-30', 'yyyy-mm-dd') + level - 1, 'DY') = 'SUN'
connect by level <= ceil( (to_date('25-jul-2006','dd-mon-yyyy')
-to_date('30-dec-2005','dd-mon-yyyy'))+1 )
order by desired_date ;
DESIRED_DATE
1/1/2006
1/8/2006
1/15/2006
1/22/2006
1/29/2006
...
Business Days between two dates
Ashok, April 07, 2010 - 5:27 pm UTC
Hi Tom,
Please help me in getting the number of business day between two dates(DATE_REQ and DATE_COM ) in a table WRK.(Business day = Excluding Saturday, Sunday and holidays). Holidays are in separte table(Holiday).
(E.g)
CREATE TABLE WRK (ID VARCHAR2(10),DATE_REQ DATE, DATE_COM DATE);
INSERT INTO WRK VALUES(1,TO_DATE('01/18/2010','MM/DD/YYYY'),TO_DATE('01/23/2010','MM/DD/YYYY'));
INSERT INTO WRK VALUES(2,TO_DATE('02/15/2010','MM/DD/YYYY'),TO_DATE('02/19/2010','MM/DD/YYYY'));
INSERT INTO WRK VALUES(3,TO_DATE('02/22/2010','MM/DD/YYYY'),TO_DATE('02/24/2010','MM/DD/YYYY'));
CREATE TABLE Holiday (HOL_DATE DATE,DESCRIPTION VARCHAR2(20));
INSERT INTO HOLIDAY('01/21/2010','Martin Luther King, Jr');
INSERT INTO HOLIDAY VALUES('01/21/2010,'Martin Luther King, Jr');
INSERT INTO HOLIDAY('02/18/2010','Presidents Day');
Thanks,
Kumar
April 13, 2010 - 7:42 am UTC
How about a list of Sundays and Mondays Between Two Dates?
Salan, May 19, 2010 - 3:14 pm UTC
I already saw the below follow up post on how to list all dates that fall on Sundays between two dates, but how we could add also to the same list all dates that fall on Mondays as well?
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> with data
2 as
3 (select level l
4 from dual
5 connect by level <= ceil( (to_date(:b,'dd-mon-yyyy')-to_date(:a,'dd-mon-yyyy'))/7 )+1
6 )
7 select next_day( to_date(:a,'dd-mon-yyyy')-14, 'sun')+l*7
8 from data
9 where next_day( to_date(:a,'dd-mon-yyyy')-14, 'sun')+l*7
10 between to_date(:a,'dd-mon-yyyy')
11 and to_date(:b,'dd-mon-yyyy');
NEXT_DAY(
---------
01-JAN-06
08-JAN-06
15-JAN-06
22-JAN-06
....
May 24, 2010 - 11:24 am UTC
modify the where clause - look at the where clause and change it to keep only mondays.
date range
A reader, June 02, 2011 - 9:46 am UTC
CREATE TABLE TEST_DATE
(
ID NUMBER(2),
FROM_DATE DATE,
TO_DATE DATE
);
SET DEFINE OFF;
Insert into TEST_DATE
(ID, FROM_DATE, TO_DATE)
Values
(1, TO_DATE('04/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('04/11/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATE
(ID, FROM_DATE, TO_DATE)
Values
(2, TO_DATE('04/12/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/15/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATE
(ID, FROM_DATE, TO_DATE)
Values
(3, TO_DATE('06/10/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/25/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATE
(ID, FROM_DATE, TO_DATE)
Values
(4, TO_DATE('10/20/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
There are missing dates, need to fill these gaps for example (5/16/2010 to 6/09/2010, 9/26/2010 to 10/19/2010)
June 02, 2011 - 10:17 am UTC
ops$tkyte%ORA11GR2> with data as (select 1 r from dual union all select 2 r from dual)
2 select id,
3 case when r = 1 then from_date else to_date+1 end f_date,
4 case when r = 1 then to_date else next_date-1 end t_date,
5 case when r = 2 then '*' end flag
6 from (
7 select id, from_date, to_date, next_date, r
8 from (
9 select id, from_date, to_date, lead(from_date) over (order by id) next_date
10 from test_date
11 ) x, data
12 where r = 1
13 or (r=2 and next_date <> to_date+1 )
14 )
15 order by id, r
16 /
ID F_DATE T_DATE F
---------- --------- --------- -
1 01-APR-10 11-APR-10
2 12-APR-10 15-MAY-10
2 16-MAY-10 09-JUN-10 *
3 10-JUN-10 25-SEP-10
3 26-SEP-10 19-OCT-10 *
4 20-OCT-10 01-NOV-10
6 rows selected.
presuming no overlaps in the existing data - just missing gaps...
date
sruthi, September 13, 2011 - 6:12 am UTC
hi tom,
i want to retrieve data between to dates irrepctible of year
September 13, 2011 - 6:27 am UTC
where to_number( to_char( date_column, 'mmdd' ) ) between to_number( to_char( :first_date, 'mmdd' ) ) and to_number( to_char( :second_date, 'mmdd' ) )
would be one approach.
rebel, November 24, 2012 - 3:13 am UTC
Hi Tom,
I have a table in below format,
startdate endate bednumber
---------- ------- ---------
24/11/2012 26/11/2012 XYZ
I need the following result to be inserted in a table.
24-Nov XYZ
25-Nov XYZ
26-Nov XYZ
Thanks in advance
November 29, 2012 - 7:44 am UTC
here is a query that works if you just have one row to target by some key, then:
ops$tkyte%ORA11GR2> create table t
2 as
3 select 1 id, trunc(sysdate-5) startdate, trunc(sysdate-2) enddate, 'xyz' bednumber from dual
4 union all
5 select 2 id, trunc(sysdate-7) startdate, trunc(sysdate-3) enddate, 'abc' bednumber from dual
6 /
Table created.
ops$tkyte%ORA11GR2> select enddate-startdate+1 ndates,
2 startdate,
3 enddate,
4 startdate+rownum-1 thedate,
5 bednumber
6 from t
7 start with id = 1
8 connect by level <= enddate-startdate+1 and id=1
9 /
NDATES STARTDATE ENDDATE THEDATE BED
---------- --------- --------- --------- ---
4 24-NOV-12 27-NOV-12 24-NOV-12 xyz
4 24-NOV-12 27-NOV-12 25-NOV-12 xyz
4 24-NOV-12 27-NOV-12 26-NOV-12 xyz
4 24-NOV-12 27-NOV-12 27-NOV-12 xyz
this works for any number of rows - from zero to whatever:
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select id, startdate, enddate,
2 startdate+COLUMN_VALUE-1 thedate,
3 bednumber
4 from t, TABLE(
5 cast(
6 multiset( select level l
7 from dual
8 connect by level <= enddate-startdate+1) as sys.odciNumberList )
9 )
10 where id = 2
11 /
ID STARTDATE ENDDATE THEDATE BED
---------- --------- --------- --------- ---
2 22-NOV-12 26-NOV-12 22-NOV-12 abc
2 22-NOV-12 26-NOV-12 23-NOV-12 abc
2 22-NOV-12 26-NOV-12 24-NOV-12 abc
2 22-NOV-12 26-NOV-12 25-NOV-12 abc
2 22-NOV-12 26-NOV-12 26-NOV-12 abc
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select id, startdate, enddate,
2 startdate+COLUMN_VALUE-1 thedate,
3 bednumber
4 from t, TABLE(
5 cast(
6 multiset( select level l
7 from dual
8 connect by level <= enddate-startdate+1) as sys.odciNumberList )
9 )
10 /
ID STARTDATE ENDDATE THEDATE BED
---------- --------- --------- --------- ---
1 24-NOV-12 27-NOV-12 24-NOV-12 xyz
1 24-NOV-12 27-NOV-12 25-NOV-12 xyz
1 24-NOV-12 27-NOV-12 26-NOV-12 xyz
1 24-NOV-12 27-NOV-12 27-NOV-12 xyz
2 22-NOV-12 26-NOV-12 22-NOV-12 abc
2 22-NOV-12 26-NOV-12 23-NOV-12 abc
2 22-NOV-12 26-NOV-12 24-NOV-12 abc
2 22-NOV-12 26-NOV-12 25-NOV-12 abc
2 22-NOV-12 26-NOV-12 26-NOV-12 abc
9 rows selected.
to date and next from date is greater than 60 Days
chandu, November 20, 2013 - 7:24 am UTC
Hi Tom,
For same member withy different lines to date and end date for all lines total should be greater than 100 days. If below 100 days we dont want all above lines and The difference between to date and next from date is greater than 60 Days so from here new count of 100 days starts again.
Greg
Greg, July 01, 2014 - 9:03 am UTC
Most helpful
A reader, July 23, 2014 - 1:14 pm UTC
Thanks for giving such answer...
very much useful
Amar, June 28, 2016 - 12:40 pm UTC
How can I get dates alternately in two columns
like
01-03-2016 02-03-2016
03-03-2016 04-03-2016
.
.
.
.
like that
Thank You..
June 29, 2016 - 3:35 am UTC
SQL> select
2 date '2010-01-01' + 2*rownum-2 x,
3 date '2010-01-01' + 2*rownum-1 y
4 from dual
5 connect by level <= 10;
X Y
--------- ---------
01-JAN-10 02-JAN-10
03-JAN-10 04-JAN-10
05-JAN-10 06-JAN-10
07-JAN-10 08-JAN-10
09-JAN-10 10-JAN-10
11-JAN-10 12-JAN-10
13-JAN-10 14-JAN-10
15-JAN-10 16-JAN-10
17-JAN-10 18-JAN-10
19-JAN-10 20-JAN-10
10 rows selected.
Thank you very much sir
Amar, June 29, 2016 - 4:34 am UTC
thanks for the valuable time
June 29, 2016 - 6:53 am UTC
glad we could help
Amar, June 29, 2016 - 4:41 am UTC
There are two input dates and I want to display
dates between these two input dates like,
Start Date :- 01-04-2016
End Date :- 01-05-2016
01-04-2016 02-04-2016
03-04-2016 04-04-2016
.
.
.
.
June 29, 2016 - 6:58 am UTC
You just need to have the right number of rows...eg
SQL> select
2 x,
3 case when y <= date '2010-01-31' then y end y
4 from (
5 select
6 date '2010-01-01' + 2*rownum-2 x,
7 date '2010-01-01' + 2*rownum-1 y
8 from dual
9 connect by level <= ( date '2010-01-31' - date '2010-01-01' ) / 2 + 1
10 );
X Y
--------- ---------
01-JAN-10 02-JAN-10
03-JAN-10 04-JAN-10
05-JAN-10 06-JAN-10
07-JAN-10 08-JAN-10
09-JAN-10 10-JAN-10
11-JAN-10 12-JAN-10
13-JAN-10 14-JAN-10
15-JAN-10 16-JAN-10
17-JAN-10 18-JAN-10
19-JAN-10 20-JAN-10
21-JAN-10 22-JAN-10
23-JAN-10 24-JAN-10
25-JAN-10 26-JAN-10
27-JAN-10 28-JAN-10
29-JAN-10 30-JAN-10
31-JAN-10
want to build a date wise table
A reader, August 31, 2019 - 7:48 am UTC
eg. I have a table like this,
Dat acc_balance
1-sep-2019 1000
5-sep-2019 2000
6-sep-2019 2500
9-sep-2019 3000
table to be build like below
1-sep-2019 1000
2-sep-2019 1000
3-sep-2019 1000
4-sep-2019 1000
5-sep-2019 2000
6-sep-2019 2500
7-sep-2019 2500
8-sep-2019 2500
9-sep-2019 3000
September 02, 2019 - 10:17 am UTC
You can use lead to convert the data to a series of start/end dates
with dates as (
select date'2019-09-01' dt from dual union all
select date'2019-09-03' dt from dual union all
select date'2019-09-07' dt from dual
)
select dt start_dt,
lead ( dt ) over (
order by dt
) end_date
from dates;
START_DT END_DATE
01-SEP-2019 00:00:00 03-SEP-2019 00:00:00
03-SEP-2019 00:00:00 07-SEP-2019 00:00:00
07-SEP-2019 00:00:00 <null> From here it's just a matter of joining this to a table of all the dates themselves. Which could be a physical table or generated as above.
slight performance hit for JDBC clients
John, November 12, 2019 - 3:47 pm UTC
Very helpful even so many years later.
Does it make sense that this query would run 100's of milliseconds longer via JDBC client than a "traditional" oracle client?
If I replace all_objects with any old table the user can query, the performance difference goes away, FYI. Perhaps a JDBC client doesn't cache all_objects.
November 12, 2019 - 4:26 pm UTC
Perhaps a JDBC client doesn't cache all_objects
The driver won't cache the table. It's the database that does that.
Is this repeatable? Are you using the same user for JDBC and Oracle client tests? Have you got an example code you can share?
excellent
Anil, March 03, 2020 - 6:55 am UTC
can you explain select to_date('13-01-20','dd-mm-yy') + rownum -1 atten_date
from all_objects
where rownum <= to_date(13-01-20,'dd-mm-yy')-to_date('14-01-20','dd-mm-yy')+1; this code how it performs.
March 03, 2020 - 10:34 am UTC
Well that query will be fast -
to_date(13-01-20,'dd-mm-yy')-to_date('14-01-20','dd-mm-yy')+1is zero! So the database can optimize this to do little or no work.
But in general this way will have to read N rows from all_objects; look at the execution plan to see how much work this is doing.
Personally I'd use fast dual:
select to_date('13-01-20','dd-mm-yy') + rownum -1 atten_date
from dual
connect by level <= to_date('31-01-20','dd-mm-yy')-to_date('14-01-20','dd-mm-yy')+1; Or create a real table of dates.
Marius, August 04, 2020 - 6:28 pm UTC
Hi Tom,
I have this table:
col_A col_B col_3
01.07.2020 04.07.2020 3
01.07.2020 04.07.2020 3
01.07.2020 04.07.2020 3
07.07.2020 09.07.2020 2
07.07.2020 09.07.2020 2
and I need a select statement which returns:
col_A col_B
01.07.2020 02.07.2020
02.07.2020 03.07.2020
03.07.2020 04.07.2020
07.07.2020 08.07.2020
08.07.2020 09.07.2020
Could you please tell me how can I receive that kind of result?
Thank you !
Marius
August 05, 2020 - 3:09 am UTC
select '01.07.2020','02.07.2020' from dual
union all select '02.07.2020','03.07.2020 from dual
union all select '03.07.2020','04.07.2020 from dual
union all select '07.07.2020','08.07.2020 from dual
union all select '08.07.2020','09.07.2020 from dual
Seriously ... we need context, we need test scripts and we need descriptions
FROM TABLE
Noureddine BENNIOU, December 11, 2020 - 6:54 pm UTC
SELECT (DATE_DEBUT+LEVEL-1) DATES_NON_SCOLAIRES from periode_non_scolaire connect by level<=((DATE_FIN-DATE_DEBUT)+1)