this is my stab at nested set. i generate on every set of the table.
i am looking to refactor and trim it down at some point.
if this is any help to anyone enjoy
or anyone wants to refactor it a bit more i would appreciate it
CREATE TABLE [dbo].[Organisation](
[OrganisationId] [int] IDENTITY(1,1) primary key NOT NULL,
[ParentId] [int] NULL,
[MinNest] [int] NULL,
[MaxNest] [int] NULL,
[Name] [varchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[UpdateOrgHierarchy] Script Date: 11/11/2013 14:17:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UpdateOrgHierarchy]
AS
BEGIN
begin transaction
SELECT top 1 *
into #tmp
from dbo.Organisation with (TABLOCKX)
;WITH orgCTE2 AS (
SELECT OrganisationId,
OrganisationId as CurrentOrgId,
1 as Total
FROM dbo.Organisation e
where ParentId IS NULL
UNION ALL
SELECT parent.OrganisationId,
e.OrganisationId as CurrentOrgId,
1 as Total
FROM dbo.Organisation e
INNER JOIN orgCTE2 parent ON parent.CurrentOrgId = e.ParentId
UNION ALL
SELECT e.OrganisationId,
e.OrganisationId as CurrentOrgId,
1 as Total
FROM dbo.Organisation e
INNER JOIN orgCTE2 parent ON parent.CurrentOrgId = e.ParentId
) , orgCTE3 AS (
select OrganisationId,
count(*) as [count]
from (select distinct OrganisationId, CurrentOrgId from orgCTE2) a
where Organisationid != CurrentOrgId
group by OrganisationId
) , orgCTE AS (
SELECT OrganisationId as TopOrgId,
OrganisationId as OrganisationId,
ParentId as ParentId,
1 as depth
FROM dbo.Organisation
UNION ALL
SELECT child.TopOrgId,
e.OrganisationId as OrganisationId,
e.ParentId,
depth + 1 as depth
FROM dbo.Organisation e
INNER JOIN orgCTE child ON child.parentId = e.OrganisationId
), orgCTE4 AS (
SELECT OrganisationId,
ParentId,
cast(null as INT) as parentOrganisationId,
OrganisationId as superparentOrganisationId,
',' + cast(OrganisationId as varchar(max)) + ',' as idstack,
1 as depth
FROM dbo.Organisation
WHERE parentid IS NULL
UNION ALL
SELECT e.OrganisationId,
e.ParentId,
parent.organisationid as parentOrganisationId,
superparentOrganisationId,
idstack + cast(e.OrganisationId as varchar(max)) + ',' as idstack,
depth + 1 as depth
FROM dbo.Organisation e
INNER JOIN orgCTE4 parent ON parent.organisationid = e.parentId
)
UPDATE org
set minNest = ((row_num-1) * 2) + 1 - (d-1),
maxNest = ((row_num-1) * 2) + 1 - (d-1)+(cnn*2)-1
from dbo.Organisation org
join (select c.*, e.maxdepth - c.depth + 1 as dep, isnull(par.[count] + 1,1) cnn, st.depth as d,
row_number() OVER (PARTITION BY 1 ORDER BY idstack) as row_num
from orgCTE c
join orgCTE4 st
on c.OrganisationId = st.OrganisationId
LEFT JOIN orgCTE3 par
on c.OrganisationId = par.OrganisationId
JOIN (
select max(depth) maxdepth, toporgid
from orgCTE c
group by toporgid) e
on c.toporgid = e.toporgid
where c.depth = 1) ln
on org.OrganisationId = ln.OrganisationId
COMMIT transaction
END