Perhaps Iam missing something somewhere...
May be the Lookup might be the confusion. It should be Join (With cross product) Full outer.
Code: Select all
If Input.Start > Lookup.Start And Input.End<Lookup.End Then 1 Else 0
Wouldn't the above code results to 1 for the first scenario that you had given and 0 for second.
For Third OP has to decide wheather its a duplicate or not.
For a each set of keys, there will be a cross product join.
For two records set for given key 1 and Value a There will be four records to operate.
Eg
Code: Select all
key value Inp.Start Inp.End Lkp.Start Lkp.End Inp.Unique Lkp.Unique
1 a 1/1/2010 12/31/2010 1/1/2010 12/31/2010 1234 1234
1 a 1/1/2011 12/31/2011 1/1/2010 12/31/2010 1235 1234
1 a 1/1/2010 12/31/2010 1/1/2011 12/31/2011 1234 1235
1 a 1/1/2011 12/31/2011 1/1/2011 12/31/2011 1235 1235
Let the Unique field be either other available key in the input or self created one. So the records with
has to discarded at first setp.
With the above records set, would we be albe to find the duplicates?
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'