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%'

Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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%'
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -