i have a query and I want to calculate the number of sat sun total count in oracle, for example, I have a query pasted below there should be a total count of Saturday and Sunday, how can I achieve that please help, I really appreciate any help you can provide.

SELECT    TO_DATE('01-12-2022','dd-mm-yyyy') start_date , TO_DATE(sysdate) end_date
   FROM  dual;

CodePudding user response：

Don't use a row-generator to create a calendar (as it is very inefficient); just calculate the number by calculating the number of full weeks and then deal with the part weeks at the start and end of the range:

WITH range (start_date, end_date) AS (
  SELECT DATE '2022-12-01', TRUNC(SYSDATE) FROM DUAL
)
SELECT -- Number of full weeks
       (TRUNC(end_date, 'IW') - TRUNC(start_date, 'IW')) * 2/7
       -- Number of weekend days in final week
         GREATEST(end_date - TRUNC(end_date, 'IW') - 4, 0)
       -- Number of weekend days in before first week
       - GREATEST(start_date - TRUNC(start_date, 'IW') - 5, 0)
       AS weekend_day_count
FROM   range;

Which outputs:

fiddle

CodePudding user response：

One option is to create a calendar between these two dates and then count number of Saturdays and Sundays:

SQL> with
  2  test (start_date, end_date) as
  3    -- period
  4    (select date '2022-12-01', date '2022-12-29' from dual),
  5  calendar as
  6    -- calendar (all dates between START_DATE and END_DATE)
  7    (select start_date   level - 1 as datum
  8     from test
  9     connect by level <= end_date - start_date   1
 10    )
 11  -- number of Saturdays and Sundays
 12  select count(*)
 13  from calendar
 14  where to_char(datum, 'dy', 'nls_date_language = english') in ('sat', 'sun');

  COUNT(*)
----------
         8

SQL>

You'd change dates at line #4.

P.S. If you look at code MT0 posted and their objection that row generator is inefficient, that's true. Although both queries return the same result, timing is different. For example:

Period     01.01.2022 - 31.12.2022   01.01.1900 - 31.12.2022   01.01.0001 - 31.12.2022
------     -----------------------   -----------------------   -----------------------
LF         00:00:00.00               00:00:00.17               00:00:03.72
MT0        00:00:00.02               00:00:00.02               00:00:00.05

It is obvious that my timing gets worse with period length. If you're looking at one year or a century, the difference is mostly irrelevant. For 2000 years, the difference is huge!

However, if you consider debugging, from my own point of view, my code is easier to read: "select number of rows from the calendar where date is either saturday or sunday" - plain English.

On the other hand, the other code isn't that straightforward; truncate date to week, subtract them, multiply by 2/7 (why "2/7" and not 4/9?), add result returned by the GREATEST function minus 4 (why 4? Why not 7?), subtract GREATEST of something minus 5 (why 5? Why not 2?) - as I said, that's NOT easy to read nor understand.

Therefore, it depends on what you actually need, timing vs. readability. Pick one :)