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
| card_id | phase_history | firstTimeIn |
|---|---|---|
| 8837 | Start | 2022-08-11T13:44:24+00:00 |
| 8837 | Compliance | 2022-08-11T13:44:25+00:00 |