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
  • 106 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by jovillanuev

Dani AI

Generated

Nice work, — the left-join + aggregate approach is a common pattern, but a couple of practical points and an alternative that’s often clearer and faster.

The aggregate approach requires you to GROUP BY any non-aggregated columns (for example ESN and DateReturn), and it can be awkward if you want to preserve rows that have no prior ShipDate (you need a LEFT form). A simpler, easy-to-read pattern uses OUTER APPLY with TOP(1) to pull the single nearest ShipDate before the return date; it naturally returns NULL when there is no prior ship date and avoids GROUP BY.

Example (uses OUTER APPLY + TOP 1):

SELECT t1.ESN, t1.DateReturn, prev.ShipDate AS NearestShipDate
FROM Table1 t1
OUTER APPLY (
  SELECT TOP (1) ShipDate
  FROM Table2 t2
  WHERE t2.ESN = t1.ESN
    AND t2.ShipDate < t1.DateReturn  -- use <= if same-day should count
  ORDER BY t2.ShipDate DESC
) prev;

Performance and correctness tips:

  • Add a nonclustered index on (ESN, ShipDate) so the TOP 1 search is fast.
  • If ShipDate can include a time component, normalize or truncate consistently before comparing.
  • If multiple rows can tie for the latest ShipDate, add a deterministic tie-breaker in ORDER BY (for example a surrogate key).
  • For set-based alternatives on newer SQL Server versions consider window functions (ROW_NUMBER or LAG) — see the ROW_NUMBER and TOP documentation for details: ROW_NUMBER and TOP / APPLY usage.
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.