If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to Identify Records with Overlapping Dates
I need to be able to systematically identify any instance where there are
overlapping date ranges in a data set. I need to pull records like those listed below out of a larger data set. I previously posted a question similar to this and was advised to pull the same table in a query, match on Member Number, and qualify that the product code from TableA did not match product code in TableA_1 where the Begin Date TableA was Term date TableA_1 and TableA_1 Begin Date TableA End Date. Which worked great for me finding overlapping records where the product was different, but now I also need to find them when the product is the same ProductCode MemberNumber Begin Date End Date PFFS 123456789A 20090101 20091231 PFFS 123456789A 20081201 29991231 Thanks for you help |
#2
|
|||
|
|||
How to Identify Records with Overlapping Dates
You can remove the criteria that the products are different.
If there is a primary key field on the table, then you would want those to not match between TableA and TableA1, and it could be the product was the closest option to use. This would be to avoid the SAME record to match the from TableA to TableA_1, but with your criteria, you should be fine. -- Daryl S "DudeGenius" wrote: I need to be able to systematically identify any instance where there are overlapping date ranges in a data set. I need to pull records like those listed below out of a larger data set. I previously posted a question similar to this and was advised to pull the same table in a query, match on Member Number, and qualify that the product code from TableA did not match product code in TableA_1 where the Begin Date TableA was Term date TableA_1 and TableA_1 Begin Date TableA End Date. Which worked great for me finding overlapping records where the product was different, but now I also need to find them when the product is the same ProductCode MemberNumber Begin Date End Date PFFS 123456789A 20090101 20091231 PFFS 123456789A 20081201 29991231 Thanks for you help |
#3
|
|||
|
|||
How to Identify Records with Overlapping Dates
You can remove the criteria that the products are different.
If there is a primary key field on the table, then you would want those to not match between TableA and TableA1, and it could be the product was the closest option to use. This would be to avoid the SAME record to match the from TableA to TableA_1, but with your criteria, you should be fine. -- Daryl S "DudeGenius" wrote: I need to be able to systematically identify any instance where there are overlapping date ranges in a data set. I need to pull records like those listed below out of a larger data set. I previously posted a question similar to this and was advised to pull the same table in a query, match on Member Number, and qualify that the product code from TableA did not match product code in TableA_1 where the Begin Date TableA was Term date TableA_1 and TableA_1 Begin Date TableA End Date. Which worked great for me finding overlapping records where the product was different, but now I also need to find them when the product is the same ProductCode MemberNumber Begin Date End Date PFFS 123456789A 20090101 20091231 PFFS 123456789A 20081201 29991231 Thanks for you help |
#4
|
|||
|
|||
How to Identify Records with Overlapping Dates
You can remove the criteria that the products are different.
If there is a primary key field on the table, then you would want those to not match between TableA and TableA1, and it could be the product was the closest option to use. This would be to avoid the SAME record to match the from TableA to TableA_1, but with your criteria, you should be fine. -- Daryl S "DudeGenius" wrote: I need to be able to systematically identify any instance where there are overlapping date ranges in a data set. I need to pull records like those listed below out of a larger data set. I previously posted a question similar to this and was advised to pull the same table in a query, match on Member Number, and qualify that the product code from TableA did not match product code in TableA_1 where the Begin Date TableA was Term date TableA_1 and TableA_1 Begin Date TableA End Date. Which worked great for me finding overlapping records where the product was different, but now I also need to find them when the product is the same ProductCode MemberNumber Begin Date End Date PFFS 123456789A 20090101 20091231 PFFS 123456789A 20081201 29991231 Thanks for you help |
#5
|
|||
|
|||
How to Identify Records with Overlapping Dates
This select record that has another record overlapping it --
SELECT ProductCode, MemberNumber, [Begin Date], [End Date] FROM TableA WHERE TableA.[End Date] (SELECT TOP 1 [TableA_1].[Begin Date] FROM TableA AS TableA_1 WHERE [TableA_1].[Begin Date] = [TableA].[Begin Date] ORDER BY [TableA_1].[Begin Date] DESC); -- Build a little, test a little. "DudeGenius" wrote: I need to be able to systematically identify any instance where there are overlapping date ranges in a data set. I need to pull records like those listed below out of a larger data set. I previously posted a question similar to this and was advised to pull the same table in a query, match on Member Number, and qualify that the product code from TableA did not match product code in TableA_1 where the Begin Date TableA was Term date TableA_1 and TableA_1 Begin Date TableA End Date. Which worked great for me finding overlapping records where the product was different, but now I also need to find them when the product is the same ProductCode MemberNumber Begin Date End Date PFFS 123456789A 20090101 20091231 PFFS 123456789A 20081201 29991231 Thanks for you help |
Thread Tools | |
Display Modes | |
|
|