Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Spotting/flagging weekends for a comparison during two dates 4

Status
Not open for further replies.
adventurous1

adventurous1

Programmer
Mar 5, 2004
64
US
Another brain teaser from a newbie...

I need to compare two dates, a TRIPSTARTDATE and TRIPENDDATE, to return a value to populate a Y/N flag in a column called INCL_WEEKEND.

I am trying to accomplish this in a SQL statement but figure that it is better suited towards a PL/SQL function.

Can anyone assist with the PL/SQL function?

Big Thanks...

Adventurous

Examples:

(1) TRIPSTARTDATE='02-MAR-04' (a Tuesday)
TRIPENDDATE='08-MAR-04' (the following Monday)
>>In this case, the INCL_WEEKEND flag should be set to "Y", as a weekend day (Friday, Saturday, Sunday) falls in between these dates.

(2) TRIPSTARTDATE='02-MAR-04' (a Tuesday)
TRIPENDDATE='04-MAR-04' (a Thursday)
>>In this case, the INCL_WEEKEND flag should be set to "N", as the trip period does not contain a weekend day (Friday, Saturday, Sunday) between these dates.

(3) TRIPSTARTDATE='05-MAR-04' (a Friday)
TRIPENDDATE='07-MAR-04' (a Sunday)
>>In this case, the INCL_WEEKEND flag should be set to "Y", as the trip period does contain a weekend day (Friday, Saturday, Sunday) between these dates.

(4) TRIPSTARTDATE='06-MAR-04' (a Saturday)
TRIPENDDATE='09-MAR-04' (a Tuesday)
>>In this case, the INCL_WEEKEND flag should be set to "Y", as the trip period does contain a weekend day (Friday, Saturday, Sunday) between these dates.
 
Sort by date Sort by votes
Adventurous,

Here is your function (which I saved to a script file named "test_weekends.sql"):

Code: 
create or replace function get_weekend_flag (beg_date date, end_date date)
	return varchar2 is
begin
	if 	to_char(beg_date,'DY') in ('SAT','SUN') or
		to_char(end_date,'DY') in ('SAT','SUN') or
		least(next_day(trunc(beg_date),'SAT'),next_day(trunc(beg_date),'SUN')) <= end_date then
		return 'Y';
	else
		return 'N';
	end if;
end;
/

...and here are your tests and proofs of concept
Tuesday through Monday:
Code: 
SQL> @test_weekends
Enter the Trip Start Date (DD-MON-YY): 02-MAR-04
Enter the Trip End   Date (DD-MON-YY): 08-MAR-04

Weekend
Flag
--------------------------------------------------------------------------
Tuesday, March 2, 2004 through Monday, March 8, 2004 includes a weekend day.
Tuesday through Thursday:
Code: 
SQL> @test_weekends
Enter the Trip Start Date (DD-MON-YY): 02-MAR-04
Enter the Trip End   Date (DD-MON-YY): 04-MAR-04

Weekend
Flag
--------------------------------------------------------------------------
Tuesday, March 2, 2004 through Thursday, March 4, 2004 does not include a weekend day.
Friday through Sunday:
Code: 
SQL> @test_weekends
Enter the Trip Start Date (DD-MON-YY): 05-MAR-04
Enter the Trip End   Date (DD-MON-YY): 07-MAR-04

Weekend
Flag
--------------------------------------------------------------------------
Friday, March 5, 2004 through Sunday, March 7, 2004 includes a weekend day.
Saturday through Tuesday:
Code: 
SQL> @test_weekends
Enter the Trip Start Date (DD-MON-YY): 06-MAR-04
Enter the Trip End   Date (DD-MON-YY): 09-MAR-04

Weekend
Flag
--------------------------------------------------------------------------
Saturday, March 6, 2004 through Tuesday, March 9, 2004 includes a weekend day.
And an additional important test: Sunday through Tuesday:
Code: 
SQL> @test_weekends
Enter the Trip Start Date (DD-MON-YY): 07-MAR-04
Enter the Trip End   Date (DD-MON-YY): 09-MAR-04

Weekend
Flag
--------------------------------------------------------------------------
Sunday, March 7, 2004 through Tuesday, March 9, 2004 includes a weekend day.

Let me know if this is what you wanted.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA @ 01:34 (01Apr04) UTC (aka "GMT" and "Zulu"), 18:34 (31Mar04) Mountain Time)
 
Upvote 0 Downvote
Mufasa,

I will test shortly. I have to say that you are incredible.

Adventurous
 
Upvote 0 Downvote
Adventurous,

If I were truly that good, I would have included the SELECT statement that REALLY comprises the "test_weekends.sql" script:

Code: 
accept tripstartdate	prompt "Enter the Trip Start Date (DD-MON-YY): "
accept tripenddate	prompt "Enter the Trip End Date (DD-MON-YY): "
col a heading "Weekend|Flag"
select decode(get_weekend_flag
		(to_date('&tripstartdate','dd-MON-rr')
		,to_date('&tripenddate','dd-MON-rr'))
		,'Y',to_char('&tripstartdate','Day, Month DD, YYYY "through "')||
		     to_char('&tripstartdate','Day, Month DD, YYYY "includes a weedend day."')
		,'N',to_char('&tripstartdate','Day, Month DD, YYYY "through "')||
		     to_char('&tripstartdate','Day, Month DD, YYYY "does not include a weedend day."')
	     )
from dual;

The function definition IS NOT part of the "test_weekends.sql" script.

Sorry.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA @ 01:44 (01Apr04) UTC (aka "GMT" and "Zulu"), 18:44 (31Mar04) Mountain Time)
 
Upvote 0 Downvote
Mufasa,

You are good. No worries since I will need to execute the function as part of a SQL statement. I cant wait to test this tomorrow. Thanks again for all your hard work.

Adventurous
 
Upvote 0 Downvote
A slight variation (which includes checking for stupid user trick #48 - Reversing the dates):

Code: 
create or replace function weekend(p_start IN DATE, p_end IN DATE) RETURN varchar2 IS
   l_first DATE := p_start;
   l_last  DATE := p_end;
BEGIN
   -- MAKE SURE THE FIRST DATE IS THE EARLIEST
   IF (p_start > p_end) THEN
      l_first := p_end;
      l_last := p_start;
   END IF;
   IF (next_day(l_first - 1,'Saturday') <= l_last) 
       OR 
      (next_day(l_first - 1,'Sunday') <= l_last) THEN
      RETURN 'Weekend Included';
   ELSE
      RETURN 'Weekend NOT Included';
   END IF;
END;

Testing yields:
Code: 
SQL> select weekend('1-APR-04','2-APR-04') from dual;

WEEKEND('1-APR-04','2-APR-04')
-----------------------------------------------------------
Weekend NOT Included

SQL> select weekend('1-APR-04','3-APR-04') from dual;

WEEKEND('1-APR-04','3-APR-04')
------------------------------------------------------------
Weekend Included

SQL> select weekend('4-APR-04','3-APR-04') from dual;

WEEKEND('4-APR-04','3-APR-04')
------------------------------------------------------------
Weekend Included

SQL> select weekend('4-APR-04','5-APR-04') from dual;

WEEKEND('4-APR-04','5-APR-04')
------------------------------------------------------------
Weekend Included

SQL> select weekend('5-APR-04','5-APR-04') from dual;

WEEKEND('5-APR-04','5-APR-04')
------------------------------------------------------------
Weekend NOT Included

SQL> select weekend('5-APR-04','9-APR-04') from dual;

WEEKEND('5-APR-04','9-APR-04')
-----------------------------------------------------------
Weekend NOT Included
If you use this code, you'll probably want to pick a more compact response for ease of use - something like 0/1, Y/N, etc.

Elbert, CO
0839 MST
 
Upvote 0 Downvote
SantaMufasa,
Since a weekend, by adventurous1's definition, includes Friday, I thought I would post my attempt at this puzzle. If the resulting count is greater than 0 then a "weekend" day is in the date range.
Code: 
select count(*)
from   (select to_char((rownum-1) + 
               to_date('02-MAR-04','dd-mon-yy'),'DY') DOW
        from   user_tables
        where  rownum between 1 and
                (to_date('08-MAR-04','dd-mon-yy') -
                 to_date('02-MAR-04','dd-mon-yy')+1))
where  DOW in ('FRI','SAT','SUN');

select count(*)
from   (select to_char((rownum-1) + 
               to_date('02-MAR-04','dd-mon-yy'),'DY') DOW
        from   user_tables
        where  rownum between 1 and
                (to_date('04-MAR-04','dd-mon-yy') -
                 to_date('02-MAR-04','dd-mon-yy')+1))
where  DOW in ('FRI','SAT','SUN');

select count(*)
from   (select to_char((rownum-1) +
               to_date('05-MAR-04','dd-mon-yy'),'DY') DOW
        from   user_tables
        where  rownum between 1 and
                (to_date('07-MAR-04','dd-mon-yy') - 
                 to_date('05-MAR-04','dd-mon-yy')+1))
where  DOW in ('FRI','SAT','SUN');

select count(*)
from   (select to_char((rownum-1) +
               to_date('06-MAR-04','dd-mon-yy'),'DY') DOW
        from   user_tables
        where  rownum between 1 and
                (to_date('09-MAR-04','dd-mon-yy') -
                 to_date('06-MAR-04','dd-mon-yy')+1))
where  DOW in ('FRI','SAT','SUN');

The results:
Code: 
SQL> @tryit

  COUNT(*)
----------
         3


  COUNT(*)
----------
         0


  COUNT(*)
----------
         3


  COUNT(*)
----------
         2

Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw
Consultant/Custom Forms & PL/SQL - Oracle 8i & 9i - Windows 2000
 
Upvote 0 Downvote
BJ,

I must be suffering from "Old-Timers' Disease"...I can't see where Adventurous specified Fridays as part of the weekend. Can you help me out here and point me to the "Friday as a weekend day" spec?

Carp,

Clever code simplification !

Cheers,

[santa]Mufasa
(aka Dave of Sandy, Utah, USA @ 16:57 (01Apr04) UTC (aka "GMT" and "Zulu"), 09:57 (01Apr04) Mountain Time)
 
Upvote 0 Downvote
Not just you, I read it 3 times before I spotted it!
>>In this case, the INCL_WEEKEND flag should be set to "Y", as a weekend day (Friday, Saturday, Sunday) falls in between these dates.
Click to expand...

Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw
Consultant/Custom Forms & PL/SQL - Oracle 8i & 9i - Windows 2000
 
Upvote 0 Downvote
Thanks to all of you that have helped me through another one! :)

Adventurous
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TheLazyPig
  • Locked
  • Question
Button not aligned after click
Replies
0
Views
303
TheLazyPig
TheLazyPig
KeyserSoze
  • Locked
  • Question
TOAD for Oracle - Icons
Replies
1
Views
504
Andrzejek
Andrzejek
Chopstik
  • Locked
  • Question
How to identify a package based on a code snippet 1
Replies
1
Views
961
Andrzejek
Andrzejek
Andrzejek
  • Locked
  • Question
Get part of a string with SQL 1
Replies
2
Views
1K
Andrzejek
Andrzejek
Andrzejek
  • Locked
  • Question
Format a number - TO_CHAR() 2
Replies
5
Views
981
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top