2009-04-24

sys_connect_by_path in sql server

Oracle sys_connect_by_path

create table v(o int, k varchar(20));

insert into v values(1,'Barbados');
insert into v values(1,'Rio De Janeiro');
insert into v values(1,'Kapkaupunki');
insert into v values(2,'Hanko');
insert into v values(2,'Helsinki');


select o, max(sys_connect_by_path(k, '/')) as scbp
from (
select o
, k
, row_number() over (partition by o order by k) as rn
from v
)
start with rn = 1
connect by prior rn = rn - 1 and prior o = o
group by o
;

1 /Barbados/Kapkaupunki/Rio De Janeiro
2 /Hanko/Helsinki


SQL Server:

create table dbo.v(o int, k varchar(20))

insert into dbo.v values(1,'Barbados')
insert into dbo.v values(1,'Rio De Janeiro')
insert into dbo.v values(1,'Kapkaupunki')
insert into dbo.v values(2,'Hanko')
insert into dbo.v values(2,'Helsinki')

with x as (
select o
, k
, 1 as level
, row_number() over (partition by o order by k) as rn
, k as scbp
from dbo.v
), cte as (
select o
, k
, level
, rn
, '/' + cast(k as varchar(max)) as scbp
from x
where rn = 1
union all
select x.o
, x.k
, cte.level + 1 as level
, x.rn
, cte.scbp + '/' + cast(x.scbp as varchar(max)) as scbp
from x inner join cte on x.rn - 1 = cte.rn and x.o = cte.o
)
select o, max(scbp)
from cte
group by o
;

1 /Barbados/Kapkaupunki/Rio De Janeiro
2 /Hanko/Helsinki

2009-04-08

SUM over time

Time is continuous and time periods are modelled in SQL tables with validFrom validTill columns. I like to see continous pair stored as open ended, validFrom moment is included in the period, but validTill moment is excluded. This way it is possible to store the whole timeline.

Lets have a relation having such a pair

9.4.2009 10.4.2009 1
10.4.2009 12.4.2009 2
11.4.2009 14.4.2009 3

As one can see two last rows overlaps and for 11.4. there is all together 5 as a sum. Here is a SQL how to generate a sum for each moment of time.


with td as (
 select trunc(sysdate) + mod(level, 4) validFrom
      , trunc(sysdate) + mod(level, 8) * 2 validTill
      , level a
   from dual
connect by level < 4
)
select validFrom
     , nvl(lead(validFrom) over (order by validFrom)
         , to_date('22000101','yyyymmdd')) validTill
     , sumovertime
  from (
 select validFrom, max(sumover) sumovertime
   from (
  select a
   , case r when 1 then validFrom else validTill end validFrom
   , sum(case r when 1 then a else -a end) over (order by case r when 1 then validFrom else validTill end,r desc) sumover
   from td, (
  select rownum r from dual connect by level < 3
  )
 )
 group by validFrom
)
order by validFrom
;

9.4.2009 10.4.2009 1
10.4.2009 11.4.2009 2
11.4.2009 12.4.2009 5
12.4.2009 14.4.2009 3
14.4.2009 1.1.2200 0



About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.