Select most recent by date plus other criteria

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select most recent by date plus other criteria

Author

Topic

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-02-01 : 11:11:42

I have the following query that should return the most recent FormNote entry for a work order where the note begins with "KPI". However if someone decides to a more recent note, it selects that one, even if it doesn't begin with "KPI".

I would like it to return the most recent record that ALSO begins with "KPI". How can I correct this?

Select wh.worknumber, wh.date_created, wh.itemcode, wn.TextEntry as [Notes] from worksorderhdr wh left join

(select ID, WorksOrder,[CreationDate], TextEntry
from
(
select ROW_NUMBER()over(partition by worksorder order by [CreationDate] desc) OID,*
from FormNotes 
)orders where orders.OID=1 ) wn on wn.WorksOrder = wh.worknumber where TextEntry like 'KPI%'

Sample results below, see line 5 - this record should not have been selected as there is a record beginning with "KPI" for that work order, but it is dated before this one.

worknumber           date_created            itemcode             Notes
-------------------- ----------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HU-DN-004385         2014-07-21 16:15:00     4261                 KPI Hyd oil leak repaired
HU-DN-004707         2014-08-06 11:39:00     8005                 KPI Valve replaced on day 2.
HU-DN-004889         2014-08-19 15:44:00     9275A                KPI Repaired in 2 days - m/c working
HU-DN-004923         2014-08-22 14:23:00     4261                 KPI New tracks fitted
HU-DN-005162         2014-09-12 15:04:00     9360A                Mechlock key delivered to site - m/c working
HU-DN-005170         2014-09-15 12:07:00     2130A                KPI 28.10.14 Metlock fitted

Many thanks
Martyn

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-01 : 13:17:47

quote:

Originally posted by wembleybear

Select wh.worknumber, wh.date_created, wh.itemcode, wn.TextEntry as [Notes] from worksorderhdr wh left join

(select ID, WorksOrder,[CreationDate], TextEntry
from
(
select ROW_NUMBER()over(partition by worksorder order by [CreationDate] desc) OID,*
from FormNotes 
)orders where orders.OID=1 ) wn on wn.WorksOrder = wh.worknumber where wn.TextEntry like 'KPI%'

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-02-02 : 05:45:39

Thank you, but I still have the problem. If a note is added without the "KPI" prefix AFTER the date of the KPI note, then nothing is returned for that work order.

As a test I added a note to work order HU-DN-004923 with todays date but without the "KPI" prefix. Now that work order does not appear in the results. It should still return the KPI note for the work order shown in the sample results below, but it doesn't return anything.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-02 : 07:11:28

then, remove the WHERE clause that explicitly tests for KPI.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-02 : 11:23:11

Try this:

Select wh.worknumber, wh.date_created, wh.itemcode, wn.TextEntry as [Notes] from worksorderhdr wh left inner join

(select ID, WorksOrder,[CreationDate], TextEntry
from
(
select ROW_NUMBER()over(partition by worksorder order by [CreationDate] desc) OID,*
from FormNotes where TextEntry like 'KPI%'
)orders where orders.OID=1 ) wn on wn.WorksOrder = wh.worknumber where wn.TextEntry like 'KPI%'

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-02-02 : 12:53:10

Excellent bitsmead, that's sorted it.

Many thanks for your help.

Martyn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources