how to obtain closest date

[jovillanuev]

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

[debasisdas]

so what is the problem ?

[BitBlt]

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.

[jovillanuev]

Hi BitBit,

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

Thank you in advance.
JOnel

[urtrivedi]

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.

[BitBlt]

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]

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]

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]

...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]

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