CODEWITHSUNDEEP
oracle-databaseoracle11g
myhouse
myhouse

Reputation: 1991

Oracle 11g count how many Mondays and Thursdays from a starting date until now

In Oracle I would like to build a query that counts how many Mondays and Thursdays have gone by from a starting date counting both together.

For example:

from 20-MAY-18 to today (Thursday 31-MAY-18)

There has been the total count of 4 Mondays and Thursdays (2 Mondays and 2 Thursdays) have passed by from May 20 until today. I want to 4 in return in this query. If this query ran yesterday (Wednesday 30-MAY-18), I want to get 3 counts.

I am thinking subtracting the weeks from today minus 20-MAY-18

Maybe something like this:

select ( TRUNC( SYSDATE, 'IW' ) - TRUNC( TO_DATE('20-MAY-18'), 'IW' ) ) / 7 * 2 from dual;

I times it by two because of the monday and thursday. If the thurday hasn't come up yet then it gives the wrong result because of this week.

I cannot figure out to count the weekdays that have past this week to times it by the week count.

I need to be able also to change the days selected and not get stuck with only Monday and Thursday.

Upvotes: 0

Views: 116

Answers (2)

MT0
MT0

Reputation: 167832

You can use this to calculate the value:

Calculate the number of full days between the Monday of the week containing the start date and the Monday of this week:

( TRUNC( SYSDATE, 'IW' ) - TRUNC( start_date, 'IW' ) )

Divide it by 7 to get the number of full weeks:

 / 7

Multiply it by the number of days you want to match per week

* 2

Then adjust it to remove the Mondays and Thursdays that have been counted before the start date. This is 0 adjustment if the start day is Monday, 1 for Tuesday-Thursday and 2 for Friday-Sunday:

- DECODE( TRUNC( start_date ) - TRUNC( start_date, 'IW' ),
          0, 0, -- Monday
          1, 1, -- Tuesday
          2, 1, -- Wednesday
          3, 1, -- Thursday
          4, 2, -- Friday
          5, 2, -- Saturday
          6, 2  -- Sunday
        )

Then adjust it to include the days from the Monday of this current week to today:

+ DECODE( TRUNC( SYSDATE ) - TRUNC( SYSDATE, 'IW' ),
          0, 1, -- Monday
          1, 1, -- Tuesday
          2, 1, -- Wednesday
          3, 2, -- Thursday
          4, 2, -- Friday
          5, 2, -- Saturday
          6, 2  -- Sunday
        )

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( start_date ) AS
SELECT DATE '2018-05-20' + LEVEL - 1
FROM DUAL
CONNECT BY DATE '2018-05-20' + LEVEL - 1 <= SYSDATE;

Query 1:

SELECT start_date,
       TO_CHAR( start_date, 'DY' ) As day,
       ( TRUNC( SYSDATE, 'IW' ) - TRUNC( start_date, 'IW' ) ) / 7 * 2
       - DECODE( TRUNC( start_date ) - TRUNC( start_date, 'IW' ),
                0, 0, 1, 1, 2, 1, 3, 1, 4, 2, 5, 2, 6, 2 )
       + DECODE( TRUNC( SYSDATE ) - TRUNC( SYSDATE, 'IW' ),
                0, 1, 1, 1, 2, 1, 3, 2, 4, 2, 5, 2, 6, 2 ) AS num_mon_and_thurs
FROM   table_name

Results:

|           START_DATE | DAY | NUM_MON_AND_THURS |
|----------------------|-----|-------------------|
| 2018-05-20T00:00:00Z | SUN |                 4 |
| 2018-05-21T00:00:00Z | MON |                 4 |
| 2018-05-22T00:00:00Z | TUE |                 3 |
| 2018-05-23T00:00:00Z | WED |                 3 |
| 2018-05-24T00:00:00Z | THU |                 3 |
| 2018-05-25T00:00:00Z | FRI |                 2 |
| 2018-05-26T00:00:00Z | SAT |                 2 |
| 2018-05-27T00:00:00Z | SUN |                 2 |
| 2018-05-28T00:00:00Z | MON |                 2 |
| 2018-05-29T00:00:00Z | TUE |                 1 |
| 2018-05-30T00:00:00Z | WED |                 1 |
| 2018-05-31T00:00:00Z | THU |                 1 |

What if I want to change it instead of monday and thursday to be tuesday, wednesday, saturday? How do I do that?

SELECT start_date,
       TO_CHAR( start_date, 'DY' ) As day,
       ( TRUNC( SYSDATE, 'IW' ) - TRUNC( start_date, 'IW' ) ) / 7 * 3
       - DECODE( TRUNC( start_date ) - TRUNC( start_date, 'IW' ),
                0, 0, 1, 0, 2, 1, 3, 2, 4, 2, 5, 2, 6, 3 )
       + DECODE( TRUNC( SYSDATE ) - TRUNC( SYSDATE, 'IW' ),
                0, 0, 1, 1, 2, 2, 3, 2, 4, 2, 5, 3, 6, 3 ) AS num_tue_wed_sat
FROM   table_name;

SQLFIDDLE

Upvotes: 1

Art
Art

Reputation: 5782

Maybe this will help. You can add more logic to it. I'm not sure why this has to return 3 if ran on May 30th. What is he idea? Your start/end dates are the same and number of days between also the same no matter when you run it. What should happen if his run May 30?

WITH mon_thur AS
(
SELECT to_date('20-MAY-2018') + LEVEL-1 start_date
     , to_date('31-MAY-2018') end_date
     , to_date('31-MAY-2018') - to_date('20-MAY-2018') days_between
 FROM dual
CONNECT BY LEVEL <= to_date('31-MAY-2018') - to_date('20-MAY-2018')+1  -- days_between+1
)
SELECT count(*) number_of_mon_thu FROM
(
SELECT start_date, to_char(start_date, 'D') d_date 
  FROM mon_thur
)
WHERE d_date IN (2, 4)
/

The output is 4. You can replace the 'D' format to 'DY' and replace 2, 4 with WHERE d_date IN ('MON', 'THU').

Upvotes: 1

Related Questions