Pages

▼

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:

  1. Tony29 November 2016 at 04:38:00 GMT+8

    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.

    ReplyDelete
  2. Scott Wesley29 November 2016 at 06:55:00 GMT+8

    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

    ReplyDelete
  3. Unknown3 December 2016 at 01:02:00 GMT+8

    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

    ReplyDelete