Page 42 - SQL

JOIN tbl_hierarchy th ON (th.id = t.parent_id) -- CONNECT BY PRIOR id =
parent_id
WHERE th.CONNECT_BY_ISCYCLE = 0) -- NOCYCLE
SELECT th.*
--, REPLICATE(' ', (th."LEVEL" - 1) * 3) + th.name AS tbl_hierarchy
FROM tbl_hierarchy th
JOIN tbl CONNECT_BY_ROOT ON (CONNECT_BY_ROOT.id = th.root_id)
ORDER BY th.SYS_CONNECT_BY_PATH_name; -- ORDER SIBLINGS BY name

CONNECT BY features demonstrated above, with explanations:

• Clauses
CONNECT BY: Specifies the relationship that defines the hierarchy.
○
START WITH: Specifies the root nodes.
○
ORDER SIBLINGS BY: Orders results properly.
○
• Parameters
NOCYCLE: Stops processing a branch when a loop is detected. Valid hierarchies are
○
Directed Acyclic Graphs, and circular references violate this construct.
• Operators
PRIOR: Obtains data from the node's parent.
○
CONNECT_BY_ROOT: Obtains data from the node's root.
○
• Pseudocolumns

○ LEVEL: Indicates the node's distance from its root.
○ CONNECT_BY_ISLEAF: Indicates a node without children.
○ CONNECT_BY_ISCYCLE: Indicates a node with a circular reference.
• Functions

○ SYS_CONNECT_BY_PATH: Returns a flattened/concatenated representation of the
path to the node from its root.

Recursively generate dates, extended to include team rostering as example

DECLARE @DateFrom DATETIME = '2016-06-01 06:00'
DECLARE @DateTo DATETIME = '2016-07-01 06:00'
DECLARE @IntervalDays INT = 7

-- Transition Sequence = Rest & Relax into Day Shift into Night Shift
-- RR (Rest & Relax) = 1
-- DS (Day Shift) = 2
-- NS (Night Shift) = 3

;WITH roster AS
(
SELECT @DateFrom AS RosterStart, 1 AS TeamA, 2 AS TeamB, 3 AS TeamC
UNION ALL
SELECT DATEADD(d, @IntervalDays, RosterStart),
CASE TeamA WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 1 END AS TeamA,
CASE TeamB WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 1 END AS TeamB,
CASE TeamC WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 1 END AS TeamC
FROM roster WHERE RosterStart < DATEADD(d, -@IntervalDays, @DateTo)
)

SELECT RosterStart,
ISNULL(LEAD(RosterStart) OVER (ORDER BY RosterStart), RosterStart + @IntervalDays) AS

https://riptutorial.com/ 24