Member Avatar for jovillanuev

Hi Guys,

How do i obtain the nearest shipdate prior to Returndate.
here is my sample script. thanks. kindly check my script.

the result should be 2011-02-23

Table1
ESN--DateReturn
123--2011-03-08

TABLE2

ESN---ShipDate
123---2010-09-28 
123---2011-02-23 

Select 
	a.DateReturn
	max(b.shipdate)
from Table1 as a
left outer join table2 as b
on a.esn=b.esn
and b.shipdate <= returndate

Thank you.

JOV

Edited by jovillanuev because: n/a
  • 1 Contributor
  • 1 Reply
  • 94 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by jovillanuev
Member Avatar for jovillanuev

I got it. here is my script..any comments. or other option. thanks.

Select
a.DateReturn
max(b.shipdate)
from Table1 as a
left outer join table2 as b
on a.esn=b.esn
and b.shipdate <= returndate

Result:
123---2011-02-23

Edited by jovillanuev because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.