0

I have data in a table that has gotten cut up with multiple start and end dates. This is due to an error that has happened a few times in the last year. Apparently causing the system to not see the record already exists and move on, so the system ends the active record and adds a new one with a new start date.

This should only be happening when there's an actual change on the primary keys (Account and EffectiveStart). For now I just need a SQL to get to as few unbroken records as possible and MAX/MIN aren't quite getting me there as it's giving me an overlap between two records. Here is a sample:

Sample Data

Sample

My Query

SELECT Parent,Account,PersonID,TerritoryID, MIN(EffectiveStart), MAX(EffectiveEnd) from testingMaxandMinDates
Group by
Parent,Account,PersonID,TerritoryID

Bad result set (with overlapping)

Bad Result

Desired result set

The example data at the top shows the following behaviour:

  1. There are separate records showing where the account/parent were on one territory/person at first.
  2. Then the account/parent/territory moved to a different person.
  3. Then the account/parent moved to a different person and different territory.
  4. Then the account/parent/territory all moved back to the original person.

So the desired output should display the MIN(EffectiveStart) and MAX(EffectiveEnd) of each group of records where Account, Parent, PersID and Territory where (still) the same.

DesiredResults

Additional notes in case it's helpful:

  • Parent can be the same as Account# but is not always, Parent contains many Accounts including itself.
  • A TerritoryID can only have one person on it at a time, but the person can change over time.
  • Account can only belong to one Parent and one TerritoryID and one Rep at a time.
4

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.