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
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!



3 comments:

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:

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