oracle 10g api to count how many saturday/sundays within 2 date parameters

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

however, your date values seems completely messed up?

SOLUTION

Gerwin Jansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

if you look at the calendar and look at my dates you will see what I mean

ie

Date_From Date_To Count
01/06/2015 06/01/2015 1 ===== There is physically 1 Saturday in this range

or

Date_From Date_To Count
01/06/2015 10/01/2015 2 ====== There is a Saturday sunday in this range

or

Date_From Date_To Count
11/06/2015 25/01/2015 4 ====== There is 2 Saturdays and 2 Sundays in this range

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Your dates should read:

01/06/2015 06/06/2015
01/06/2015 10/06/2015
11/06/2015 25/06/2015

ASKER

bugger !!!!!!!!!!!!!

ASKER

sorry guys my bad -_-

ASKER

not enough coffee

Thank you Guy for the hint with the calendar table, that's awesome ;-)

I have tried to find some alternative, and the simplest way IS the calendar table from what I see. especially as it is very reusable for many other scenarios/days etc ...

ASKER

ill take a look at this calendar option

Thankyou

Just want to add to Alexander's original query. It has a limitation that if the dates are more than a year apart it would not work. It also does a lot more work than it has to.

If you change it to this:

SELECT Count(CASE 
               WHEN To_char(date_col, 'D') IN ( '6', '7' ) THEN 1 
               ELSE NULL 
             END) 
FROM   (SELECT To_date('&start_date', 'dd/mm/yyyy') 
               + ROWNUM - 1 date_col 
        FROM   dual 
        CONNECT BY LEVEL <= To_date('&end_date', 'dd/mm/yyyy') - 
                            To_date('&start_date', 'dd/mm/yyyy') + 1);

It is slightly more efficient and can handle any date range. The where clause is removed as we can handle the limit with the connect by clause.

Thanks johnsone for making it "nice" ;-) It was just a quick & dirty one off the top of my head...

You can create a function to simply loop through and count

CREATE OR REPLACE FUNCTION count_weekend_days(p_start_date IN DATE, p_end_date IN DATE)
    RETURN NUMBER
    DETERMINISTIC
IS
    v_date  DATE := p_start_date;
    v_count INTEGER := 0;
BEGIN
    WHILE v_date <= p_end_date
    LOOP
        IF TO_CHAR(v_date, 'Dy') IN ('Sat', 'Sun')
        THEN
            v_count := v_count + 1;
        END IF;

        v_date := v_date + 1;
    END LOOP;

    RETURN v_count;
END;

I didn't actually look at the Saturday and Sunday determination in the query that Alexander posted. Do not use the numbered day of the week like that. It depends on NLS settings and could give incorrect results. Use a case where the day is spelled out like DAY or DY.

The spelled out versions are also dependent on NLS settings; but I think those are easier to determine correctness than numbers.

Of course they do, what was I thinking. I was looking at the documentation for format models and it clearly states that D is determined by NLS_TERRITORY and people do get tripped up on that setting and the changing start of the week. However, in a completely separate section, it clearly states that DAY and DY depend on NLS_LANGUAGE. Why put them in different places?

My guess is that you are better off going with the spelled out version as it is probably less confusing, but be aware that a user changing the value of NLS_LANGUAGE in their session will render your count useless.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

sdstuber, The function is more what I am after but there is a slight problem with it.

If I enter dates

01/06/2015 to 06/06/2015 I get a result of 1 which correct
if I enter
01/06/2015 to 07/06/2015 I get a result of 2 which correct
but if I
01/06/2015 to 10/06/2015 I get a result of 3 which incorrect

Can you let me know where the problem is

Thanks

select count_weekend_days(date '2015-06-01', date '2015-06-10') from dual

I get 2 which is correct

I get 2 with either of the versions I have posted above.
What are you using?

ASKER

mmmmm I run the exact same and I get 3 !!!!!!!!!

what is the function code you are using? If I have an NLS error, what are your NLS settings?

ASKER

I am on a dutch server but don't think this matters.

btw I have altered the function to allow for dutch naming

