Calculate supernets to /16 in Oracle SQL

This bit of SQL calculates all the supernet addresses (to /16) for a given IP address (and mask) in Oracle. Supernet addresses are the 'containing' IP Addresses based on powers of 2, so a /30 address covers 2^(32-30) addresses (or 4 IPs). A /26 address covers 2^(32-26) addresses (64 IPs). This works on Oracle 9, 10 and 11. If your version does not support regexp_replace there are some commented fields that utilise some instr calls.

select 
lvl supernet_mask, 
ip, 
regexp_substr(ip,'^\d+\.\d+\.') || resl2 || '.' || resl supernet
from
(
select 
  lvl, ip, mask, q3,q4,
  case when lvl > 24 then
    null
  else 
    0 
  end supernet
  , 
  floor(q4/(power(2,32-lvl))) * power(2,32-lvl) resl 
  , 
  CASE WHEN lvl <= 24 then 
    floor(q3/(power(2,24-lvl))) * power(2,24-lvl) 
  ELSE 
    Q3
  END resl2
from
(
select lvl, ip, mask
, to_number(regexp_substr(ip,'\d+',1)) q1
, to_number(regexp_replace(ip,'(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})','\2')) q2
, to_number(regexp_replace(ip,'(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})','\3')) q3
, to_number(regexp_replace(ip,'(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})','\4')) q4
/* -- if your version does not support regexp_replace
, to_number(substr(ip,1,instr(ip,'.',1,1)-1)) q1
, to_number(substr(ip,instr(ip,'.',1,1)+1,instr(ip,'.',1,2 )-instr(ip,'.',1,1)-1 ))  q2
, to_number(substr(ip,instr(ip,'.',1,2)+1,instr(ip,'.',1,3 )-instr(ip,'.',1,2)-1 )) q3
, to_number(substr(ip,instr(ip,'.',1,3)+1)) q4
*/
from 
(
select 33 - level lvl,'203.63.104.108' ip,30 mask from dual connect by level <= 17 union
select 33 - level lvl,'203.63.104.12' ip,30 mask from dual connect by level <= 17 union
select 33 - level lvl,'203.63.104.124' ip,30 mask from dual connect by level <= 17 union
select 33 - level lvl,'203.63.104.144' ip,30 mask from dual connect by level <= 17 
) x
where mask >= lvl
) xx order by ip, lvl desc
) xxx

Beginning of financial year in Oracle

In Australia, the financial year runs from 1 July to 30 June. When doing financial reports, sometimes you need to know the financial year current at the time. Oracle (amazingly) makes it easy...


select
case
when mth < 4 then trunc(add_months(dt,-6),'Q') when mth < 7 then trunc(add_months(dt,-9),'Q') when mth < 10 then trunc(dt,'Q') else trunc(add_months(dt,-3),'Q') end fin_year /*return the beginning of the current financial year*/ from (select dt, to_number(to_char(dt ,'MM')) mth from ( select sysdate dt from dual) xx ) xx1

Business days between 2 dates in Oracle

A request I get occasionally is how many business days between 2 arbitrary dates. Oracle is, of course, not at all helpful here. So here is SQL to do it for you (2 methods). The second method relies on 'CONNECT BY' syntax being available so not less than Oracle 10 I'm afraid. The other one is more complex, but should work on any version.

The 2 different methods give slightly different results when the start dates are on Sat/Sun - differing interpretation of when you start counting business days from. The second method is a bit of a hack and only covers 18 months in the past and 18 months in the future. To expand that, change the 500 to the number of days ago you want to start from and the 1000 to the number of days you need to go forward from there. Ridiculously complex I know. but most of it is handling weekend dates.



select
abs( case when to_char(dt2,'d') > 5 then trunc(dt2,'d')+7 else dt2 end -
case when to_char(dt1,'d') > 5 then trunc(dt1,'d')+7 else dt1 end ) -
abs(trunc((trunc( case when to_char(dt2,'d') > 5 then trunc(dt2,'d')+7 else dt2 end ,'d') -
trunc(case when to_char(dt1,'d') > 5 then trunc(dt1,'d')+7 else dt1 end ,'d'))/7)
)*2
method1


, greatest((select count(*) from (select sysdate - 500 + level - 1 dd from dual connect by level< 1000) w where w.dd between dt1 and dt2 and to_char(w.dd,'d') < 6)-1,0) method2

,abs((trunc(dt1,'d')+7) - (trunc(dt2,'d')+7)) - trunc(abs((trunc(dt1,'d')+7) - (trunc(dt2,'d')+7))/7)*2 +
greatest(5 - to_char(dt1,'d'),0) -
greatest(5 - to_char(dt2,'d'),0)
method3


from
(
select to_date('24/08/2011','dd/mm/yyyy') dt1 , to_date('25/8/2011','dd/mm/yyyy') dt2 from dual
union select to_date('25/11/2011','dd/mm/yyyy') dt1 , to_date('28/11/2011','dd/mm/yyyy') dt2 from dual
union select to_date('13/12/2011','dd/mm/yyyy') dt1 , to_date('26/12/2011','dd/mm/yyyy') dt2 from dual
union select to_date('21/12/2011','dd/mm/yyyy') dt1 , to_date('11/01/2012','dd/mm/yyyy') dt2 from dual
union select to_date('22/12/2011','dd/mm/yyyy') dt1 , to_date('13/01/2012','dd/mm/yyyy') dt2 from dual
union select to_date('28/12/2011','dd/mm/yyyy') dt1 , to_date('25/01/2012','dd/mm/yyyy') dt2 from dual
) c
order by c.dt1