I think Tom Kyte was the originator of this technique. It's not necessarily the fastest method, but it's super convenient - no table required.

Today I wanted to know how many weekdays a year, so I defined 365 rows on the fly using sysdate to turn these into each day of the year. Then I ran a simple select over this to aggregate my result

with years as ( select to_char(trunc(sysdate,'yy')+rownum-1,'dy') dy from dual connect by level <= 365 ) select count(*) ,sum(case when dy in ('sat','sun') then 1 end) weekends from yearsYou can see this demonstrated at livesql.oracle.com

https://livesql.oracle.com/apex/livesql/file/content_D6ZWKMK4O2IVMWGOAXIDEP61M.html

Never heard of it? I recommend you have a play. I've only dabbled myself, but if you don't have an environment to experiment with, this is free!

Wonder if you should use 366 in your connect by to account for leap years and then add a predicate to make sure that everything is in the same year.

ReplyDeleteDepending on the level of accuracy required, you could calculate the count dynamically

ReplyDeleteselect 365-28 days_in_year_minus_feb

,337+ extract(day from last_day(add_months(trunc(sysdate,'yy'),1))) as "2016a"

,337+ extract(day from last_day(add_months(trunc(date '2015-01-01','yy'),1))) as "2015a"

-- another method

,to_char(last_day(add_months(trunc(sysdate,'yy'),11)),'ddd') as "2016b"

,to_char(last_day(add_months(trunc(date '2015-01-01','yy'),11)),'ddd') as "2015b"

from dual

Probably a few other ways of doing this, depending on how you like your sausage made.

On that day I just wanted a guesstimate. Without Oracle sitting in front of me I probably should have just typed 365/7*5 in the calculator

In a regular year there are 52 weeks plus 1 day, in a leap year it's plus 2 days. You get 5 weekdays for every full week, so the only variance is the plus 1 or 2 days. The plus days are going to exactly match the day of the week of Jan 1st (and 2nd in a leap year).

ReplyDeleteThus the number of weekdays is:

52*5

+ case when to_char(first_of_year,'d') in ('1','7') then 0 else 1 end

+ case when first_of_year + interval '1' year

- first_of_year = 366

and to_char(trunc(date_in_year,'year')+1,'d') in ('1','7') then 0 else 1 end