1      NLS_LANGUAGE      DUTCH
2      NLS_TERRITORY      THE NETHERLANDS
3      NLS_CURRENCY
4      NLS_ISO_CURRENCY      THE NETHERLANDS
5      NLS_NUMERIC_CHARACTERS      ,.
6      NLS_CALENDAR      GREGORIAN
7      NLS_DATE_FORMAT      DD-MM-RR
8      NLS_DATE_LANGUAGE      DUTCH
9      NLS_SORT      DUTCH
10      NLS_TIME_FORMAT      HH24:MI:SSXFF
11      NLS_TIMESTAMP_FORMAT      DD-MM-RR HH24:MI:SSXFF
12      NLS_TIME_TZ_FORMAT      HH24:MI:SSXFF TZR
13      NLS_TIMESTAMP_TZ_FORMAT      DD-MM-RR HH24:MI:SSXFF TZR
14      NLS_DUAL_CURRENCY
15      NLS_COMP      BINARY
16      NLS_LENGTH_SEMANTICS      BYTE

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

>>>> btw I have altered the function to allow for dutch naming

if you used the 2nd form I posted, you shouldn't need to do that.

But again - what is the code you are running?

Please post the exact function code you are using, with any modifications
And the exact test query you used that generated 3

you and I are obviously doing something different but I can only see what I'm doing, show me what you are doing and I can diagnose

ASKER

this is it exactly

CREATE OR REPLACE FUNCTION count_weekend_days(p_start_date IN DATE, p_end_date IN DATE)
RETURN NUMBER
DETERMINISTIC
IS
v_date DATE := p_start_date;
v_count INTEGER := 0;
BEGIN
WHILE v_date <= p_end_date
LOOP
IF TO_CHAR(v_date, 'DY') IN ('ZO', 'MA')
THEN
v_count := v_count + 1;
END IF;

v_date := v_date + 1;
END LOOP;

RETURN v_count;
END;

select count_weekend_days(date '2015-06-01', date '2015-06-10') from dual

using this code

CREATE OR REPLACE FUNCTION count_weekend_days(p_start_date IN DATE, p_end_date IN DATE)
    RETURN NUMBER
    DETERMINISTIC
IS
    v_date           DATE := p_start_date;
    v_count          INTEGER := 0;

    -- Derive the Weekend abbreviations regardless of NLS settings by using known dates
    v_saturday_abbrv VARCHAR2(5) := TO_CHAR(DATE '2015-06-06', 'Dy'); -- a known Saturday
    v_sunday_abbrv   VARCHAR2(5) := TO_CHAR(DATE '2015-06-07', 'Dy'); -- a known Sunday
BEGIN
    WHILE v_date <= p_end_date
    LOOP
        IF TO_CHAR(v_date, 'Dy') IN (v_saturday_abbrv, v_sunday_abbrv)
        THEN
            v_count := v_count + 1;
        END IF;

        v_date := v_date + 1;
    END LOOP;

    RETURN v_count;
END;
/

I get these results

SQL> select value  from v$nls_parameters where parameter = 'NLS_LANGUAGE';

VALUE
----------------------------------------------------------------
AMERICAN

SQL> select TO_CHAR(DATE '2015-06-06', 'Dy'), TO_CHAR(DATE '2015-06-07', 'Dy') from dual;

TO_ TO_
--- ---
Sat Sun

SQL> select count_weekend_days(date '2015-06-01', date '2015-06-10') from dual;

COUNT_WEEKEND_DAYS(DATE'2015-06-01',DATE'2015-06-10')
-----------------------------------------------------
                                                    2

SQL> alter session set nls_language ='DUTCH';

Session altered.

SQL> select value  from v$nls_parameters where parameter = 'NLS_LANGUAGE';

VALUE
----------------------------------------------------------------
DUTCH

SQL> select TO_CHAR(DATE '2015-06-06', 'Dy'), TO_CHAR(DATE '2015-06-07', 'Dy') from dual;

TO TO
-- --
Za Zo

SQL> select count_weekend_days(date '2015-06-01', date '2015-06-10') from dual;

COUNT_WEEKEND_DAYS(DATE'2015-06-01',DATE'2015-06-10')
-----------------------------------------------------
                                                    2

It almost appears that somehow the function is searching for Monday and Sunday since they would the only days that would occur 3 time between, 01/06/2016 and 10/06/2015. Are you sure your function created the abbreviations for a known Saturday and Sunday?

