I am trying to create a table to manage parent/child relationships for a website.  This is not too difficult if it were for one site, however I would like to use this table for multiple sites, as well as subsites.

For example, I have a website ALPHA, a website BETA as well as a Website GAMMA, but the root of website GAMMA is also a page in website ALPHA.

A
|-B
|-|-C
|-|-D
|-|-|-E
|-|-F
|-G
|-H

M
|-N
|-O
|-P
|-|-Q
|-|-R

B
|-C
|-D
|-|-E
|-F

As you can see above, site GAMMA’s root of B is also a child of site ALPHA’s A.

This is my schema

IF OBJECT_ID (N'Sites_GetTreeFromPage') IS NOT NULL
	DROP FUNCTION Sites_GetTreeFromPage
GO

CREATE FUNCTION dbo.Sites_GetTreeFromPage (
	@Page_id uniqueidentifier
	, @Site_id uniqueidentifier = NULL
	, @StartDepth int = 0
	, @Depth int = NULL
) RETURNS @PageTree TABLE (
	Page_id uniqueidentifier PRIMARY KEY NOT NULL
	, L int NOT NULL
	, R int NOT NULL
	, D int NOT NULL
	, Site_id uniqueidentifier NOT NULL
) AS
BEGIN
	SELECT TOP 1 @Site_id = ISNULL(@Site_id, Site_id)
	FROM Sites_Pages
	WHERE Page_id = @Page_id
	ORDER BY D ASC

	INSERT INTO @PageTree (Page_id, L, R, D, Site_id)
	SELECT
		p.Page_id
		, tree.L
		, tree.R
		, tree.D
		, s.Site_id
	FROM
		Sites_Pages s_p
		JOIN Sites_Pages tree ON tree.Site_id = s_p.Site_id AND tree.L >= s_p.L AND tree.R <= s_p.R
		JOIN Pages p ON tree.Page_id = p.Page_id
		JOIN Sites s ON s_p.Site_id = s.Site_id
	WHERE
		s_p.Page_id = @Page_id
		AND s_p.Site_id = @Site_id
		AND tree.D >= s_p.D+@startDepth
		AND (@Depth IS NULL OR tree.D <= s_p.D+@Depth+@startDepth)

	RETURN
END
GO

IF OBJECT_ID (N'Sites_GetPageTree') IS NOT NULL
	DROP PROCEDURE Sites_GetPageTree
GO

CREATE PROCEDURE Sites_GetPageTree
	@Page_id uniqueidentifier
	, @Site_id uniqueidentifier = NULL
	, @StartDepth int = 0
	, @Depth int = NULL
AS
BEGIN
	SELECT
		p.Page_id
		, p.Title
		, REPLICATE('| - ', tree.D-@startDepth) + p.Title AS Tree
		, tree.L
		, tree.R
		, tree.D
		, s.Title AS Site
		, CASE WHEN tree.L = 1 THEN 1 ELSE 0 END AS IsRoot
		, CASE WHEN tree.R = tree.L + 1 THEN 1 ELSE 0 END AS IsLeaf
		, CASE WHEN tree.R > tree.L + 1 THEN 1 ELSE 0 END AS IsParent
	FROM
		dbo.Sites_GetTreeFromPage(@Page_id, @Site_id, @StartDepth, @Depth) tree
		JOIN Pages p ON tree.Page_id = p.Page_id
		JOIN Sites s ON tree.Site_id = s.Site_id
	ORDER BY
		tree.L
		, tree.R
END
GO

IF OBJECT_ID (N'Sites_GetPageChildren') IS NOT NULL
	DROP PROCEDURE Sites_GetPageChildren
GO

CREATE PROCEDURE Sites_GetPageChildren
	@Page_id uniqueidentifier
AS
BEGIN
	EXEC Sites_GetPageTree @Page_id = @Page_id, @StartDepth = 1, @Depth = 0
END
GO

IF OBJECT_ID (N'Sites_GetSiteTree') IS NOT NULL
	DROP PROCEDURE Sites_GetSiteTree
GO

CREATE PROCEDURE Sites_GetSiteTree
	@Site_id uniqueidentifier
AS
BEGIN
	DECLARE @Page_id uniqueidentifier
	SELECT
		@Page_id = RootPage_id
	FROM
		Sites
	WHERE
		Site_id = @Site_id

	EXEC Sites_GetPageTree @Page_id = @Page_id, @Site_id = @Site_id
END
GO

I am trying to write stored procedures to interact with the Sites_Pages table, but it has been hurting my brain.

The complication is that I would like to perform all operations without having to run different queries for different Sites. Also, I don’t want to use a temporary table to store rows if I want to move Pages from one parent to another.

I use this function when writing reports.

Use:

SELECT
  time.Date
  , Count(tab.ID) AS [Count]
FROM
  dbo.TimeDimension('1/1/2007 00:00:00', '12/31/2007 23:59:59', 'month', 1) time
  LEFT OUTER JOIN
   TempTable tab
  ON tab.DateCreated BETWEEN time.Date AND time.DateEnd

Here is the function. I don’t know if there is a better way to do this, if there is can someone tell me. As far as I know I am the only person to post a function like this.

CREATE FUNCTION [dbo].[TimeDimension] (
 @DateStart datetime
 , @DateEnd datetime
 , @Type varchar(20)
 , @IncrementValue int
)
RETURNS @Dates TABLE ( Date datetime , DateEnd datetime )
AS
BEGIN
 DECLARE @DateCurrent datetime, @NextDate datetime
 SET @DateCurrent = @DateStart

 WHILE (@DateCurrent <= @DateEnd)
 BEGIN
 SET @NextDate = CASE (@Type)
 WHEN 'year' THEN DateAdd(year, @IncrementValue, @DateCurrent)
 WHEN 'quarter' THEN DateAdd(quarter, @IncrementValue, @DateCurrent)
 WHEN 'month' THEN DateAdd(month, @IncrementValue, @DateCurrent)
 WHEN 'dayofyear' THEN DateAdd(dayofyear, @IncrementValue, @DateCurrent)
 WHEN 'day' THEN DateAdd(day, @IncrementValue, @DateCurrent)
 WHEN 'week' THEN DateAdd(week, @IncrementValue, @DateCurrent)
 WHEN 'weekday' THEN DateAdd(weekday, @IncrementValue, @DateCurrent)
 WHEN 'hour' THEN DateAdd(hour, @IncrementValue, @DateCurrent)
 WHEN 'minute' THEN DateAdd(minute, @IncrementValue, @DateCurrent)
 WHEN 'second' THEN DateAdd(second, @IncrementValue, @DateCurrent)
 WHEN 'millisecond' THEN DateAdd(millisecond, @IncrementValue, @DateCurrent)
 END

 INSERT INTO @Dates ( Date , DateEnd )
 VALUES ( @DateCurrent , DateAdd(second, -1, @NextDate) )

 SET @DateCurrent = @NextDate
 END
 RETURN
END