Getting Latest Date

[jovillanuev]

Hi expert,

When i try this script i got 2 esn number with different shipdate and supplierid.
were us it should be the latest shipdate should be obtain.
Any Idea guys. thanks.

TABLE1
ESN
123


TABLE2
ESN--SupplierID--Shipdate
123--40----------2011-04-27
123--14----------2011-07-02

Select
     vp.esn,
     us.supplierid,
     us.shipdate
     --(Select Max(shipdate) from TABLE2 where vp.esn = us.esn)
From Table1 as vp with (nolock) 
Left Outer Join TABLE2 as us  with (nolock)
	 On vp.esn = us.esn COLLATE Chinese_Taiwan_Stroke_CI_AS 
     and us.shipdate ?
     --and Max(Shipdate)
Where vp.ReceivingPO is not null
and vp.Receiveddate is not null
and vp.esn <> 'ESN' and vp.ESN <> 'TLCSHK'

Regards,

Jov

[darkagn]

You can use the TOP N function to retrieve the most recent if you order by shipdate. Like so:

SELECT TOP 1
vp.esn,
us.supplierid,
us.shipdate
FROM Table1 AS vp WITH (nolock)
LEFT OUTER JOIN TABLE2 AS us WITH (nolock)
ON vp.esn = us.esn COLLATE Chinese_Taiwan_Stroke_CI_AS
WHERE vp.ReceivingPO IS NOT NULL
AND vp.Receiveddate IS NOT NULL
AND vp.esn <> 'ESN' AND vp.ESN <> 'TLCSHK'
ORDER BY us.shipdate DESC -- desc will give most recent, asc will give oldest

[adam_k]

The TOP will result in only 1 record from the entire query. If you want all esns - whatever that is - with the supplierid and the latest shipdate then you must use something similar to:

Select
     vp.esn,
     us.supplierid,
     us.shipdate
    From Table1 as vp with (nolock) 
Left Outer Join 
(select a.* from TABLE2 as a with (nolock) 
inner join 
(select esn, max(shipdate) as shipdate from TABLE2 with (nolock) 
group by esn) 
as b on a.esn = b.esn 
and a.shipdate = b.shipdate) as us  
	 On vp.esn = us.esn COLLATE Chinese_Taiwan_Stroke_CI_AS 
     Where vp.ReceivingPO is not null
and vp.Receiveddate is not null
and vp.esn <> 'ESN' and vp.ESN <> 'TLCSHK'

Depending on the number of records it could be faster to select max(date) where esn = vp.esn, but the solution I'm proposing is simple and hopefully you can easily see where you went wrong. If you find that this query is taking too long to execute, create a new thread and we'll discuss your options there.

[jovillanuev]

Sorry guys for my late reply.
Btw, Thank for your reply. i will try this scripts later.

again thank you.