Home » SQL & PL/SQL » SQL & PL/SQL » calculate breakfast,lunch and dinner time between 2 times
calculate breakfast,lunch and dinner time between 2 times [message #228845] Wed, 04 April 2007 06:54 Go to next message
Atif107
Messages: 5
Registered: April 2007
Junior Member
Hi

i have this type of data in my table

OT_DATE FROM_TIME TO_TIME FROM_ TO_TIME
10-JAN-07 01-APR-07 01-APR-07 17:30 19:30
11-JAN-07 01-APR-07 01-APR-07 17:30 19:30
12-JAN-07 01-APR-07 01-APR-07 18:30 20:30
13-JAN-07 01-APR-07 01-APR-07 08:00 17:30
14-JAN-07 01-APR-07 01-APR-07 08:00 17:30
15-JAN-07 01-APR-07 01-APR-07 17:30 19:30

I need to calculate 3 more columns breakfast ,lunch and dinner.The rule is from 07:00 to 08:00 one breakfast will be counted.from 13:00 to 14:00 one lunch will be counted
and from 20:00 to 00:00(12pm) one dinner will be counted

Against each row i need to calculate number of bfast,lunch and dinner if from_time and to_time contains any of time slots alloted for each meal then return 1 for respective column else return 0 for that column.Is it possible through sql.
Regards

Re: calculate breakfast,lunch and dinner time between 2 times [message #228851 is a reply to message #228845] Wed, 04 April 2007 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I had the same exercise when I was a student, 25 years ago.

Regards
Michel
Re: calculate breakfast,lunch and dinner time between 2 times [message #228860 is a reply to message #228851] Wed, 04 April 2007 07:40 Go to previous messageGo to next message
Atif107
Messages: 5
Registered: April 2007
Junior Member
Hi
Then how did you manage this.
Re: calculate breakfast,lunch and dinner time between 2 times [message #228863 is a reply to message #228860] Wed, 04 April 2007 07:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I first asked if I have to count one meal if the whole period is included between from/to times or if only a part of it is sufficient.

Regards
Michel
Re: calculate breakfast,lunch and dinner time between 2 times [message #228891 is a reply to message #228863] Wed, 04 April 2007 08:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And let me guess, Michel.
After that you tried to do it yourself and only asked help when you got stuck.
Then you showed what you tried and where you thought things went wrong?

Good boy you were back then... Wink
Re: calculate breakfast,lunch and dinner time between 2 times [message #228905 is a reply to message #228891] Wed, 04 April 2007 09:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wow Frank! How do you guess? Wink

Michel
Re: calculate breakfast,lunch and dinner time between 2 times [message #228930 is a reply to message #228891] Wed, 04 April 2007 09:58 Go to previous messageGo to next message
Atif107
Messages: 5
Registered: April 2007
Junior Member
Ok i did try to do it.
This is what i am trying
say for breakfast the time slot is 07:00 to 08:00 and f_time is 07:30 and to_time is 08:30
if FTIME >= '0700' and :tO_time <= '0800' Then b_fast = 1
but what if ftime = '0600' and to_time = '10:00' the condition will fail.
Regards

Re: calculate breakfast,lunch and dinner time between 2 times [message #229129 is a reply to message #228930] Thu, 05 April 2007 08:41 Go to previous messageGo to next message
Atif107
Messages: 5
Registered: April 2007
Junior Member
If from_time is 7:30 and to_time is 13:30 then we will have one break fast, one lunch and zero dinner.
because 7:30 lies between 07:00 to 08:00(time slot for bfast) and 13:30 lies between 13:00 to 14:00(time slot for lunch).
Regards
Re: calculate breakfast,lunch and dinner time between 2 times [message #229133 is a reply to message #229129] Thu, 05 April 2007 09:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, now post a test case (create table and insert statements).

Regards
Michel
Re: calculate breakfast,lunch and dinner time between 2 times [message #229150 is a reply to message #229133] Thu, 05 April 2007 09:45 Go to previous messageGo to next message
Atif107
Messages: 5
Registered: April 2007
Junior Member
Hi
Here is the test script

create table OVERTIME(
o_date date,
ftime VARCHAR2(5),
to_time VARCHAR2(5));

BEGIN
INSERT INTO OVERTIME VALUES('01-JAN-2007','0730','1400');
INSERT INTO OVERTIME VALUES('02-JAN-2007','0700','1200');
INSERT INTO OVERTIME VALUES('03-JAN-2007','1110','1525');
INSERT INTO OVERTIME VALUES('04-JAN-2007','1720','2300');
INSERT INTO OVERTIME VALUES('05-JAN-2007','0800','2300');
END;
Regards
Re: calculate breakfast,lunch and dinner time between 2 times [message #229163 is a reply to message #229150] Thu, 05 April 2007 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I choose that at least one minute should be in the period to take the meal into account.
If you don't agree then change the boundaries.
SQL> select o_date, ftime, to_time,
  2         case when   ftime between '0700' and '0759' 
  3                  or to_time between '0701' and '0800' 
  4                  or ( ftime < '0700' and to_time > '0800' )
  5                then 'Y'
  6              else 'N'
  7         end breakfast,
  8         case when   ftime between '1300' and '1359' 
  9                  or to_time between '1301' and '1400' 
 10                  or ( ftime < '1300' and to_time > '1400' )
 11                then 'Y'
 12              else 'N'
 13         end lunch,
 14         case when   ftime between '2000' and '2359' 
 15                  or to_time between '2001' and '2359' 
 16                  or to_time = '0000'
 17                then 'Y'
 18              else 'N'
 19         end dinner
 20  from overtime
 21  order by 1, 2
 22  /
O_DATE      FTIME TO_TI B L D
----------- ----- ----- - - -
01-JAN-2007 0730  1400  Y Y N
02-JAN-2007 0700  1200  Y N N
03-JAN-2007 1110  1525  N Y N
04-JAN-2007 1720  2300  N N Y
05-JAN-2007 0800  2300  N Y Y

5 rows selected.

Regards
Michel
Re: calculate breakfast,lunch and dinner time between 2 times [message #229165 is a reply to message #229163] Thu, 05 April 2007 11:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ah, Sad
Spoonfed after all...
Re: calculate breakfast,lunch and dinner time between 2 times [message #229166 is a reply to message #229165] Thu, 05 April 2007 11:05 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
He already got another answer from Tom Kyte one hour ago.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5011677391274#204419400346421412

(added)
And I had fun to do it, so I post it. Smile

Regards
Michel

[Updated on: Thu, 05 April 2007 11:06]

Report message to a moderator

Previous Topic: Presenting rows as columns
Next Topic: How to obtain a recordcount
Goto Forum:
  


Current Time: Fri Dec 09 02:26:04 CST 2016

Total time taken to generate the page: 0.05789 seconds