How to get latest Date - SQL Server Forums

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

How to get latest Date

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-11 : 23:52:35

Hi All,

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.
Can you modify my scripts.
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 00:42:43

[code]
--sql 2000 or below solution
select t1.ESN,t2.SupplierID,t2.Shipdate
from TABLE1 t1
inner join table2 t2
on t2.ESN = t1.ESN
inner join (select ESN,MAX(Shipdate) AS LatestShipdate
from table2
group by ESN)t3
on t3.esn=t2.esn
and t3.LatestShipdate = t2.Shipdate

--sql 2005 and above solutions
--solution1
SELECT t1.ESN,t2.SupplierID,t2.Shipdate
FROM Table1 t1
CROSS APPLY (SELECT TOP 1 SupplierID,Shipdate
FROM Table2
WHERE ESN = t1.ESN
ORDER BY Shipdate DESC)t2

--solution2
SELECT ESN,SupplierID,Shipdate
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY t1.ESN ORDER BY t2.Shipdate
DESC) AS Rn,t1.ESN,t2.SupplierID,t2.Shipdate
FROM Table1 t1
INNER JOIN Table2
ON t2.ESN = t1.ESN
)
WHERE Rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-12 : 01:27:42

Author Topic
Villanuev
Starting Member

17 Posts
Posted - 09/11/2011 : 23:52:35
--------------------------------------------------------------------------------

Hi All,

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.
Can you modify my scripts.
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

Hi visakh16,

I got an error for solution 2.

Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'WHERE'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 01:42:21

ah missed an alias


SELECT ESN,SupplierID,Shipdate
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY t1.ESN ORDER BY t2.Shipdate
 DESC) AS Rn,t1.ESN,t2.SupplierID,t2.Shipdate
FROM Table1 t1
INNER JOIN Table2
ON t2.ESN = t1.ESN
)t
WHERE Rn=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 01:42:54

i hope the other two solutions work fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-12 : 01:59:22

Thanks visakh16. Its working.
Btw, in this script the display only records that have found based on ESN.
what about if i would to include to display ESN doenst have found.?

Regards,
jov

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-12 : 02:07:25

Where should i place this command..

into #VPData

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-12 : 02:09:37

Posted - 09/12/2011 : 02:07:25

THis is done.
into #VPData

Btw, in this script the display only records that have found based on ESN.
what about if i would to include to display ESN doenst have found.?

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-12 : 02:14:49

Done both.

Just replace the inner join to right outer join and place additional field of ESN2 to identified
that this esn is not found.

Thank you very much..

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-12 : 02:20:05

Sorry, i think its done..

Still not consistend the records.
and still doenst display the esn from Table1 wwho is not found in TABLE2.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-12 : 02:54:51

Hi visakh16,

Its possible to display the ESN from table1 which are not found in Table2 based on your given script. I need also to know those ESNs records not found. thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 02:56:56

for displaying esns not in table2 use like


--sql 2000 or below solution
select t1.ESN,t2.SupplierID,t2.Shipdate
from TABLE1 t1
left join table2 t2
on t2.ESN = t1.ESN
left join (select ESN,MAX(Shipdate) AS LatestShipdate
            from table2
            group by ESN)t3
on t3.esn=t2.esn
and t3.LatestShipdate = t2.Shipdate

--sql 2005 and above solutions
--solution1
SELECT t1.ESN,t2.SupplierID,t2.Shipdate
FROM Table1 t1
OUTER APPLY (SELECT TOP 1 SupplierID,Shipdate
             FROM Table2
             WHERE ESN = t1.ESN
             ORDER BY Shipdate DESC)t2

--solution2
SELECT ESN,SupplierID,Shipdate
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY t1.ESN ORDER BY t2.Shipdate
 DESC) AS Rn,t1.ESN,t2.SupplierID,t2.Shipdate
FROM Table1 t1
LEFT JOIN Table2
ON t2.ESN = t1.ESN
)
WHERE Rn=1 OR t2.ESN IS NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-12 : 04:06:41

Again. Its a big thank you very much. visakh16.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 04:12:11

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources