Home » SQL & PL/SQL » SQL & PL/SQL » Generate Time slots
Generate Time slots [message #286550] Sat, 08 December 2007 03:19 Go to next message
jagadeeshg
Messages: 14
Registered: December 2007
Location: BLR
Junior Member

Hi Gurus

I have a master table time_slot_master.
( working_time_from date,
working_time_to date,
time_slot_key number(3) primary key
duratuion number(5,2)
slot_duaration number(5,2));

I want to create a child table which captures time slots.

time_slot_key number(3),
slot_id number(5),
time_slot_from date ,
time_slot_to date ,
duration number(5,2)


I want to store date time in working_time_from (9.00 a.m) and working_time_to(5.30 p.m.) columns.

if I deduct working_time_to - working_time_from then
i should get the duration.E.g. 8 hrs 30 mins.

slot duration is 30 mins default.


based on these master values I will generate a unique key
and insert into child table based on slot duration.
E.g. 9.00 a.m. + 30 min I should get time_slot_to column values.

like this I want to populate upto working_time_to value.

please tell me how to do this.

thanks in advance.








Re: Generate Time slots [message #286551 is a reply to message #286550] Sat, 08 December 2007 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not clear what you have and what you want.
Post a test case (create table and insert statements) and the result you want with this test case.

Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: Generate Time slots [message #286556 is a reply to message #286550] Sat, 08 December 2007 03:51 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Here is One clue .


SQL> with timeset as(
  2  select Trunc(sysdate )+ 9/24    ftime,
  3        Trunc(sysdate )+ 17.5/24 totime
  4    from dual )
  5    select ftime + ((level-1)*1/48)
  6    from timeset
  7    connect by level <= (totime - ftime)*48 +1;

FTIME+((LEVEL-1)*1/4
--------------------
08-DEC-2007 09:00:00
08-DEC-2007 09:30:00
08-DEC-2007 10:00:00
08-DEC-2007 10:30:00
08-DEC-2007 11:00:00
08-DEC-2007 11:30:00
08-DEC-2007 12:00:00
08-DEC-2007 12:30:00
08-DEC-2007 01:00:00
08-DEC-2007 01:30:00
08-DEC-2007 02:00:00
08-DEC-2007 02:30:00
08-DEC-2007 03:00:00
08-DEC-2007 03:30:00
08-DEC-2007 04:00:00
08-DEC-2007 04:30:00
08-DEC-2007 05:00:00
08-DEC-2007 05:30:00

18 rows selected.

SQL>


Thumbs Up
Rajuvan.
Re: Generate Time slots [message #286714 is a reply to message #286556] Sun, 09 December 2007 23:55 Go to previous messageGo to next message
jagadeeshg
Messages: 14
Registered: December 2007
Location: BLR
Junior Member

Dear Rajuvan

Thank u for giving the clue. It help me a lot.

I tried the query like below

1 with timeset as(
2 select Trunc(sysdate )+ 9/24 ftime,
3 Trunc(sysdate )+ 17.5/24 totime
4 from dual )
5 select ftime + ((level)*1/48),level,totime
6 from timeset
7* connect by level <= (totime - ftime)*48 +1
fas-dtadev>/

FTIME+((LEVEL)*1/48) LEVEL TOTIME
-------------------- ---------- --------------------
10-dec-2007 09:30:00 1 10-dec-2007 05:30:00

I am getting only one row.

How u are able to generate this series ?

do I have to make any pre-requisites.

thankx in advance

Re: Generate Time slots [message #286716 is a reply to message #286550] Sun, 09 December 2007 23:58 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
U is NOT a member of this forum.
Re: Generate Time slots [message #286719 is a reply to message #286714] Mon, 10 December 2007 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sat, 08 December 2007 10:34

Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).


If you did what has been asked, you'd had your answer.

Regards
Michel

[Updated on: Mon, 10 December 2007 00:21]

Report message to a moderator

Re: Generate Time slots [message #286721 is a reply to message #286550] Mon, 10 December 2007 00:22 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

It depends on the the tool you are using.

select * from 
(with timeset as(
  select Trunc(sysdate )+ 9/24    ftime,
         Trunc(sysdate )+ 17.5/24 totime
    from dual )
    select ftime + ((level-1)*1/48)
   from timeset
    connect by level <= (totime - ftime)*48 +1)


may help you in that case.

Avoid IM speak terms like 'u' from next time .

Thumbs Up
Rajuvan.
Re: Generate Time slots [message #286723 is a reply to message #286714] Mon, 10 December 2007 00:39 Go to previous messageGo to next message
jagadeeshg
Messages: 14
Registered: December 2007
Location: BLR
Junior Member

Dear Rajuvu1

Ok I will follow the rules of the forum.Since time constraint I could not follow standards.Next time onwards I do follow.
coming back to question.I am using sqlplus - database version 9i release 2.

Thankx in advance


Re: Generate Time slots [message #286728 is a reply to message #286723] Mon, 10 December 2007 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
10g:
SQL> select level from dual connect by level <= 3;
     LEVEL
----------
         1
         2
         3

3 rows selected.


9iR2:
SQL> select level from dual connect by level <= 3;
     LEVEL
----------
         1

1 row selected.

SQL> select * from ( select level from dual connect by level <= 3 );
     LEVEL
----------
         1
         2
         3

3 rows selected.

Regards
Michel
Re: Generate Time slots [message #286790 is a reply to message #286728] Mon, 10 December 2007 02:32 Go to previous messageGo to next message
jagadeeshg
Messages: 14
Registered: December 2007
Location: BLR
Junior Member

Dear Michel

Thank you very much.

I have one more question for you. Can you please tell me why this behaviour in 9i relase 2 sqlplus. Is there any sepecific reason for this? If you suggest me to go through any material related to it.I will sure do it.

Thank you in advance




Re: Generate Time slots [message #286805 is a reply to message #286790] Mon, 10 December 2007 02:56 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
It's a SQL*plus feature/bug.

MHE
Re: Generate Time slots [message #286807 is a reply to message #286790] Mon, 10 December 2007 03:01 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"Connect by" algorithm and behaviour change and are improved in each version.
In 8i, you got:
SQL>  select level from dual connect by level <= 3;
     LEVEL
----------
         1

1 row selected.

SQL>  select * from ( select level from dual connect by level <= 3 );
ERROR:
ORA-01436: CONNECT BY loop in user data

no rows selected

Regards
Michel

[Updated on: Mon, 10 December 2007 03:03]

Report message to a moderator

Previous Topic: Query for retrieving date records
Next Topic: outer join
Goto Forum:
  


Current Time: Sun Dec 11 08:04:34 CST 2016

Total time taken to generate the page: 0.06126 seconds