SQLTeam.com | Weblogs | Forums

Find most recent record but with extra columns

sql2012

#1

I'm trying to return only the latest works order details for an item of equipment, but my query is returning all the works order records for each FleetNo. Basically, I want to see only the most recent works order, but I need to see it's corresponding service hours and the service name for that works order and no others. Here you can see it is repeating rows as I'm getting something wrong:

Select MAX(wh.itemcode) FleetNo
,MAX(ISNULL(CAST(LEFT(wt.name, charindex(' ', wt.name) -1)as int),0)) [ServiceNo]
,wt.name [ServiceName]
,wh.worknumber
,wh.date_in

from worksorderhdr wh
left join worksordertypes wt on wt.id = wh.worktype

where wh.rejected <> 1 and wh.worktype in (10,11,12,13,14,15,16,17,18,19,20,21,22,32,51,52,53,55,56,64,65)
Group by wh.itemcode, wt.name, wh.worknumber, wh.date_in
order by FleetNo

And results, showing show examples of extra rows for items that I don't want:

FleetNo ServiceNo ServiceName worknumber date_in


00GA0003 1000 1000 Hour Service HU-DN-008275 2015-04-13 15:56:00
00GA0003 250 250 Hour Service IP-DN-116597 2015-05-05 13:30:00
03EX0002 500 500 Hour Service IP-DN-120858 2015-06-09 07:45:00
03EX0003 500 500 Hour Service HU-DN-010433 2015-07-29 13:30:00
03EX0006 500 500 Hour Service IP-DN-120327 2015-06-03 09:30:00
05EX0001 500 500 Hour Service IP-DN-126168 2015-07-21 10:35:00
05MC0001 100 100 Hour Service IP-DN-078879 2014-07-24 11:21:00
05MC0001 500 500 Hour Service IP-DN-108533 2015-03-04 12:29:00
05MC0002 100 100 Hour Service ER-DN-001362 2015-02-18 00:00:00
06MT0001 1500 1500 Hour Service ER-DN-001885 2015-04-01 13:00:00
06MT0002 1000 1000 Hour Service ER-DN-002049 2015-04-13 11:41:00
06MT0002 500 500 Hour Service IP-DN-103415 2015-01-28 16:37:00
06MT0003 1500 1500 Hour Service IP-DN-118844 2015-06-01 16:29:00
06MT0003 1000 1000 Hour Service IP-DN-106136 2015-02-16 13:06:00

So for example for 06MR0003 I only want to see the 1500 service record as it is the most recent. How can I achieve this please?


#2

There is no 06MR0003 in your current output. You are grouping by itemCode, so not sure why that got eliminated. In any case, this is my guess on what you are looking for. If it is not, please post sample input data and DDL for the tables.

SELECT *
FROM
(
	SELECT  
		wh.itemcode FleetNo ,
		ISNULL(CAST(LEFT(wt.name, CHARINDEX(' ', wt.name) - 1) AS INT), 0) [ServiceNo] ,
		wt.name [ServiceName] ,
		wh.worknumber ,
		wh.date_in,
		ROW_NUMBER() OVER 
		(
			PARTITION BY FleetNo, ISNULL(CAST(LEFT(wt.name, CHARINDEX(' ', wt.name) - 1) AS INT), 0)
			ORDER BY wh.date_in DESC
		)
	FROM    
		worksorderhdr wh
		LEFT JOIN worksordertypes wt ON wt.id = wh.worktype
	WHERE   
		wh.rejected <> 1
		AND wh.worktype IN ( 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21,
							 22, 32, 51, 52, 53, 55, 56, 64, 65 )
) s
WHERE RN = 1
ORDER BY FleetNo;

#3

Sorry, my bad - I meant 06MT0003.

I tried with your code but got:

Msg 207, Level 16, State 1, Line 12
Invalid column name 'FleetNo'.
Msg 8155, Level 16, State 2, Line 23
No column name was specified for column 6 of 's'.
Msg 207, Level 16, State 1, Line 23
Invalid column name 'RN'.

Thanks
Martyn


#4

Sorry about that. Try this?

SELECT *
FROM
(
	SELECT  
		wh.itemcode FleetNo ,
		ISNULL(CAST(LEFT(wt.name, CHARINDEX(' ', wt.name) - 1) AS INT), 0) [ServiceNo] ,
		wt.name [ServiceName] ,
		wh.worknumber ,
		wh.date_in,
		ROW_NUMBER() OVER 
		(
			PARTITION BY FleetNo, ISNULL(CAST(LEFT(wt.name, CHARINDEX(' ', wt.name) - 1) AS INT), 0)
			ORDER BY wh.date_in DESC
		) AS RN
	FROM    
		worksorderhdr wh
		LEFT JOIN worksordertypes wt ON wt.id = wh.worktype
	WHERE   
		wh.rejected <> 1
		AND wh.worktype IN ( 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21,
							 22, 32, 51, 52, 53, 55, 56, 64, 65 )
) s
WHERE RN = 1
ORDER BY FleetNo;

The addition is the aliasing added (AS RN)


#5

No, still getting the extra rows for each itemcode as below, for example FleetNo 05MC0001, I only want to see the 500 hour service row.

FleetNo ServiceNo ServiceName worknumber date_in RN


00GA0003 1000 1000 Hour Service HU-DN-008275 2015-04-13 15:56:00 1
00GA0003 250 250 Hour Service IP-DN-116597 2015-05-05 13:30:00 1
03EX0002 500 500 Hour Service IP-DN-120858 2015-06-09 07:45:00 1
03EX0003 500 500 Hour Service HU-DN-010433 2015-07-29 13:30:00 1
03EX0006 500 500 Hour Service IP-DN-120327 2015-06-03 09:30:00 1
05EX0001 500 500 Hour Service IP-DN-126168 2015-07-21 10:35:00 1
05MC0001 500 500 Hour Service IP-DN-108533 2015-03-04 12:29:00 1
05MC0001 100 100 Hour Service IP-DN-078879 2014-07-24 11:21:00 1
05MC0002 100 100 Hour Service ER-DN-001362 2015-02-18 00:00:00 1
06MT0001 1500 1500 Hour Service ER-DN-001885 2015-04-01 13:00:00 1
06MT0002 500 500 Hour Service IP-DN-103415 2015-01-28 16:37:00 1
06MT0002 1000 1000 Hour Service ER-DN-002049 2015-04-13 11:41:00 1

Thanks
Martyn


#6

May be you don't need that second column in the partition by clause. I am guessing - if you post some sample input data that would help. Try using this.

ROW_NUMBER() OVER 
		(
			PARTITION BY FleetNo
			ORDER BY wh.date_in DESC
		) AS RN

#7

Apologies for the delay in replying - I had a family emergency trip to hospital yesterday.

Thank you for your help, with a little tweaking I got it to work using the code below:

SELECT *
FROM
(
SELECT
wh.itemcode FleetNo ,
ISNULL(CAST(LEFT(wt.name, CHARINDEX(' ', wt.name) - 1) AS INT), 0) [ServiceNo] ,
wt.name [ServiceName] ,
wh.worknumber ,
wh.date_in,
ROW_NUMBER() OVER
(
PARTITION BY wh.itemcode
ORDER BY ISNULL(CAST(LEFT(wt.name, CHARINDEX(' ', wt.name) - 1) AS INT), 0) DESC
) AS RN
FROM
worksorderhdr wh
LEFT JOIN worksordertypes wt ON wt.id = wh.worktype
WHERE
wh.rejected <> 1
AND wh.worktype IN ( 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21,
22, 32, 51, 52, 53, 55, 56, 64, 65 )
) s
WHERE RN = 1
ORDER BY FleetNo;

Many thanks again
Martyn