>>> IF TO_CHAR(v_date, 'DY') IN ('ZO', 'MA')

That doesn't appear to be correct

Sat,Sun should be Za, Zo in Dutch, not Zo,Ma

You are counting Sundays and Mondays, and June 1 - June 10 has 3 of those, not 2.

If you count Saturdays and Sundays (Za,Zo) you'll get 2, not 3

If you use the 2nd form I posted, then the code is NLS-impervious.
You don't need to change anything to have it recognize Saturday and Sunday, regardless of what those days are named in other languages.

You might want to change the variable names and comments to correspond to native language, but no functional changes are needed

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

That is the issue. These are the days of the week in Dutch -
maandag Monday
dinsdag Tuesday
woensdag Wednesday
donderdag Thursday
vrijdag Friday
zaterdag Saturday
zondag Sunday

So the abbreviations should be ('ZA','ZO') and not ('ZO','MA')

I've got to teach these arthritic hands to type faster :-)

ASKER

OMG how can I be so stupid.

I am so sorry that I never picked this up.

Think I need to go back to bed .

ASKER

it magically works now ;)

I just checked, the maximum abbreviation length is 10 characters (NLS_LANGUAGE=DIVEHI), so, using the 2nd form expand the variables.

This version really should be NLS-impervious, at least as of 12.1.0.2

CREATE OR REPLACE FUNCTION count_weekend_days(p_start_date IN DATE, p_end_date IN DATE)
    RETURN NUMBER
    DETERMINISTIC
IS
    v_date           DATE := p_start_date;
    v_count          INTEGER := 0;

    -- Derive the Weekend abbreviations regardless of NLS settings by using known dates
    v_saturday_abbrv VARCHAR2(10) := TO_CHAR(DATE '2015-06-06', 'Dy'); -- a known Saturday
    v_sunday_abbrv   VARCHAR2(10) := TO_CHAR(DATE '2015-06-07', 'Dy'); -- a known Sunday
BEGIN
    WHILE v_date <= p_end_date
    LOOP
        IF TO_CHAR(v_date, 'Dy') IN (v_saturday_abbrv, v_sunday_abbrv)
        THEN
            v_count := v_count + 1;
        END IF;

        v_date := v_date + 1;
    END LOOP;

    RETURN v_count;
END;
/

In the interest of performance. Wouldn't it be faster to keep the work in the SQL engine rather than having PL/SQL do all the work? Seems like we are creating a looping structure where we don't really need one. It can be simplified to this:

CREATE OR replace FUNCTION Count_weekend_days(p_start_date IN DATE, 
                                              p_end_date   IN DATE) 
RETURN NUMBER deterministic 
IS 
  v_count          INTEGER := 0; 
  -- Derive the Weekend abbreviations regardless of NLS settings by using known dates 
  v_saturday_abbrv VARCHAR2(10) := To_char(DATE'2015-06-06', 'Dy'); 
  -- a known Saturday 
  v_sunday_abbrv   VARCHAR2(10) := To_char(DATE '2015-06-07', 'Dy'); 
-- a known Sunday 
BEGIN 
    SELECT Count(1) 
    INTO   v_count 
    FROM   (SELECT To_char(p_start_date + ROWNUM - 1, 'Dy') dname 
            FROM   dual 
            CONNECT BY LEVEL <= p_end_date - p_start_date + 1) 
    WHERE  dname IN ( v_saturday_abbrv, v_sunday_abbrv ); 

    RETURN v_count; 
END; 

/

Not that either way is right or wrong. Just different. If this is a function that would get called a lot, you may see a performance difference without the looping.

actually no, the pl/sql should be faster because switching from pl/sql to sql consumes resources.
the connect by is still a looping structure

here's a simple test to confirm

count_weekend_days - this is the final version, with 10 character abbreviations I posted above
count_weekend_dayssql - this is the code posted by johnsone

DECLARE
    c_loop_count CONSTANT INTEGER := 10;
    v_time1               TIMESTAMP;
    v_time2               TIMESTAMP;
    v_time3               TIMESTAMP;
    v_diff1               NUMBER;
    v_diff2               NUMBER;

    v_dummy               NUMBER;
BEGIN
    v_time1 := SYSTIMESTAMP;

    FOR n IN 1 .. c_loop_count
    LOOP
        FOR x IN 1 .. 100
        LOOP
            FOR y IN x + 1 .. x + 100
            LOOP
                v_dummy := count_weekend_days(SYSDATE + x, SYSDATE + y);
            END LOOP;
        END LOOP;
    END LOOP;

    v_time2 := SYSTIMESTAMP;

    FOR n IN 1 .. c_loop_count
    LOOP
        FOR x IN 1 .. 100
        LOOP
            FOR y IN x + 1 .. x + 100
            LOOP
                v_dummy := count_weekend_dayssql(SYSDATE + x, SYSDATE + y);
            END LOOP;
        END LOOP;
    END LOOP;

    v_time3 := SYSTIMESTAMP;

    v_diff1 :=
          EXTRACT(SECOND FROM (v_time2 - v_time1))
        + EXTRACT(MINUTE FROM (v_time2 - v_time1)) * 60
        + EXTRACT(HOUR FROM (v_time2 - v_time1)) * 3600
        + EXTRACT(DAY FROM (v_time2 - v_time1)) * 86400;

    v_diff2 :=
          EXTRACT(SECOND FROM (v_time3 - v_time2))
        + EXTRACT(MINUTE FROM (v_time3 - v_time2)) * 60
        + EXTRACT(HOUR FROM (v_time3 - v_time2)) * 3600
        + EXTRACT(DAY FROM (v_time3 - v_time2)) * 86400;

    DBMS_OUTPUT.put_line(RPAD('-', 80, '-'));
    DBMS_OUTPUT.put_line('Test1: ' || v_time1 || '   ' || v_time2 || '   ' || v_diff1);
    DBMS_OUTPUT.put_line('Test2: ' || v_time2 || '   ' || v_time3 || '   ' || v_diff2);
    DBMS_OUTPUT.put_line('Test1 took: ' || v_diff1 / v_diff2 || ' times as long as Test2');
    DBMS_OUTPUT.put_line(RPAD('-', 80, '-'));
END;

which, on a small server I tested on, produced these results

--------------------------------------------------------------------------------
Test1: 10-JUN-15 10.46.43.917757 AM   10-JUN-15 10.46.47.057447 AM   3.13969
Test2: 10-JUN-15 10.46.47.057447 AM   10-JUN-15 10.46.55.494899 AM   8.437452
Test1 took: .3721135243199013161793394498718333449482 times as long as Test2
--------------------------------------------------------------------------------

So, the purely pl/sql is significantly faster than the pl/sql and sql mix version.

Well now I got on a performance kick. Best is to not do any looping of any kind and just calculate it directly.
We know there are 2 weekdays per full week, so use that as the base and then handle the end-points that may or may not fall on a weekend.

CREATE OR REPLACE FUNCTION count_weekend_days(p_start_date IN DATE, p_end_date IN DATE)
    RETURN NUMBER
    DETERMINISTIC
IS
    v_start          DATE := TRUNC(p_start_date);
    v_end            DATE := TRUNC(p_end_date);
    v_date           DATE := v_start;

    -- Derive the Weekend abbreviations regardless of NLS settings by using known dates
    v_saturday_abbrv VARCHAR2(10) := TO_CHAR(DATE '2015-06-06', 'Dy'); -- a known Saturday
    v_sunday_abbrv   VARCHAR2(10) := TO_CHAR(DATE '2015-06-07', 'Dy'); -- a known Sunday

    v_start_sunday   DATE;
    v_end_sunday     DATE := NEXT_DAY(v_end - 7, v_sunday_abbrv);

    v_count          INTEGER := 0;
BEGIN
    IF v_start = v_end AND TO_CHAR(v_start, 'Dy') IN (v_saturday_abbrv, v_sunday_abbrv)
    THEN
        v_count := 1;
    ELSIF v_start < v_end
    THEN
        IF TO_CHAR(v_date, 'Dy') = v_saturday_abbrv
        THEN
            v_count := v_count + 1;
            v_date := v_date + 1;
        END IF;

        IF v_date <= v_end AND TO_CHAR(v_date, 'Dy') = v_sunday_abbrv
        THEN
            v_count := v_count + 1;
            v_date := v_date + 1;
        END IF;

        v_start_sunday := NEXT_DAY(v_date - 7, v_sunday_abbrv);
        v_count := v_count + (v_end_sunday - v_start_sunday) / 7 * 2;

        IF TO_CHAR(v_end, 'Dy') = v_saturday_abbrv
        THEN
            v_count := v_count + 1;
        END IF;
    END IF;

    RETURN v_count;
END;

This is MUCH more efficient than any of the previous options...
Test 1 is my previous best code, test 2 is the code in this post.

--------------------------------------------------------------------------------
Test1: 10-JUN-15 11.24.38.666404 AM   10-JUN-15 11.24.41.749738 AM   3.083334
Test2: 10-JUN-15 11.24.41.749738 AM   10-JUN-15 11.24.42.401349 AM   .651611
Test1 took: 4.73186302870884622880829206382335473158 times as long as Test2
--------------------------------------------------------------------------------

I recommend NOT using any of the accepted answers above.

Instead - use the code you can find in http:#a40822772

Everything else is either wrong or needlessly slow and resource intensive

Sorry to put you on the performance chase.

Surprising (to me anyway) that the PL/SQL looping is faster. I would have thought the internal looping in the SQL would be faster.

>>> Sorry to put you on the performance chase.

haha, no problem, It was kind of bugging me that I used looping from my very first post. It seemed cumbersome even if simple from the very beginning.

the pl/sql vs sql seems expected to me. SQL is a whole engine unto itself. So you have to switch from pl/sql mode to sql mode and then do the parsing (even if its soft parsing) and invoke the execution engine and then whatever code happens inside the connect by and then you have to loop through those results of that connect by to do a count and then return that sql count back into pl/sql.

the pure pl/sql compiles down to almost nothing. It's just a simple integer iterator. If we compiled it down to c instead of the default p-code, there would be little to it.

and then the last version is nothing but some simple math and incrementing and that definitely makes sense that it should be faster and significantly so.

I guess that I was forgetting that your original didn't have a SQL call in it at all. Good experiment though.

I fully agree that the pure pl/sql is the best in performance ...

but it has some serious limitations:
* it is limited to Saturday/Sunday calculations;
* as soon as you want to include holidays in the maths, or count only sundays, or only holidays etc ...
=> the only flexible way will be the calendar table.
* the calendar table is "portable as is" to any other db platform (oh well, yes, sorry, who would ever want to port from oracle to what's-the-name-of-that-other-vendor.... ?!?! ), while the pl/sql code would need a serious code rewrite.

so far for my 3c rant :)

* it is limited to Saturday/Sunday calculations;

-- true, but that was the requested functionality. Any function that hits a table will have to reference Saturday/Sunday or a "weekend_flag" or some similar indicator thus creating the same "limitation". Of course the code can me modified, whether pl/sql or sql or a combination. I don't see this as a positive or a negative, it's implicit in defining a function to return the requested result.

* as soon as you want to include holidays in the maths, or count only sundays, or only holidays etc ...

-- if the requirements change the function code must change, so yes, of course. I don't think I'm catching your intent there. For holidays specifically, those can often be derived by code, but a lookup table can be easier. I'd have to see specific cases to determine whether io vs computation would be faster. My guess is computation would win but it would require testing to verify. With this function, the "easyness" isn't really a factor. None of the variations are complex.

=> the only flexible way will be the calendar table.

--- that's definitely not true, it's simply another option. It does make some coding easier but I rarely take into account the developer's convenience. The end functionality trumps a small amount of extra effort I might need to put into the code.

* the calendar table is "portable as is" to any other db platform (oh well, yes, sorry, who would ever want to port from oracle to what's-the-name-of-that-other-vendor.... ?!?! ), while the pl/sql code would need a serious code

--- A table is portable, and the sql to reference it would likely be too. It's even more rare that I consider cross-platform portability when I write my code. I write a common api and then underneath each platform gets its own code with whatever form is best for it. I don't see trying to be generic as feature.

However, if this function is seldomly invoked then a portable, simple table-driven design is definitely a viable alternative. I made the assumption it would be called frequently so I wanted to optimize. If my assumption is wrong, there is no harm in having it be fast and the code, in all the forms is pretty simple. So I wouldn't be concerned about future flexibility. It's easily extensible as is.