how to obtain closest date

Question

jovillanuev 0 Junior Poster in Training

15 Years Ago

Hi All,

Couldnt figure out to obtain the nearest date in query properly.
find below the sample.

Insert Fsample (ESN varchar(10), optn_type int, auditdate datetime)
values('123',2,'2011-1-03')
values('123',11,'2011-2-20')
values('123',2,'2011-03-02')
values('123',2,'2011-4-10')
values('123',11,'2011-5-18')

2=shipdate
11=returndate

result
ESN|Shipdate|returndate
123|2011-01-03|2011-02-02
123|2011-02-20
123|2011-04-10|2011-05-18

Thank you in advance.

Jonel

microsoft:sql-server

4 Contributors
9 Replies
118 Views
3 Days Discussion Span
Latest Post 15 Years Ago Latest Post by jovillanuev

Dani AI

Generated 9 Months Ago

The behaviour you want is: for every ship row (optn_type = 2) show the next return (optn_type = 11) that occurs on or after the ship date — but only if that return also falls inside the reporting window; otherwise show NULL. That is exactly what ’s approach was trying to do, and ’s point about a transaction identifier is also valid: if events are ambiguous you should model them so they can be paired deterministically.

Why you saw the June return included: the correlated lookup was choosing the minimum return date >= ship date without limiting that lookup to the report end. So a ship on 2011-05-18 will happily match the 2011-06-10 return even though the report ends 2011-05-31. The NOT EXISTS piece that used prevents assigning a return that is actually meant for a later ship by checking there is no intervening ship between the current ship and the candidate return.

Two practical fixes (pick one):

Constrain the candidate-return search to the report end date so the lookup only considers returns <= @ReportEndDate.
Use OUTER APPLY (SQL Server) to pick the TOP 1 return per ship while applying both the >= ship date and <= @ReportEndDate conditions. Example pattern:

SELECT s.esn, s.auditdate AS ShipDate, r.RetDate
FROM fsample s
OUTER APPLY (
  SELECT TOP 1 auditdate AS RetDate
  FROM fsample r2
  WHERE r2.esn = s.esn
    AND r2.optn_type = 11
    AND r2.auditdate >= s.auditdate
    AND r2.auditdate <= @ReportEndDate
  ORDER BY r2.auditdate
) r
WHERE s.optn_type = 2
  AND s.auditdate BETWEEN @ReportStartDate AND @ReportEndDate;

Performance/model tips: add a composite index (esn, optn_type, auditdate) to speed the lookup, test edge cases (multiple ships before a return, missing returns), and consider adding a transaction/group id if business rules require explicit pairing.

All 9 Replies

debasisdas 580 Posting Genius

15 Years Ago

so what is the problem ?

BitBlt 452 Practically a Master Poster

15 Years Ago

I have a solution (I think), but shouldn't your "Results" look like this?

right result
ESN|Shipdate  |returndate
123|2011-01-03|2011-02-20
123|2011-03-02|NULL
123|2011-04-10|2011-05-18

Please confirm if this is the case, then I will post what I have.

Also, in the spirit of "Help those who have at least have tried", please post what you have already tried.

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

You must add one transaction id column.
say
Fsample (ESN varchar(10), transId int, optn_type int, auditdate datetime)

depening only on query may generate misleading report, if some data is missing.
In above case if data is missing, then it will show null instead of closest date.

Edited 15 Years Ago by urtrivedi because: n/a

Reply to this topic

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.

jovillanuev 0 Junior Poster in Training · Answer 1 · 2011-06-30T08:53:51+00:00

Hi BitBit,

Yes. this is the right result im looking for.
Please. i need your help.

Thank you in advance.
JOnel

BitBlt 452 Practically a Master Poster Featured Poster · Answer 2 · 2011-06-30T20:38:01+00:00

Nah...Here's some code that actually does the trick for the sample data (yes, I tested it):

select * 
from fsample Tb1
left join fsample tb2
on tb1.esn = tb2.esn
and tb2.optn_type = 11
and tb2.auditdate  in
(select min(auditdate)
from fsample tb3 
where tb3.esn = tb1.esn
and tb3.optn_type = 11
and tb3.auditdate >= tb1.auditdate
and not exists
(
select 1
from fsample tb4
where tb4.esn = tb1.esn
and tb4.optn_type = tb1.optn_type
and tb4.auditdate > tb1.auditdate
and tb4.auditdate < tb3.auditdate
)
)
where tb1.optn_type = 2

Not pretty, but it works.

jovillanuev 0 Junior Poster in Training · Answer 3 · 2011-07-01T10:23:28+00:00

Hi BItBits,

Thank you very much. it's great. it's working. Thumbs up.!!!!

