SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version AS [SQL Version]


SQL Version


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
 Oct  8 2022 05:58:25 
 Copyright (C) 2022 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE cards (
card_id int,
phase_history varchar(500),
firstTimeIn varchar(500)
)
INSERT INTO cards (card_id,phase_history, firstTimeIn)
VALUES
('8837','[Start, Compliance, Contact, Down]', '[2022-08-11T13:44:24+00:00, 2022-08-11T13:44:25+00:00, 2022-08-25T17:37:19+00:00, 2022-08-11T13:44:26+00:00]'),
('6596','[Start, Compliance, Contact, Down]', '[2022-03-11T13:44:24+00:00, 2022-04-11T13:44:25+00:00, 2022-04-25T17:37:19+00:00, 2022-04-11T13:44:26+00:00]'),
('2416','[Start, Contact, Up]', '[2022-08-02T02:22:31+00:00, 2022-08-02T02:22:31+00:00, 2022-08-02T12:13:32+00:00]'),
('5424','[Start, Compliance, Contact, Lead, Down]', '[2022-09-01T12:51:24+00:00, 2022-09-01T12:51:25+00:00, 2022-09-01T13:25:52+00:00, 2022-09-01T12:51:26+00:00, 2022-09-01T16:47:31+00:00]')
4 rows affected

SELECT * FROM cards


card_id
phase_history
firstTimeIn


8837
[Start, Compliance, Contact, Down]
[2022-08-11T13:44:24+00:00, 2022-08-11T13:44:25+00:00, 2022-08-25T17:37:19+00:00, 2022-08-11T13:44:26+00:00]

6596
[Start, Compliance, Contact, Down]
[2022-03-11T13:44:24+00:00, 2022-04-11T13:44:25+00:00, 2022-04-25T17:37:19+00:00, 2022-04-11T13:44:26+00:00]

2416
[Start, Contact, Up]
[2022-08-02T02:22:31+00:00, 2022-08-02T02:22:31+00:00, 2022-08-02T12:13:32+00:00]

5424
[Start, Compliance, Contact, Lead, Down]
[2022-09-01T12:51:24+00:00, 2022-09-01T12:51:25+00:00, 2022-09-01T13:25:52+00:00, 2022-09-01T12:51:26+00:00, 2022-09-01T16:47:31+00:00]


/*  For SQL Server 2016 (compatibility level 130+) and higher */
SELECT d.card_id, a.phase_history, a.firstTimeIn
FROM cards d
CROSS APPLY (
   SELECT 
   TRIM('[ ]' FROM c.[value]) AS phase_history, 
   TRIM('[ ]' FROM s.[value]) AS firstTimeIn
   FROM OPENJSON(CONCAT('["', REPLACE(d.phase_history, ',', '","'), '"]')) c
   LEFT OUTER JOIN OPENJSON(CONCAT('["', REPLACE(d.firstTimeIn, ',', '","'), '"]')) s 
      ON c.[key] = s.[key]
) a 
WHERE a.phase_history IN ('Start', 'Compliance')
AND card_id = 8837


card_id
phase_history
firstTimeIn


8837
Start
2022-08-11T13:44:24+00:00

8837
Compliance
2022-08-11T13:44:25+00:00


/*  For SQL Server 2014 */
WITH PhaseHistoryCTE AS (
    SELECT 
        d.card_id,
        LTRIM(RTRIM(x.value('.', 'VARCHAR(50)'))) AS phase_history,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM 
        cards d
    CROSS APPLY (
        SELECT CAST('<x>' + REPLACE(SUBSTRING(d.phase_history, 2, LEN(d.phase_history) - 2), ',', '</x><x>') + '</x>' AS XML) AS xmlValues
    ) AS a
    CROSS APPLY xmlValues.nodes('/x') AS SplitLoad(x)
),
FirstTimeInCTE AS (
    SELECT 
        d.card_id,
        LTRIM(RTRIM(x.value('.', 'VARCHAR(50)'))) AS firstTimeIn,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM 
        cards d
    CROSS APPLY (
        SELECT CAST('<x>' + REPLACE(SUBSTRING(d.firstTimeIn, 2, LEN(d.firstTimeIn) - 2), ',', '</x><x>') + '</x>' AS XML) AS xmlValues
    ) AS b
    CROSS APPLY xmlValues.nodes('/x') AS SplitBOL(x)
)
SELECT 
    p.card_id, 
    p.phase_history, 
    f.firstTimeIn
FROM 
    PhaseHistoryCTE p
LEFT OUTER JOIN 
    FirstTimeInCTE f
ON 
    p.rn = f.rn
WHERE 
    p.phase_history IN ('Start', 'Compliance')
AND 
    p.card_id = 8837;


card_id
phase_history
firstTimeIn


8837
Start
2022-08-11T13:44:24+00:00

8837
Compliance
2022-08-11T13:44:25+00:00

card_id	phase_history	firstTimeIn
8837	[Start, Compliance, Contact, Down]	[2022-08-11T13:44:24+00:00, 2022-08-11T13:44:25+00:00, 2022-08-25T17:37:19+00:00, 2022-08-11T13:44:26+00:00]
6596	[Start, Compliance, Contact, Down]	[2022-03-11T13:44:24+00:00, 2022-04-11T13:44:25+00:00, 2022-04-25T17:37:19+00:00, 2022-04-11T13:44:26+00:00]
2416	[Start, Contact, Up]	[2022-08-02T02:22:31+00:00, 2022-08-02T02:22:31+00:00, 2022-08-02T12:13:32+00:00]
5424	[Start, Compliance, Contact, Lead, Down]	[2022-09-01T12:51:24+00:00, 2022-09-01T12:51:25+00:00, 2022-09-01T13:25:52+00:00, 2022-09-01T12:51:26+00:00, 2022-09-01T16:47:31+00:00]

card_id	phase_history	firstTimeIn
8837	Start	2022-08-11T13:44:24+00:00
8837	Compliance	2022-08-11T13:44:25+00:00