Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Tamar Granor
  Where is Tamar Granor?
 Elkins Park
 Pennsylvania - United States
 Tamar Granor
 To: Jonel Villanueva
  Where is Jonel Villanueva?
 sta. rosa
 Philippines
 Jonel Villanueva
 Tags
Subject: RE: Help report script result
Thread ID: 310963 Message ID: 311110 # Views: 37 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Tuesday, June 28, 2011 10:29:10 PM         
   


> Im looking the nearest or closest date from shipdate to returndate.
>
>
> Insert into TABLE1(ESN varchar(10), Optn_type int, Auditdate datetime)
> Values ('123','2','1-20-2011') --this is shipdate
> Values ('123','11','2-10-2011') --returndate
> Values ('123','2','3-25-2011') --shipdate
> Values ('123','2','4-30-2011') --shipdate
> Values ('123','11','5-15-2011') --returndate
>
> The result should be like this:
> ESN|Shipdate|Returndate
> -----------------------------------
> 123|1-20-2011|2-10-2011
> 123|3-25-2011
> 123|4-30-2011|5-15-2011
>
> 2=shipdate
> 11=returndate

So you'll need something like:

SELECT SD.ESN, SD.AuditDate, RD.AuditDate ;
  FROM (SELECT ESN, AuditDate ;
          FROM Table1 T1SD ;
          WHERE Optn_Type='2') SD ;
    JOIN ;
       (SELECT ESN, AuditDate ;
          FROM Table1 T1RD ;
          WHERE Optn_Type='11') RD ;
      ON SD.ESN = RD.ESN ;
       AND SD.AuditDate < RD.AuditDate ;
       AND NOT EXISTS ;
         (SELECT ESN ;
            FROM Table1 T1Sub ;
            WHERE T1Sub.ESN = T1SD.ESN ;
              AND T1Sub.ESN = T2RD.ESN ;
              AND T1Sub.OptnType = '2' ;
              AND T1Sub.AuditDate BETWEEN T1SD.AuditDate AND T1RD.AuditDate))


This is untested, so may not be exactly right, but the basic idea is that you use derived tables to separate the ship records from the return records, then match them up and keep only the ones that have the closest dates.

Tamar

ENTIRE THREAD

Help report script result Posted by Jonel Villanueva @ 6/27/2011 2:34:56 PM
RE: Help report script result Posted by Tamar Granor @ 6/27/2011 10:18:42 PM
RE: Help report script result Posted by Jonel Villanueva @ 6/28/2011 12:07:31 AM
RE: Help report script result Posted by Jonel Villanueva @ 6/28/2011 2:08:50 PM
RE: Help report script result Posted by Tamar Granor @ 6/28/2011 10:29:10 PM
RE: Help report script result Posted by Jonel Villanueva @ 7/3/2011 1:35:05 PM