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.comhttps://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:
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.
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
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
Post a Comment