Select the most recent date for each item

Question

Post reply

Select the most recent date for each item

Isabelle2378

SSCertifiable

Points: 6174
More actions
April 30, 2007 at 12:16 pm

#176163

Hi,
I've been looking at previous posts to try and figure out how to get my query to return the most recent date for each item and I can't figure it out. Here is my query:
select vw.item_code, vw.qty_onhand, rh.date_rcvd
from incinvw vw, incinv1 v1, podinrh rh
where v1.lot_control_ind = 0
and vw.whse_code = 'W011'
and rh.item_code = vw.item_code
and vw.item_code = v1.item_code
and vw.whse_code = rh.whse_code
AND vw.qty_onhand != 0 and vw.qty_onhand > 0
order by vw.item_code, rh.date_rcvd DESC
and I get back:
item_code     qty_onhand     date_rcvd
402                 11112.00        2006-09-14 00:00:00
402                 11112.00        2006-09-13 00:00:00
402T                 1200.00        2006-07-17 00:00:00
and what I need it to bring back is just each item only once, so for 402, i just want the date for 2006-09-14. So for this particular warehouse, it should only return 2 rows. How can I incorporate that into my code above? I've tried using Max(date) and nested queries but it does not return the correct 2 rows.

Thanks in advance!!!
Isabelle
Thanks!
Bea Isabelle

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

select a.item, a.date

from MyTable a

where a.date = ( select max(b.date)

from MyTable b

where b.item = a.item

)

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 2

Hi Steve,

When I add your suggestion into my code, it only selects 1 line for the item 402T and nothing for the 402 item. I had tried something very similar before and was only getting back the one item and thought I was doing something wrong. When I test your query by itself, it returns both rows

select vw.item_code, rh.item_code, MAX(rh.date_rcvd)

from podinrh rh, incinvw vw

where vw.item_code like '*1PS0402%'

and vw.item_code = rh.item_code

GROUP BY vw.ITEM_CODE, rh.item_code

*1PS0402 *1PS0402 2006-10-10 00:00:00

*1PS0402T *1PS0402T 2006-07-17 00:00:00

But when I try and incorporate it into the code, I only get 1 back:

W011 *1PS0402T PS0402T 1200.000 2006-07-17 00:00:00 060717

I'm not sure what I'm doing wrong?

Isabelle

Thanks!
Bea Isabelle

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 3

FYI...here is what the code looks like when I try to incorporate it.

select vw.whse_code, vw.item_code, vw.qty_onhand, rh.date_rcvd

from incinvw vw, incinv1 v1, podinrh rh

where v1.lot_control_ind = 0

and rh.date_rcvd = (select max(b.date_rcvd)

from podinrh b where b.item_code = vw.item_code)

and vw.whse_code = 'W011'

and rh.item_code = vw.item_code

and vw.item_code = v1.item_code

and vw.whse_code = rh.whse_code

AND vw.qty_onhand != 0 and vw.qty_onhand > 0

group by vw.whse_code, vw.item_code, vw.qty_onhand, rh.date_rcvd

Thanks,

Isabelle

Thanks!
Bea Isabelle

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 4

Try this:

select

t.item_code,

t.qty_onhand,

t.date_rcvd

from my_table t

join

(

select

item_code,

max_date_rcvd = max(date_rcvd)

from my_table

group by item_code

) m

on t.item_code = m.item_code

and t.date_rcvd = m.max_date_rcvd

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

select

item_code,

qty_onhand

,

date_rcvd

from

(

select vw.item_code,

vw

.qty_onhand,

rh

.date_rcvd,

row_number() over (partition by vw.item_code order by rh.date_rcvd desc) as recid

from incinvw AS vw

inner join incinv1 AS v1 on v1.item_code = vw.item_code and vw.whse_code = 'W011'

inner join podinrh AS rh on rh.item_code = vw.item_code and rh.whse_code = vw.whse_code and v1.lot_control_ind = 0

where vw.qty_onhand > 0

) as x

where

recid = 1

order

by item_code,

date_rcvd

DESC

N 56°04'39.16"
E 12°55'05.25"

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 6

I got it to work using the following:

select vw.whse_code, vw.item_code, vw.qty_onhand, MAX(rh.date_rcvd),

CONVERT(VARCHAR,MAX(rh.date_rcvd),12) AS CONVERTED_LOT

from incinvw vw, incinv1 v1, podinrh rh

where v1.lot_control_ind = 0

and vw.whse_code = 'W011'

and rh.item_code = vw.item_code

and vw.item_code = v1.item_code

and vw.whse_code = rh.whse_code

AND vw.qty_onhand != 0 and vw.qty_onhand > 0

group by vw.whse_code, vw.item_code, vw.qty_onhand

order by vw.item_code

Thanks so much for your help.

Isabelle

Thanks!
Bea Isabelle