I have some questions. i will pull this data based on Auditdate or shipdate (optn_type=2). i encounter that there is an issue that even June transaction returndate is included. its possible that this could be a blank. pleas see that sample below.

)
)
Change this portion already.
where tb1.audit_date between('2011-01-01') and ('2011-05-31')
and tb1.optn_type = 2

SAMPLE

This is the right result
ESN|Shipdate |returndate
123|2011-01-03|2011-02-20
123|2011-03-02|NULL
123|2011-04-10|NULL
-------------------------
123|2011-04-10|2011-06-18

the 2011-06-18(returndate) should not be included in the result, it should be null values. btw can you explain what is this portion.

AND NOT EXISTS(SELECT 1FROM fsample tb4WHERE tb4.esn = tb1.esnAND tb4.optn_type = tb1.optn_typeAND tb4.auditdate > tb1.auditdateAND tb4.auditdate < tb3.auditdate

Thanks you.

Jonel

jovillanuev 0 Junior Poster in Training · Answer 4 · 2011-07-01T10:28:08+00:00

You must add one transaction id column.
say
Fsample (ESN varchar(10), transId int, optn_type int, auditdate datetime)
depening only on query may generate misleading report, if some data is missing.
In above case if data is missing, then it will show null instead of closest date.

There is no transid in the table.
the script of BitbIt is working now but my concern is you said instead of null values
it show the closest date. maybe it can tweak in the date cause i have to pull this based on audit_date(shipdate). thanks for your reply.

Jonel

BitBlt 452 Practically a Master Poster Featured Poster · Answer 5 · 2011-07-01T20:45:46+00:00

...can you explain what is this portion.

What about it is unclear? If it is the "select 1 from", that causes no data to be retrieved physically...it is simply a check to see if the data exists. If I had done "select returndate from" or something like that, it would have physically returned an unneeded result. On small tables it doesn't make much difference, but against larger tables it might have significant impact.

As far as not including the other date when it should be null, you did not specify any further selection criteria in your original scenario. If you have to check on the audit date for both the ship date and return date, just include that as part of the selection criteria inside the appropriate correlated subselect.

jovillanuev 0 Junior Poster in Training · Answer 6 · 2011-07-02T16:07:26+00:00

Hi BitBIt,

Initially the script that you've given is running and the expected result is given.
i will give you another scenario but the result is the same only the date that not validate by the where clause assign will not be display in the reports or will not capture. see the below sample.

SELECT *
      FROM fsample Tb1
      LEFT JOIN fsample tb2
      ON tb1.esn = tb2.esn
      AND tb2.optn_type = 11
      AND tb2.auditdate IN
     (SELECT min(auditdate)
      FROM fsample tb3
      WHERE tb3.esn = tb1.esn
      AND tb3.optn_type = 11
      AND tb3.auditdate >= tb1.auditdate
      AND NOT EXISTS
     (
     SELECT 1
     FROM fsample tb4
     WHERE tb4.esn = tb1.esn
     AND tb4.optn_type = tb1.optn_type
     AND tb4.auditdate > tb1.auditdate
     AND tb4.auditdate < tb3.auditdate
     )
     )
     WHERE tb1.optn_type = 2
select * from fsample Tb1 left join fsample tb2 on tb1.esn = tb2.esn and tb2.optn_type = 11 and tb2.auditdate in (select min(auditdate) from fsample tb3 where tb3.esn = tb1.esn and tb3.optn_type = 11 and tb3.auditdate >= tb1.auditdate and not exists ( select 1 from fsample tb4 where tb4.esn = tb1.esn and tb4.optn_type = tb1.optn_type and tb4.auditdate > tb1.auditdate and tb4.auditdate < tb3.auditdate ) )
    where tb1.auditdate between('2011-01-01') and ('2011-05-31')   --- Just include this portion
    and  tb1.optn_type = 2

Insert Fsample (ESN varchar(10), optn_type int, auditdate datetime)
values('123',2,'2011-1-03') --Shipdate
values('123',11,'2011-2-20') --Returndate
values('123',2,'2011-03-02') --shipdate
values('123',11,'2011-4-10') --Returndate
values('123',2,'2011-5-18') --Shipdate
values('123',11,'2011-6-10') --returndate

this is the equivalent of numbers:
the value of 2 is = Shipdate
the 3 is = Returndate

RIGHT result
ESN|Shipdate |returndate
123|2011-01-03|2011-02-20
123|2011-03-02|2011-04-10
123|2011-05-18|NULL

The last record should not be inluded in the result.
It should be until 2011-05-18 because the 2011-06-10 is out of the date range that is declare to obtain the records.

Thank you very much.

Jonel

how to obtain closest date

Dani AI

Recommended Answers Collapse Answers

All 9 Replies

Recommended Answers