Tuesday, 29 November 2016

Answer with SQL: How many weekdays a year?

I'm a big fan of generating data with dual, using a perk of the connect by syntax.

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 years
You can see this demonstrated at livesql.oracle.com
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!


Tony said...

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.

Scott Wesley said...

Depending on the level of accuracy required, you could calculate the count dynamically
select 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

David Grimberg said...

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).

Thus the number of weekdays is:

+ 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