January 7, 2015 at 11:23 am
Hello,
I'm struggling with a particular query and would greatly appreciate any help on this.
My task is to query the MachineParts table and pull the MachineFk for any Machine whose three most recent (based on InstallDate) MachineParts are valid (IsValid = 1). But I need to look at the last three for each individual PartType for each MachineFk. If at least one PartType fits the above criteria, then I would pull the MachineFk. If multiple PartTypes fit that criteria, I would still only want to pull the MachineFk one time.
Here is some sample data:
CREATE TABLE
#MachineParts
(
MachinePartPk INT PRIMARY KEY,
MachineFk INT,
InstallDate DATE,
PartType INT,
IsValid BIT
)
INSERT INTO #MachineParts VALUES (1, 1, '01/15/2014', 300, 1)
INSERT INTO #MachineParts VALUES (2, 1, '03/01/2014', 300, 0)
INSERT INTO #MachineParts VALUES (3, 1, '04/16/2014', 300, 1)
INSERT INTO #MachineParts VALUES (4, 1, '02/11/2014', 500, 1)
INSERT INTO #MachineParts VALUES (5, 2, '03/22/2014', 250, 1)
INSERT INTO #MachineParts VALUES (6, 2, '07/18/2014', 500, 1)
INSERT INTO #MachineParts VALUES (7, 3, '02/04/2014', 250, 1)
INSERT INTO #MachineParts VALUES (8, 3, '02/11/2014', 300, 1)
INSERT INTO #MachineParts VALUES (9, 3, '06/28/2014', 250, 1)
INSERT INTO #MachineParts VALUES (10, 3, '11/12/2014', 250, 0)
INSERT INTO #MachineParts VALUES (11, 3, '11/23/2014', 300, 1)
INSERT INTO #MachineParts VALUES (12, 3, '11/30/2014', 300, 1)
INSERT INTO #MachineParts VALUES (13, 3, '08/09/2014', 500, 1)
INSERT INTO #MachineParts VALUES (14, 3, '10/27/2014', 500, 1)
INSERT INTO #MachineParts VALUES (15, 3, '10/28/2014', 500, 1)
SELECT * FROM #MachineParts ORDER BY MachineFk, PartType, InstallDate DESC
DROP TABLE #MachineParts
Based on this test data, the only result I would expect is a MachineFk of 3.
You wouldn't get MachineFk 1 because one of the three most recent Parts for PartType 300 is not valid. You also wouldn't get MachineFk 2 because there are only two records. You would get MachineFk 3 because at both PartType 300 and PartType 500 meet the criteria.
Please let me know if you have any questions.
Thank you!
January 7, 2015 at 12:18 pm
This should do the trick.
WITH cteMP AS(
SELECT MachineFk,
IsValid,
PartType,
ROW_NUMBER() OVER(PARTITION BY MachineFk, PartType
ORDER BY InstallDate DESC) rn --Number the rows by MachineFk & PartType
FROM #MachineParts
)
SELECT DISTINCT MachineFk --DISTINCT to show MachineFK just once
FROM cteMP
WHERE rn <= 3 --Get only the first 3
AND IsValid = 1 --And limit only for valid ones
GROUP BY MachineFk, PartType
HAVING COUNT(*) = 3 --Be sure that you're getting 3 and not less
January 7, 2015 at 2:19 pm
Thank you Luis! I'll give this a try tomorrow!
January 8, 2015 at 11:14 am
Luis,
Your query appears to have worked. Thank you very much!
An additional question: What if I wanted everything the same, but instead of looking at a boolean field like IsValid, I would be looking at a field (we'll call it "Price") which could hold any number of values. Then I would want to retrieve the MachineFk for any instance in which the three most recent machine parts have identical Price values for any PartType.
January 8, 2015 at 11:50 am
I'm not sure I get it. Could you post an example with ddl and sample data as you did previously?
January 8, 2015 at 12:07 pm
Absolutely.
CREATE TABLE
#MachineParts
(
MachinePartPk INT PRIMARY KEY,
MachineFk INT,
InstallDate DATE,
PartType INT,
Price MONEY
)
INSERT INTO #MachineParts VALUES (1, 1, '01/15/2014', 300, 9.75)
INSERT INTO #MachineParts VALUES (2, 1, '03/01/2014', 300, 10.50)
INSERT INTO #MachineParts VALUES (3, 1, '04/16/2014', 300, 10.50)
INSERT INTO #MachineParts VALUES (4, 1, '02/11/2014', 500, 10.50)
INSERT INTO #MachineParts VALUES (5, 2, '03/22/2014', 250, 2.35)
INSERT INTO #MachineParts VALUES (6, 2, '07/18/2014', 500, 2.35)
INSERT INTO #MachineParts VALUES (7, 3, '02/04/2014', 250, 6.10)
INSERT INTO #MachineParts VALUES (8, 3, '02/11/2014', 300, 3.95)
INSERT INTO #MachineParts VALUES (9, 3, '06/28/2014', 250, 6.10)
INSERT INTO #MachineParts VALUES (10, 3, '11/12/2014', 250, 6.10)
INSERT INTO #MachineParts VALUES (11, 3, '11/23/2014', 300, 1.15)
INSERT INTO #MachineParts VALUES (12, 3, '11/30/2014', 300, 3.85)
INSERT INTO #MachineParts VALUES (13, 3, '08/09/2014', 500, 2.45)
INSERT INTO #MachineParts VALUES (14, 3, '10/27/2014', 500, 2.45)
INSERT INTO #MachineParts VALUES (15, 3, '10/28/2014', 500, 2.45)
SELECT * FROM #MachineParts ORDER BY MachineFk, PartType, InstallDate DESC;
DROP TABLE #MachineParts
So in this instance, MachineFk of 3 is the only result. You wouldn't see MachineFk of 1 because the three most recent parts (based on InstallDate) are not the same price. You wouldn't see MachineFk of 2 because there are only two records. You would see MachineFk of 3 because PartType 250 and PartType 500 both have identical Price values for their three most recent parts. As with the other query, it would be irrelevant that PartType 300 for MachineFk 3 doesn't meet the criteria, only one PartType for that MachineFk needs to meet the criteria.
Hopefully that clarifies things. I appreciate your help and am finding this a good learning experience.
January 8, 2015 at 12:41 pm
If I'm correct, you just need to remove the IsValid condition and add Price column to the GROUP BY.
That should give you the desired result.
January 8, 2015 at 1:05 pm
The change to look a prices requires only very small change to Luis' solution for the version involving validity, like this:-
WITH cteMP AS(
SELECT MachineFk,
Price,
PartType, -- instead of validity
ROW_NUMBER() OVER(PARTITION BY MachineFk, PartType
ORDER BY InstallDate DESC) rn --Number the rows by MachineFk & PartType
FROM #MachineParts
)
SELECT DISTINCT MachineFk --DISTINCT to show MachineFK just once
FROM cteMP
WHERE rn <= 3 --Get only the first 3
GROUP BY MachineFk, PartType
HAVING COUNT(*) = 3 --Be sure that you're getting 3 and not less
AND count(DISTINCT Price) = 1 ; -- and that all three prices are the same
edit: I see Luis posted a comment while I was coping with a domestic problem instead of posting my suggested changes. His comment suggest a slightly different change from mine: adding Price to troup by list instead of adding a count condition to the HAVING clause. Logically the two ways of doing it have the same effect, but there may be a performance difference - and I don't know which would be faster (or how big the table would have to be to make the difference noticeable).
Tom
January 9, 2015 at 9:22 am
Tom,
Your solution appears to work perfectly!
Thank you everyone.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply