I have a spreadsheet with columns of data that need multiple match criteria to merge (using FeatureMerger) with SQL DB records, then within the spreadsheet, there are 2 columns for Start and End Date ranges, and within the DB records is a specified date/time. What would be the best way to merge the Features from both sources, matching the other criteria as an exact match, but matching the date to a range?
Solved
Merging Features with Date Ranges
+1- Contributor
Best answer by ebygomm
@jmhomza
The query should be structured something like this, make sure your dates are already formatted correctly
SELECT DateOfServiceEnd, DateOfServiceStart, Enrollment_FirstName, SQL.Service
FROM Excel, SQL
where SQL.FirstName=Excel.Enrollment_FirstName
AND SQL.Service=Excel.Service
AND SQL.DateOfService BETWEEN Excel.DateOfServiceStart AND Excel.DateOfServiceEnd
+51- Safer
One way I'd be tempted to try is to do a merge using the exact match criteria, and then drop all the unnecessary matches using a simple test to check the date.
For example...
- Use the FeatureMerger.
- Turn on Process Duplicate Suppliers.
- Turn on Generate List.
- Use a ListExploder to turn the list into separate features.
- Use a Tester to test if DB date > Spreadsheet Start Date AND DB date < Spreadsheet End Date
But otherwise, as @ebygomm suggests, the InlineQuerier is the best way to do all of the work in a single join operation. As you'll have noticed, neither the FeatureMerger or the FeatureJoiner (this transformer gives better performance, by the way) have the option to join on a date or numeric range.
+1- Contributor
mark2atsafe wrote:
One way I'd be tempted to try is to do a merge using the exact match criteria, and then drop all the unnecessary matches using a simple test to check the date.
For example...
- Use the FeatureMerger.
- Turn on Process Duplicate Suppliers.
- Turn on Generate List.
- Use a ListExploder to turn the list into separate features.
- Use a Tester to test if DB date > Spreadsheet Start Date AND DB date < Spreadsheet End Date
But otherwise, as @ebygomm suggests, the InlineQuerier is the best way to do all of the work in a single join operation. As you'll have noticed, neither the FeatureMerger or the FeatureJoiner (this transformer gives better performance, by the way) have the option to join on a date or numeric range.
Hi @mark2atsafe. I'm attempting to use the InlineQuerier because the date is what makes the record unique, but having some difficulty with the syntax...
From my spreadsheet, I have 4 columns of exact match criteria - FNname, LName, Service, and Group. Then, there are 2 columns - DateStart and DateEnd - that will make the record match unique (multiple options for the joining data, based off date) . I need to join all attributes from both sources using exact match for the first 4 and the date from SQL that falls within the range of dates in Excel. I'm not a query expert, so any help is appreciated.
SELECT DateOfServiceEnd, DateOfServiceStart, Enrollment_FirstName, Enrollment_LastName, Service, Enrollment_IndividualOrGroup
FROM Excel
JOIN SQL
ON SQL.FirstName=Excel.Enrollment_FirstName AND SQL.LastName=Excel.Enrollment_LastName AND SQL.Service=Excel.Service AND SQL.IndividualorGroup=Excel.Enrollment_IndividualOrGroup
WHERE SQL.DateOfService BETWEEN Excel.DateOfServiceStart AND Excel.DateOfServiceEnd
+40- Influencer
- Best Answer
@jmhomza
The query should be structured something like this, make sure your dates are already formatted correctly
SELECT DateOfServiceEnd, DateOfServiceStart, Enrollment_FirstName, SQL.Service
FROM Excel, SQL
where SQL.FirstName=Excel.Enrollment_FirstName
AND SQL.Service=Excel.Service
AND SQL.DateOfService BETWEEN Excel.DateOfServiceStart AND Excel.DateOfServiceEnd
- Contributor
ebygomm wrote:
@jmhomza
The query should be structured something like this, make sure your dates are already formatted correctly
SELECT DateOfServiceEnd, DateOfServiceStart, Enrollment_FirstName, SQL.Service
FROM Excel, SQL
where SQL.FirstName=Excel.Enrollment_FirstName
AND SQL.Service=Excel.Service
AND SQL.DateOfService BETWEEN Excel.DateOfServiceStart AND Excel.DateOfServiceEnd
Thanks, @ebygomm and @mark2atsafe, it works perfectly!
Reply
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.