How can i find the end entry for a start entry in sql?

user_13879367 0
Viewed 72

I came across a table that has a similar structure to the below variable @t.
I want to find the end entry for each start entry but this gets difficult as entries can contain sub entries.

Any idea how i could find the EndEntryText for its cosponsoring StartEntryText using its position?

Visual Representation of the entries:

-- Start 1
--  Start 2
--  Stop 2
--  Start 3
--      Start 4
--          Start 5
--          Stop 5
--      Stop 4
--  Stop 3
-- Stop 1

Desired Output:

StartEntryText  EndEntryText
Start 1         Stop 1  
Start 2         Stop 2
Start 3         Stop 3
Start 4         Stop 4
Start 5         Stop 5

SQL:

-- Simplified Table Structure
DECLARE @t TABLE (EntryText varchar(16) NULL
    , EntryType varchar(16)
    , EntryIndex int NULL
)

-- Test Data
INSERT INTO @t(EntryText, EntryType, EntryIndex) 
SELECT d.EntryText
    , d.EntryType
    , d.EntryIndex
FROM 
(
    SELECT 'Start 1 ' AS EntryText, 1 AS EntryIndex, 'Open' AS EntryType
    UNION ALL SELECT 'Start 2' AS EntryText, 2 AS EntryIndex, 'Open' AS EntryType
    UNION ALL SELECT 'Stop 2' AS EntryText, 3 AS EntryIndex, 'Close' AS EntryType
    UNION ALL SELECT 'Start 3' AS EntryText, 4 AS EntryIndex, 'Open' AS EntryType
    UNION ALL SELECT 'Start 4' AS EntryText, 5 AS EntryIndex, 'Open' AS EntryType
    UNION ALL SELECT 'Start 5' AS EntryText, 6 AS EntryIndex, 'Open' AS EntryType
    UNION ALL SELECT 'Stop 5' AS EntryText, 7 AS EntryIndex, 'Close' AS EntryType
    UNION ALL SELECT 'Stop 4' AS EntryText, 8 AS EntryIndex, 'Close' AS EntryType
    UNION ALL SELECT 'Stop 3' AS EntryText, 9 AS EntryIndex, 'Close' AS EntryType
    UNION ALL SELECT 'Stop 1' AS EntryText, 10 AS EntryIndex, 'Close' AS EntryType
) d

-- TODO: Find EndEntryText
SELECT t.EntryText AS StartEntryText
    , NULL AS EndEntryText
FROM @t t
WHERE t.EntryType = 'Open'
tsql sql-server sql
1 Answers

Not a perfect solution but this should do what i need.

This basically works out the level of each entry by adding its ancestors together +1 for OPEN and -1 for CLOSE. Then gets the first CLOSE after each OPEN on the same level.

-- Workout Indent level for each entry in the hierarchy
SELECT t.EntryText
    , t.EntryIndex
    , t.EntryType
    , r.Indent
INTO #t
FROM @t t
CROSS APPLY
(
    SELECT SUM(CASE WHEN s.EntryType = 'OPEN' THEN 1 ELSE -1 END)  
        + CASE WHEN t.EntryType = 'OPEN' THEN -1 ELSE 0 END AS Indent
    FROM @t s 
    WHERE s.EntryIndex <= t.EntryIndex
) r

-- Work out OPEN entry for CLOSE entry in the same level
SELECT tOpen.EntryText AS StartEntryText
    , t.EntryText AS EndEntryText
FROM #t t
OUTER APPLY   
(
    SELECT TOP(1) e.EntryText
        , e.EntryIndex
    FROM #t e
    WHERE e.EntryIndex < t.EntryIndex
        AND e.EntryType = 'OPEN'
        AND e.Indent = t.Indent
    ORDER BY e.EntryIndex DESC
) tOpen
WHERE t.EntryType = 'CLOSE'
ORDER BY tOpen.EntryIndex

DROP TABLE #t
user_138793670
Related