Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Querying a Supersession Two Column Table with Multiple Supersessions in both Columns Expand / Collapse
Author
Message
Posted Wednesday, January 29, 2014 5:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 5:49 PM
Points: 11, Visits: 50
Hi Guys,

I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 )

The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:

Supersessions Table
----------------------

RTC5756 | STC9191
SFP500160 | STC9191
STC9191 | STC2951
STC3765 | STC9191
STC8572 | STC9191
STC9150 | STC9191


Source Table
----------------------

SFP500160 | KIT BRAKE LINING | 58.02



The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160.

I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?

Any help would be greatly appreciated. If I need to make anything clearer, please let me know.

Thanks
Post #1536140
Posted Wednesday, January 29, 2014 9:34 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
Are you looking for something like this:

WITH Supercessions (PartNo, PNPrior) AS
(
SELECT 'RTC5756','STC9191'
UNION ALL SELECT 'SFP500160','STC9191'
UNION ALL SELECT 'STC9191','STC2951'
UNION ALL SELECT 'STC3765','STC9191'
UNION ALL SELECT 'STC8572','STC9191'
UNION ALL SELECT 'STC9150','STC9191'
),
[Source] (PartNo, PartDesc, Price) AS
(
SELECT 'SFP500160','KIT BRAKE LINING',58.02
),
PartsHierarchy AS
(
SELECT n=1, OriginalPart=b.PartNo, a.PartNo, PNPrior, PartDesc, Price
FROM Supercessions a
JOIN [Source] b ON a.PartNo = b.PartNo
UNION ALL
SELECT n+1, OriginalPart, b.PartNo, b.PNPrior, PartDesc, Price
FROM PartsHierarchy a
JOIN Supercessions b ON a.PNPrior = b.Partno
)
SELECT *
FROM PartsHierarchy;


Note how I've set up your sample data for you in a consumable form. Forum posting etiquette suggests you should do this to help your helpers help you. I've been kind today.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1536156
Posted Thursday, January 30, 2014 5:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 5:49 PM
Points: 11, Visits: 50
Hi,

Many thanks for your prompt reply and patience.

What I'm really trying to achieve is, a user inputs a part number, ie STC8572 (the customer does not necessarily have access to or know any of the other part numbers associated with this number), the lookup table then checks for supersessions of that part number - in this case:

(oldest number) RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 (newest number)

The query then returns the newest number as the number to be used, along with the part information, which it knows based on the 'source' table, with a message such as 'The part number you have entered has been superseded to SFP500160'.

The query will also return all of the previous numbers associated with that number, to be consumed by the application for cross reference.

I hope this clarifies things a little bit.



Post #1536263
Posted Sunday, February 2, 2014 5:17 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
p.barwick (1/30/2014)
Hi,

(oldest number) RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 (newest number)



Is the above exactly the way you want to see the output from the query? In other words, do you want a single row returned as your results? Are the part numbers in one column or more than one? If the latter, how many supersessions are possible?



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1537169
Posted Monday, February 3, 2014 4:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 5:49 PM
Points: 11, Visits: 50
Hi,

I would like the result returned as


'SFP500160','KIT BRAKE LINING',58.02,{array of previous and current part numbers}

ie

'SFP500160','KIT BRAKE LINING',58.02,{ 'RTC5756','STC8572','STC3765','STC9150','STC9191','SFP500160' }


or two split results, whichever is easier


'SFP500160','KIT BRAKE LINING',58.02

'RTC5756'
'STC8572'
'STC3765'
'STC9150'
'STC9191'
'SFP500160'

Baring in mind,that we are starting the search with only one of these part numbers known to the end user.



The part supersession trail length can be quite long, I think the longest so far is 177. They average 5-10 in most instances though.

The way the supersessions table is provided to me is a reason for my headache. Usually, you would expect the search to filter "old part number > new part number > old part number > new part number" and so forth but in this instance, the later part number can be in either the "OriginalPartNumber" column or the "NewPartNumber" column. With more than 70,000 rows and a monthly update of the Supersessions table in this format, it would be nice to have a clean and easy way to find the part number supersession trail.

Thanks.


Post #1537553
Posted Monday, February 3, 2014 6:41 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
Not sure this is exactly what will work for you but it may be close. Note that I added a PN (STC3333) to see if it would put it at the beginning of the chain (oldest).

CREATE TABLE #Supersessions
(
PartNo VARCHAR(20)
,PNPrior VARCHAR(20)
);

INSERT INTO #Supersessions
SELECT 'RTC5756', 'STC9191'
UNION ALL SELECT 'SFP500160','STC9191'
UNION ALL SELECT 'STC9191','STC2951'
UNION ALL SELECT 'STC3765','STC9191'
UNION ALL SELECT 'STC8572','STC9191'
UNION ALL SELECT 'STC9150','STC9191'
UNION ALL SELECT 'STC8572','STC3333';

CREATE TABLE #Source
(
PartNo VARCHAR(20)
,PartDesc VARCHAR(100)
,Price MONEY
);

INSERT INTO #Source
SELECT 'SFP500160','KIT BRAKE LINING',58.02;

DECLARE @N INT = 1;

SELECT n=@N, OriginalPart=b.PartNo, a.PartNo, PNPrior
INTO #Hierarchy
FROM #Supersessions a
JOIN #Source b ON a.PartNo = b.PartNo;

WHILE @@ROWCOUNT > 0
BEGIN
SELECT @N = @N + 1;

INSERT INTO #Hierarchy
SELECT @N, OriginalPart, PartNo, PNPrior
FROM
(
SELECT OriginalPart, b.PartNo, b.PNPrior
FROM #Hierarchy a
JOIN #Supersessions b ON a.PNPrior = b.PNPrior
UNION ALL
SELECT OriginalPart, b.PartNo, b.PNPrior
FROM #Hierarchy a
JOIN #Supersessions b ON a.PNPrior = b.PartNo
UNION ALL
SELECT OriginalPart, b.PartNo, b.PNPrior
FROM #Hierarchy a
JOIN #Supersessions b ON a.PartNo = b.PNPrior
UNION ALL
SELECT OriginalPart, b.PartNo, b.PNPrior
FROM #Hierarchy a
JOIN #Supersessions b ON a.PartNo = b.PartNo
EXCEPT
SELECT OriginalPart, PartNo, PNPrior
FROM #Hierarchy
) a;
END

--SELECT *
--FROM #Supersessions;

SELECT PartNo, PartDesc, Price
,Supercessions=REPLACE(STUFF(
(
SELECT '-' + PN
FROM
(
SELECT OriginalPart, PN, rn=ROW_NUMBER() OVER (PARTITION BY PN ORDER BY N)
,orb=CASE WHEN OriginalPart = PN THEN 0 ELSE N END, N
FROM #Hierarchy a
CROSS APPLY (VALUES (PartNo), (PNPrior)) b (PN)
) b
WHERE rn = 1 AND a.PartNo = b.OriginalPart
ORDER BY orb DESC
FOR XML PATH('')
), 1, 1, ''), '-', ' > ')
FROM #Source a;

GO
DROP TABLE #Source;
DROP TABLE #Supersessions;
DROP TABLE #Hierarchy;


There are several key issues here:
1. The need to eliminate a PN from the chain when it's already been resolved, hence I switched from the recursive CTE to the WHILE LOOP with EXCEPT.
2. At a particular level (and all of your PNs just below STC9191 fall into this category), I am not sure of the sort criteria. Perhaps you've got another column (like a date the PN was introduced) to help here.
3. It was not real clear from your initial description whether your Supersessions table lists the prior PN in the second column or whether those columns are reversed. I called that column PNPrior based on my assumption.
Edit: Oh yeah, and one more thing.
4. I'm not clear if you're trying to resolve a PN passed in by the UI (like @PartNo) or whether you always want to resolve from the Source table. I think I may have gotten that part wrong. But you should be able to work around that by using @PartNo in a WHERE clause of the first SELECT INTO #Hierarchy (instead of the JOIN).



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1537582
Posted Monday, February 3, 2014 7:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 5:49 PM
Points: 11, Visits: 50
Hi,

Thanks for your hard work, it's appreciated. As it stands, executing that query results in:

[Err] 42000 - [SQL Server] Must declare the scalar variable "@N"

just after

JOIN #Source b ON a.PartNo = b.PartNo


The supersessions chain appears to have no logic to the old and new part number columns. Old and new numbers are mixed up in both. Unfortunately, I only have two columns worth of data, there is no time period logged for the part number change. Largely, because I suppose it's mostly irrelevant to the end user.

The important thing, is that a list of previous part numbers is returned, regardless of order, for a point of reference.

And yes, I will be pushing data from the UI in to the SELECT statement - purely the one part number that they are searching for though.

For instance, if I searched for STC3333 in your example, it would redirect me to SFP500160 and then provide the UI with an array of previous part numbers linked to that latest number, as well as information from the [source] table for SFP500160.

The more I think about it, the more of a nightmare I can see it becoming, with no point of reference on the supersession columns ...

Can I send you the complete table, which I can export as either a CSV or TAB delimited file?

Thanks.
Post #1537590
Posted Monday, February 3, 2014 7:48 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
p.barwick (2/3/2014)
Hi,

Thanks for your hard work, it's appreciated. As it stands, executing that query results in:

