May 18, 2015 at 1:53 pm
All I have a table full of invoices and in that table there is a field named HCC. An invoice can contain multiple HCC's.
I also have a table named hierarchical codes (below is an example)
(COL1)HCC...........(COL2)If any of the HCCs in this column exist on an invoice along with the HCC in column 1 then use HCC listed in column one
1
2
3.......................4
4
6
8.......................9 ,10 ,11 ,12 ,13
9......................10 ,11 ,12 ,13
10.....................11 ,12 ,13
11.....................12 ,13
12......................13
13
18......................19 ,20 ,21 ,46 ,47
19.......................20 ,21
20.......................21
21
23
26
27
28
29
30
34......................35 ,36 ,37 ,38
35......................36 ,37 ,38
36.......................37
In my query I want to be able to set the HCC that gets returned based on the Hierarchy table.
So for example if Invoice 1 has the following entries in the invoice table
Invoice.......HCC
1.................34
1.................35
1.................36
1.................37
1.................38
I want to return invoice 1 with HCC set to 34 since the hierarchy for when any of these HCCs (35 ,36 ,37 ,38) exist on an invoice along with 34 is 34.
Any help is appreciated
May 18, 2015 at 6:31 pm
I don't know that I'm 100% clear on what you want but I think this should be pretty close...
Check this with the sample data and see if it's what you're looking for.
-- Test data
IF OBJECT_ID('tempdb..#H1') IS NOT NULL
DROP TABLE #H1;
CREATE TABLE #H1 (
CSV_String VARCHAR(200)
);
INSERT #H1 (CSV_String) VALUES
('1'),
('2'),
('3, 4'),
('4'),
('6'),
('8, 9 ,10 ,11 ,12 ,13'),
('9, 10 ,11 ,12 ,13'),
('10, 11 ,12 ,13'),
('11, 12 ,13'),
('12 ,13'),
('13'),
('18, 19 ,20 ,21 ,46 ,47'),
('19, 20 ,21'),
('20, 21'),
('21'),
('23'),
('26'),
('27'),
('28'),
('29'),
('30'),
('34, 35 ,36 ,37 ,38'),
('35, 36 ,37 ,38'),
('3, 37');
IF OBJECT_ID('tempdb..#Invoice') IS NOT NULL
DROP TABLE #Invoice;
CREATE TABLE #Invoice (
InvoiceNum INT,
HCC INT
);
INSERT #Invoice (InvoiceNum,HCC) VALUES
(1,34),
(1,35),
(1,36),
(1,37),
(1,38),
(2,9),
(2,10),
(2,11),
(2,12),
(2,13);
-- The actual solution
WITH SplitH AS (
SELECT
h2.BaseID,
h2.BaseNum,
sc.Item AS HCC,
COUNT(*) OVER (PARTITION BY h2.BaseID) AS NodeCount
FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) BaseID,
CAST(CASE WHEN h.CSV_String LIKE '%,%' THEN LEFT(h.CSV_String, PATINDEX('%,%', h.CSV_String) -1) ELSE h.CSV_String END AS INT) AS BaseNum,
REPLACE(h.CSV_String, ' ', '') AS CSV_String
FROM
#H1 h
) h2
CROSS APPLY (
SELECT sc.Item FROM dbo.SplitCSV8K(h2.CSV_String, ',') sc
) sc
)
SELECT
i.InvoiceNum,
sh.BaseNum
FROM
#Invoice i
JOIN SplitH sh
ON i.HCC = sh.HCC
WHERE
sh.NodeCount IN (SELECT COUNT(*) FROM #Invoice i2 GROUP BY i2.InvoiceNum)
GROUP BY
i.InvoiceNum,
sh.BaseNum
May 18, 2015 at 6:48 pm
brianconner (5/18/2015)
All I have a table full of invoices and in that table there is a field named HCC. An invoice can contain multiple HCC's.I also have a table named hierarchical codes (below is an example)
(COL1)HCC...........(COL2)If any of the HCCs in this column exist on an invoice along with the HCC in column 1 then use HCC listed in column one
1
2
3.......................4
4
6
8.......................9 ,10 ,11 ,12 ,13
9......................10 ,11 ,12 ,13
10.....................11 ,12 ,13
11.....................12 ,13
12......................13
13
18......................19 ,20 ,21 ,46 ,47
19.......................20 ,21
20.......................21
21
23
26
27
28
29
30
34......................35 ,36 ,37 ,38
35......................36 ,37 ,38
36.......................37
In my query I want to be able to set the HCC that gets returned based on the Hierarchy table.
So for example if Invoice 1 has the following entries in the invoice table
Invoice.......HCC
1.................34
1.................35
1.................36
1.................37
1.................38
I want to return invoice 1 with HCC set to 34 since the hierarchy for when any of these HCCs (35 ,36 ,37 ,38) exist on an invoice along with 34 is 34.
Any help is appreciated
Based on your sample data, if HCC=12 what do you return from the hierarchy table?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 20, 2015 at 7:09 am
If the invoice only contains HCC=12 then return 12 if the invoice contains HCC 12 and 13 then 12 trumps 13 so return 12. Basically if the invoice only contains the HCC in column one then return that HCC if the invoice contains the HCC in column 1 and any of the HCC's in column 2 then column 1 trumps.
May 21, 2015 at 7:40 pm
Not sure if you still need an answer to this, but here's a shot at it:
With Hierarchy (col1, col2) AS
(
SELECT 1, NULL
UNION ALL SELECT 2, NULL
UNION ALL SELECT 3, '4'
UNION ALL SELECT 4, NULL
UNION ALL SELECT 6, NULL
UNION ALL SELECT 8,'9 ,10 ,11 ,12 ,13'
UNION ALL SELECT 9, '10 ,11 ,12 ,13'
UNION ALL SELECT 10,'11 ,12 ,13'
UNION ALL SELECT 11,'12 ,13'
UNION ALL SELECT 12,'13'
UNION ALL SELECT 13, NULL
UNION ALL SELECT 18,'19 ,20 ,21 ,46 ,47'
UNION ALL SELECT 19,'20 ,21'
UNION ALL SELECT 20, '21'
UNION ALL SELECT 21, NULL
UNION ALL SELECT 23, NULL
UNION ALL SELECT 26, NULL
UNION ALL SELECT 27, NULL
UNION ALL SELECT 28, NULL
UNION ALL SELECT 29, NULL
UNION ALL SELECT 30, NULL
UNION ALL SELECT 34,'35 ,36 ,37 ,38'
UNION ALL SELECT 35,'36 ,37 ,38'
UNION ALL SELECT 36,'37'
),
AdjacencyList (col1, col2) AS
(
SELECT col1, col2=Item
FROM Hierarchy a
CROSS APPLY dbo.DelimitedSplit8K(col2,',') b
),
Invoices (Invoice, HCC) AS
(
SELECT 1, 34
UNION ALL SELECT 1, 35
UNION ALL SELECT 1, 36
UNION ALL SELECT 1, 37
UNION ALL SELECT 1, 38
UNION ALL SELECT 2, 12
UNION ALL SELECT 2, 13
UNION ALL SELECT 3, 26
UNION ALL SELECT 3, 27
)
SELECT Invoice, HCC=MIN(COALESCE(b.col1, c.col2, a.HCC))
FROM Invoices a
LEFT JOIN AdjacencyList b ON b.col1 = a.HCC
LEFT JOIN AdjacencyList c ON c.col2 = a.HCC
GROUP BY Invoice;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply