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 Wed, 04 April 2007 06:54
 Atif107 Messages: 5Registered: 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
 Michel Cadot Messages: 65153Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Atif107 Messages: 5Registered: 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
 Michel Cadot Messages: 65153Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Frank Messages: 7880Registered: 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...
Re: calculate breakfast,lunch and dinner time between 2 times [message #228905 is a reply to message #228891] Wed, 04 April 2007 09:00
 Michel Cadot Messages: 65153Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Wow Frank! How do you guess?

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
 Atif107 Messages: 5Registered: 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
 Atif107 Messages: 5Registered: 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
 Michel Cadot Messages: 65153Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Atif107 Messages: 5Registered: 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
 Michel Cadot Messages: 65153Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Frank Messages: 7880Registered: March 2000 Senior Member
Ah,
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
 Michel Cadot Messages: 65153Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

And I had fun to do it, so I post it.

Regards
Michel

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

Report message to a moderator

 Previous Topic: moths and dates Next Topic: Help me build this string
Goto Forum:

Current Time: Tue Aug 22 23:31:44 CDT 2017

Total time taken to generate the page: 0.05215 seconds