# Thread: Find distinct day from date range

1. Registered User
Join Date
Jan 2004
Posts
153

## Unanswered: Validation logic

Hi All,

A hotel booking system has got a concept of room booking by date and day code. There are 10 day codes, like 0 = All Days, 1 = Monday, … , 7 = Sunday, 8 = Week Days and 9 = Week end.

Now system needs a validation where specified day code should cover the booking date. Like, say for a room, booking date has been defined as 19-may-2014 to 23-may-2014 ( week days), but for that if the day code has been defined as 9 (Week end), then it should failed that booking.

few examples :

Valid booking :
Booking date : 19-May-2014 to 23-May-2014
Day code : 8 or 0 or (1,2,3,4,5)

Booking_date table:
Booking_ref_no Booking_st_dt Booking_end_dt
1 19-may-2014 23-May-2014

Day_code table:
Booking_ref_no Day_code
1 8

Invalid booking :
Booking date : 19-May-2014 to 23-May-2014
Day code : 9

Booking_date table:
Booking_ref_no Booking_st_dt Booking_end_dt
2 19-may-2014 23-May-2014

Day_code table:
Booking_ref_no Day_code
2 9

It will be great if some can come out with logic.

Thanks with Regards,
J

2. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
What datatype is DAY_CODE?
Why does DAY_CODE exist?

3. Registered User
Join Date
Jan 2004
Posts
153
Hi anacedent,

Day_code is number type.

Thanks with Regards,
J

4. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
what should be DAY_CODE for check in 23 May & check out 25 May?

Why does DAY_CODE exist?
for what purpose does DAY_CODE provide?

5. Registered User
Join Date
Jan 2004
Posts
153

## Find distinct day from date range

Hi All,

From the below date range I would like to display the unique day/s.

Start_date End_date
01-may-2014 12-may-2014

The result should display :
Monday, Tuesday, Wednesday, Thrusday, Friday, Saturday, Sunday

Thanks with Regards,
J

6. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Is sequence relevant?

For example:
Start_date End_date
01-may-2014 12-may-2014

Is the result allowed?
Thrusday, Friday, Saturday, Sunday, Monday, Tuesday, Wednesday

7. Registered User
Join Date
Apr 2014
Location
Kentucky
Posts
630
Like tonkuma said...if sequence isnt relevent...
SELECT DISTINCT Format([DatePlaced],"dddd") AS DoW FROM tDetails;

8. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
http://www.dbforums.com/oracle/17017...ml#post6618542

since you don't answer my question to you, I won't answer any more question of yours

9. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
I merged these threads to make it easier to follow the conversation.

-PatP

10. Registered User
Join Date
Jan 2004
Posts
153
Originally Posted by anacedent
what should be DAY_CODE for check in 23 May & check out 25 May?

Why does DAY_CODE exist?
for what purpose does DAY_CODE provide?

for your example day_code as follows:

23-May (Friday) = 5
24-May (Friday) = 6
25-May (Friday) = 7

Day_code exist to define some target% for each day code:

11. Registered User
Join Date
Jan 2004
Posts
153
Originally Posted by tonkuma
Is sequence relevant?

For example:
Start_date End_date
01-may-2014 12-may-2014

Is the result allowed?
Thrusday, Friday, Saturday, Sunday, Monday, Tuesday, Wednesday

Sequence in not relevant.

12. Registered User
Join Date
Jan 2004
Posts
153
anacedent :
for your example day_code as follows:

23-May (Friday) = 5
24-May (Friday) = 6
25-May (Friday) = 7

Day_code exist to define some target% for each day code.

tonkuma:
Sequence in not relevant.

13. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
What is the relationship between BOOKING_DATE table & DAY_CODE table? one to one or one to many?

14. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Originally Posted by jayanta_deb
for your example day_code as follows:

23-May (Friday) = 5
24-May (Friday) = 6
25-May (Friday) = 7

Day_code exist to define some target% for each day code:
You wrote at first...
Valid booking :
Booking date : 19-May-2014 to 23-May-2014
Day code : 8 or 0 or (1,2,3,4,5)
So, I thought that one Day_code(or a list of codes like (1,2,3,4,5)) may be applied to whole period of a booking.
i.e.
Only valid Day_code for check in 23 May & check out 25 May
may be (5, 6, 7).

May I have something wrong?

15. Registered User
Join Date
Jan 2004
Posts
153
anacedent:

it could be many to many.

Like Room number 1 could be booked in following pattern for month of May:

Table : Room_date
Room_no Start_dt End_dt
1 01-may-2014 02-may-2014
1 08-may-2014 09-may-2016
1 15-may-2014 16-may-2014
1 22-may-2014 23-may-2014
1 29-may-2014 30-may-2014

And valid day_code for the above pattern as follows:
0 = All days
or
8 = week days
or
1 = Monday
2 = Tuesday

Table : Room_day_code
Room_no Day_code
1 1
1 2

Or
Room_no Day_code
1 0

Or
Room_no Day_code
1 8

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•