HeFg70yw

1DROP TABLE IF EXISTS dbo.sampleData;
2DROP TABLE IF EXISTS dbo.lookupCPT;
3GO
4
5CREATE TABLE sampleData 
6(
7    numRow bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_numRow PRIMARY KEY,
8    sDelimQty varchar(MAX) NULL,
9    sDelimCPT varchar(MAX) NULL
10)
11
12CREATE TABLE lookupCPT 
13(
14    numRow bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_numRowCPT PRIMARY KEY,
15    sCPTCode varchar(10) NULL,
16    decCPTRate decimal(16,2) NULL
17)
18
19INSERT [dbo].[lookupCPT] ([numRow], [sCPTCode], [decCPTRate]) 
20VALUES (1, N'123', CAST(4.00 AS Decimal(16, 2)))
21
22INSERT [dbo].[lookupCPT] ([numRow], [sCPTCode], [decCPTRate]) 
23VALUES (2, N'456', CAST(5.00 AS Decimal(16, 2)))
24
25INSERT [dbo].[lookupCPT] ([numRow], [sCPTCode], [decCPTRate]) 
26VALUES (3, N'789', CAST(7.00 AS Decimal(16, 2)))
27
28INSERT [dbo].[sampleData] ([numRow], [sDelimQty], [sDelimCPT]) 
29VALUES (1, N'1;2', N'123;789')
30
31INSERT [dbo].[sampleData] ([numRow], [sDelimQty], [sDelimCPT]) 
32VALUES (2, N'3', N'456')
33	
34WITH Qty_CTE (numRowQ, Qty) AS
35(
36    SELECT numRow, value
37    FROM sampleData
38    CROSS APPLY STRING_SPLIT(sDelimQty, ';')
39),
40CPT_CTE (numRowC, CPT) AS
41(
42    SELECT numRow, value
43    FROM sampleData
44    CROSS APPLY STRING_SPLIT(sDelimCPT, ';')
45)
46SELECT * 
47FROM sampleData 
48JOIN CPT_CTE c on c.numRowC = sampleData.numRow
49JOIN Qty_CTE q on q.numRowQ = sampleData.numRow
50	
51WITH Qty_CTE (numRowQ, Qty, RN) AS
52(
53    SELECT 
54        numRow, value, 
55        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN
56    FROM 
57        sampleData
58    CROSS APPLY 
59        STRING_SPLIT(sDelimQty, ';')
60),
61CPT_CTE (numRowC, CPT, CPTRate, RN) AS
62(
63    SELECT 
64        s.numRow, value as CPT, l.decCPTRate as CPTRate, 
65        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN
66    FROM 
67        sampleData s
68    CROSS APPLY 
69        STRING_SPLIT(sDelimCPT, ';')
70    JOIN 
71        lookupCPT l ON value = l.sCPTCode
72)
73SELECT 
74    numRow, sDelimCPT, sDelimQty, CPT, CPTRate, Qty, CPTRate * Qty as Total
75FROM 
76    sampleData 
77JOIN 
78    CPT_CTE c on c.numRowC = sampleData.numRow
79JOIN 
80    Qty_CTE q on q.numRowQ = sampleData.numRow AND c.RN = q.RN
81	
82WITH Qty_CTE (numRowQ, Qty, RN) AS
83(
84SELECT numRow, value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN
85FROM sampleData
86    CROSS APPLY STRING_SPLIT(sDelimQty, ';')
87),
88CPT_CTE (numRowC, CPT, RN) AS
89(
90SELECT numRow, value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN
91FROM sampleData
92    CROSS APPLY STRING_SPLIT(sDelimCPT, ';')
93)
94SELECT * FROM sampleData 
95JOIN CPT_CTE c on c.numRowC = sampleData.numRow
96JOIN Qty_CTE q on q.numRowQ = sampleData.numRow AND c.RN = q.RN