[Err] 42000 - [SQL Server] Must declare the scalar variable "@N"

...

Can I send you the complete table, which I can export as either a CSV or TAB delimited file?

Thanks.


Before you send more data, fix the error by including the DECLARE of @N that is in the code I provided you right after the table CREATEs/INSERTs. See if what you're getting is close to what you need.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1537591
Posted Monday, February 3, 2014 8:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 5:49 PM
Points: 11, Visits: 50
Color me stupid but where exactly do I need to declare it?

Thanks.
Post #1537592
Posted Monday, February 3, 2014 8:17 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
p.barwick (2/3/2014)
Color me stupid but where exactly do I need to declare it?

Thanks.


It is already declared in the code I provided. You need to do it before the first SELECT into the temp #Hierarchy table. Let's do this a step at a time (new solution that may be closer to what you need):

First, your sample data with the row I added for STC3333:

CREATE TABLE #Supersessions
(
PartNo VARCHAR(20)
,PNPrior VARCHAR(20)
);

INSERT INTO #Supersessions
SELECT 'RTC5756', 'STC9191'
UNION ALL SELECT 'SFP500160','STC9191'
UNION ALL SELECT 'STC9191','STC2951'
UNION ALL SELECT 'STC3765','STC9191'
UNION ALL SELECT 'STC8572','STC9191'
UNION ALL SELECT 'STC9150','STC9191'
UNION ALL SELECT 'STC8572','STC3333';

CREATE TABLE #Source
(
PartNo VARCHAR(20)
,PartDesc VARCHAR(100)
,Price MONEY
);

INSERT INTO #Source
SELECT 'SFP500160','KIT BRAKE LINING',58.02;


Now the DECLARE (with the new variable @MYPartNo which gets passed in by the UI):

DECLARE @N      INT = 1
,@MyPartNo VARCHAR(20) = 'STC3333';


First SELECT to create the #Hierarchy table, plus the WHILE loop to fully populate it:

SELECT n=@N, OriginalPart=@MyPartNo
,a.PartNo, PNPrior
INTO #Hierarchy
FROM #Supersessions a
WHERE a.PartNo = @MyPartNo OR a.PNPrior = @MyPartNo;

WHILE @@ROWCOUNT > 0
BEGIN
SELECT @N = @N + 1;

INSERT INTO #Hierarchy
SELECT @N, OriginalPart, PartNo, PNPrior
FROM
(
SELECT OriginalPart, b.PartNo, b.PNPrior
FROM #Hierarchy a
JOIN #Supersessions b ON a.PNPrior = b.PNPrior
UNION ALL
SELECT OriginalPart, b.PartNo, b.PNPrior
FROM #Hierarchy a
JOIN #Supersessions b ON a.PNPrior = b.PartNo
UNION ALL
SELECT OriginalPart, b.PartNo, b.PNPrior
FROM #Hierarchy a
JOIN #Supersessions b ON a.PartNo = b.PNPrior
UNION ALL
SELECT OriginalPart, b.PartNo, b.PNPrior
FROM #Hierarchy a
JOIN #Supersessions b ON a.PartNo = b.PartNo
EXCEPT
SELECT OriginalPart, PartNo, PNPrior
FROM #Hierarchy
) a;
END


Now we take a look at what's in the #Hierarchy table (so you can see what I did) and then do a SELECT that might be close to what you want:

SELECT * FROM #Hierarchy;

WITH AllParts AS
(
SELECT PN, rn, N, OriginalPart, m
FROM
(
SELECT OriginalPart, PN, rn=ROW_NUMBER() OVER (PARTITION BY PN ORDER BY N), N, m
FROM #Hierarchy a
CROSS APPLY (VALUES (1, PartNo), (0, PNPrior)) b (m, PN)
) b
WHERE rn = 1
)
SELECT b.PartNo, PartDec=MAX(b.PartDesc), Price=MAX(b.Price)
,Supercessions=REPLACE(STUFF(
(
SELECT '-' + PN
FROM AllParts c
ORDER BY N, m, CASE WHEN c.PN = b.PartNo THEN '' ELSE c.PN END DESC
FOR XML PATH('')
), 1, 1, ''), '-', ' > ')
FROM AllParts a
JOIN #Source b ON b.PartNo = a.PN
GROUP BY b.PartNo;


Finally, we drop our temp tables, cleaning up our sandbox.

GO
DROP TABLE #Source;
DROP TABLE #Supersessions;
DROP TABLE #Hierarchy;


That whole sorting thing is pretty problematic. I suspect you won't like the ordering if you happen to pick a PN that's somewhere in the middle of the chain. Like I said, without a date of supersession or something, driving the ordering is going to be a headache.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1537595
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse