July 30, 2015 at 5:52 pm
My boss has asked me to write a query that lists item, mfg serial number, and inspection date. The caveat is that, where there is more than one record for an item, he only wants item listed once. For example:
PartNumberInspectionDateManufacturerSerialNumber
1A144878/4/2013.1101VAS073-7
3L115931/28/2014009VAS206-1
1/29/2014009VAS206-1
2/21/2014009VAS206-1
7/22/2014009VAS206-1
2A285631/1/2015010115ND9CV02
1A203921/3/2015010315ND9PV45
1/4/2015010315ND9PV45
3/1/2015010315ND9PV45
2A2794310/20/20120103700P003
July 30, 2015 at 5:55 pm
like falling down...
SELECT PartNumber, SerialNumber, MAX(InspectionDate) AS LastInspection
FROM MyTable
GROUP BY PartNumber, SerialNumber
ORDER BY PartNumber, SerialNumber;
July 30, 2015 at 6:06 pm
Thanks, but it is listing all of my blank part numbers at the top. What am I doing wrong?
July 30, 2015 at 6:32 pm
without sample data, it's hard to tell, but my guess is that you're missing a WHERE clause...
SELECT PartNumber, SerialNumber, MAX(InspectionDate) AS LastInspection
FROM MyTable
WHERE PartNumber IS NOT NULL
GROUP BY PartNumber, SerialNumber
ORDER BY PartNumber, SerialNumber;
July 30, 2015 at 10:53 pm
Sorry, I posted too early a while ago without giving you all the tools.
Here is some sample data:
IF OBJECT_ID('TestGroup', 'U') IS NOT NULL DROP TABLE TestGroup
CREATE TABLE TestGroup (PartNumber NVARCHAR(20), InspectionDate DATETIME, MfgSerialNumber NVARCHAR(20))
INSERT INTO TestGroup
SELECT '1A14487', '8/4/2013', '.1101VAS073-7' UNION ALL
SELECT '3L11593', '1/28/2014', '009VAS206-1' UNION ALL
SELECT '3L11593', '1/29/2014', '009VAS206-1' UNION ALL
SELECT '3L11593', '2/21/2014', '009VAS206-1' UNION ALL
SELECT '3L11593', '7/22/2014', '009VAS206-1' UNION ALL
SELECT '2A28563', '1/1/2015', '010115ND9CV02' UNION ALL
SELECT '1A20392', '1/3/2015', '010315ND9PV45' UNION ALL
SELECT '1A20392', '1/4/2015', '010315ND9PV45' UNION ALL
SELECT '1A20392', '3/1/2015', '010315ND9PV45' UNION ALL
SELECT '2A27943', '10/20/2012', '0103700P003'
When I select the data this is what I get:
PartNumberInspectionDateMfgSerialNumber
1A1448708/04/13.1101VAS073-7
3L1159301/28/14009VAS206-1
3L1159301/29/14009VAS206-1
3L1159302/21/14009VAS206-1
3L1159307/22/14009VAS206-1
2A2856301/01/15010115ND9CV02
1A2039201/03/15010315ND9PV45
1A2039201/04/15010315ND9PV45
1A2039203/01/15010315ND9PV45
2A2794310/20/120103700P003
This is what I want:
PartNumberInspectionDateMfgSerialNumber
1A1448708/04/13.1101VAS073-7
3L1159301/28/14009VAS206-1
01/29/14009VAS206-1
02/21/14009VAS206-1
07/22/14009VAS206-1
2A2856301/01/15010115ND9CV02
1A2039201/03/15010315ND9PV45
01/04/15010315ND9PV45
03/01/15010315ND9PV45
2A2794310/20/120103700P003
Is this possible?
Thanks.
July 30, 2015 at 11:57 pm
Your Query is right ..Only you need to represent it in right way
Use reporting tools and row group by part No.
July 31, 2015 at 7:45 am
That would work, but the data goes through an external app and can not use SSRS.
July 31, 2015 at 8:32 am
Select * INTO #TMP FROM(
SELECT '1A14487' as PartNumber, Convert(Datetime,'8/4/2013') as InspDate, '.1101VAS073-7' as MfgSerial UNION ALL
SELECT '3L11593', '1/28/2014', '009VAS206-1' UNION ALL
SELECT '3L11593', '1/29/2014', '009VAS206-1' UNION ALL
SELECT '3L11593', '2/21/2014', '009VAS206-1' UNION ALL
SELECT '3L11593', '7/22/2014', '009VAS206-1' UNION ALL
SELECT '2A28563', '1/1/2015', '010115ND9CV02' UNION ALL
SELECT '1A20392', '1/3/2015', '010315ND9PV45' UNION ALL
SELECT '1A20392', '1/4/2015', '010315ND9PV45' UNION ALL
SELECT '1A20392', '3/1/2015', '010315ND9PV45' UNION ALL
SELECT '2A27943', '10/20/2012', '0103700P003') D
-- Order the way you need it adding a row number
Select Row_Number() OVER(ORDER BY PartNumber ASC, InspDate ASC) as IID, * INTO #T2 FROM #tmp
-- Erase the repeated part numbers
UPDATE #T2 SET PartNumber = '' WHERE IID NOT IN (
Select MIN(X.IID) as IID FROM #T2 X GROUP BY X.PartNumber)
-- Return data ordered by IID
SELECT * FROM #T2 ORDER BY IID
July 31, 2015 at 8:35 am
WITH ctegrp
AS (SELECT
PartNumber
, InspectionDate
, MfgSerialNumber
, ROW_NUMBER() OVER (PARTITION BY PartNumber ORDER BY partnumber , inspectiondate) AS rn
FROM TestGroup)
SELECT
CASE
WHEN rn = 1 THEN PartNumber
ELSE ''
END AS pnumber
, InspectionDate
, MfgSerialNumber
FROM ctegrp
ORDER BY
partnumber , InspectionDate;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 31, 2015 at 9:07 am
;
With
A as (
SELECT
ROW_NUMBER() OVER(PARTITION BY PartNumber, MfgSerialNumber ORDER BY InspectionDate desc) subnumber
,*
FROM TestGroup
WHERE PartNumber IS NOT NULL
)
select
case when subnumber = 1 then convert(varchar(20),partnumber)
else ''
end Partnumber_or_empty
, *
from A order by partnumber,subnumber
The 'WITH' statement creates a sort of temporary views. (Views A and B)
This construction is called a Common Table Expression.
The steps:
A.
Create a table where a subnumber is generated. Within the list of partnumber MfgSerialNumber a subnumber is given. The order is de date in decending order.
End selection.
Does the sorting en representation.
Tells us if this is addequate.
Didn't see the previous anwser till I posted mine, this is almost equivalent.
Ben
July 31, 2015 at 2:18 pm
I appreciate all of your responses. I have learned a whole lot and have been able to incorporate the code into production using your examples.
July 31, 2015 at 2:21 pm
sdownen05 (7/31/2015)
I appreciate all of your responses. I have learned a whole lot and have been able to incorporate the code into production using your examples.
forum etiquette suggests that you post how you resolved your problem...this helps others to learn and may also open up additional posts that may improve on answers so far given
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply