Max Serial Retrieval 1

[Khanson82]

Why will this not pull back all of the max serial rows I'm needing for the notes field? It does it correctly for about 90% of the records? Any other ideas for getting max record pulled for a notes(text) field? The max record row for the notes field will also have text in it everytime never null.
Sql Server 2005


select a.serial,a.clientid,a.caseno,a.applid, a.applname,(a.status + ' / ' + a.statusreason)as Status,b.notes
from vwAP a
left outer join vwRQ b with(nolock)
on a.serial = b.appserial
and a.clientid = b.clientid
and b.serial in (select max(serial) from vwRQ with(nolock)
where requirement = ('qc')
and notes like ('% %')
group by appserial)
where a.status in ('corrections','returned by client','corrections submitted')

 

[Crowley16]

and notes like ('% %')

why not just use is not null or even len(isnull(notes, '')) > 0

--------------------
Procrastinate Now!

 

[markros]

;with cte as (select a.serial,a.clientid,a.caseno,a.applid, a.applname,(a.status + ' / ' + a.statusreason)as Status,b.notes, row_number() over (order by case when b.requirement = ('qc') and b.Notes > '' then b.Serial end DESC) as Row 
from vwAP a
left outer join vwRQ b with(nolock)
on a.serial = b.appserial
and a.clientid = b.clientid
where a.status in ('corrections','returned by client','corrections submitted'))

select * from cte where Row  = 1

PluralSight Learning Library

 

[Khanson82]

For some reason the different ways to exclude null in the notes field just bring back all types of different results..None as close as to 90% right results as the notes like ('% %') statement.


as far as the cte, I get this:

Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the greater than operator.

 

[Crowley16]

are you on 2000?

2000 is notorious for not handling text fields well, in my experiance at least.

try converting the text value to varchar and comparing on that.

on 2005, use the varchar(max) datatype

--------------------
Procrastinate Now!

 

[Khanson82]

I'm using 2005, and use the varchar(max) in the cte statement or my orignal one? Tried both, but I must have the wrong syntax..

 

[Crowley16]

try it in the cte...

probably best to just run the bit in the cte to check you're pulling back all the records you want first...

--------------------
Procrastinate Now!

 

[markros]

Adjust my code to cast(Notes as varchar(max)) first.

PluralSight Learning Library

 

[Khanson82]

With the cte, I get mass duplication of every row? My initial code pulls back arond 25 unique records,I'm just having a hard time pulling back the right record for the notes field. With cte, it pulled back 375 records. If i leave it at row = 1 then obviously i only get one record back. Any other tweaks?

 

[markros]

Add necessary partition into OVER clause. Do you want to get max record per serial or per what?

PluralSight Learning Library

 

[Khanson82]

I want to get the corresponding record for the max record(off the serial) where the criteria is met.

For instance a case may be in the same status twice in our system,so it will have two row records, we have it pulling the max serial for that case when the status ='qc', so it should pull the latest one but sometimes it is and sometimes it isnt.

 

[markros]

Ok, add Status as a partition. Add other fields as partition if needed.

PluralSight Learning Library

 

[Khanson82]

I just noticed the requirement='qc' was already in othe over by statement.hmm..I'm gonna keep working on it, but I know this has gone back and forth for a while, sorry to take so much time..Thank you.

This is where I'm at with the cte,(still duplicating and pulling wrong grecords)

;with cte as (select a.serial,a.clientid,a.caseno,a.applid, a.applname,(a.status + ' / ' + a.statusreason)as Status,b.notes, row_number()
over (order by case when b.requirement = ('qc')
and cast(b.Notes as varchar(max)) > ''
then b.Serial end DESC) as Row
from vwAP a
left outer join vwRQ b with(nolock)
on a.serial = b.appserial
and a.clientid = b.clientid
where a.status in ('corrections','returned by client','corrections submitted'))
select * from cte

 

[markros]

Try

;with cte as (select a.serial,a.clientid,a.caseno,a.applid, a.applname,(a.status + ' / ' + a.statusreason)as Status,b.notes, row_number()
over (partition by a.serial,a.clientid,a.caseno,a.applid, a.applname order by case when b.requirement = ('qc')
and cast(b.Notes as varchar(max)) > ''
then b.Serial end DESC) as Row
from vwAP a
left outer join vwRQ b with(nolock)
on a.serial = b.appserial
and a.clientid = b.clientid
where a.status in ('corrections','returned by client','corrections submitted'))
select * from cte where Row = 1

-------
As I said, you need to use appropriate partition, I don't know exactly by which fields - you need to figure this out.



PluralSight Learning Library

 

[Khanson82]

Will do and will update on Monday...Thanks again for all the help today!!