asked on

SQL Query Syntax Join

Hello all,

I have two tables as follows:

Table: RawFileImportLog
Fields:
FileDate
SurveyDate

Table: FileImportLog
Fields:
FileDate
SurveyDate

Each day files will be loading multiple times a day and there will be a import log record in the RawFileImportLog. I need a query that will look in the RawFileImportLog table for any record FileDate, SurveyDate where there is either no record in the FileImportLog for the paired FileDate, SurveyDate OR there is a same FileDate but the RawFileImportLog SurveyDate is > the SurveyDate for that same FileDate in the FileImportLog. I want to return the FileDate and SurveyDate pairs I will need to load. The SurveyDate is a datetime. Hope this makes sense.

Pawan Kumar

Hi,
can you please provide few rows and the expected output?

sbornstein2

ASKER

Example when started:

Day 1:
RawFileImportLog
FileDate SurveyDate
1/8/17 1/8/17 7:00PM
1/9/17 1/9/17 6:45PM
1/10/17 1/10/17 7:15AM

FileImportLog
FileDate SurveyDate
1/8/17 1/8/17 7:00PM
1/9/17 1/9/17 6:45PM
1/10/17 1/10/17 7:15AM

Day 2
RawFileImportLog
FileDate SurveyDate
1/8/17 1/8/17 7:00PM
1/9/17 1/9/17 6:45PM
1/10/17 1/10/17 7:15AM
1/9/17 1/11/17 5:15PM
1/11/17 1/11/17 5:15PM

In this case I want to get into a query the 1/9/17 1/11/17 5:15PM record because it has the FileDate in the FileImportLog table but a > SurveyDate and then the new 1/11/17 1/11/17 5:15PM record. The result I want is actually to INSERT the 1/11/17 record into the FileImportLog table and UPDATE the 1/9/17 record SurveyDate to the later 1/11/17 5:15PM date. So on Day 2 it would look like:

FileImportLog
FileDate SurveyDate
1/8/17 1/8/17 7:00PM
1/9/17 1/11/17 5:15PM
1/10/17 1/10/17 7:15AM
1/11/17 1/11/17 5:15PM

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sbornstein2

ASKER

Perfect thanks