I have a table that houses assignment attributes for people and the date ranges that each attribute was assigned for. The table has the following fields: PersonId, AssignmentType, AssignmentValue, StartDate, EndDate
Each person has an assignment type of racfId and cmsId which can independently start and end at arbitrary times. What i am trying to do is determine the start and end date of each unique combination of these two AssignmentTypes.
Ex.
CmsId - StartDate - EndDate
12345 - 7/29/2009 - 8/13/2009
RacfId - StartDate = EndDate
ABCDE - 8/4/2009 - 8/10/2009
BCDEF - 8/10/2009 - 8/13/2009
Result:
CmsId - RacfId - StartDate - EndDate
12345 - ABCDE - 8/4/2009 - 8/10/2009
12345 - BCDEF - 8/10/2009 - 8/13/2009
The only certainty that we have when it comes to the date ranges is that within each AssignmentType you will only have 1 assignment within any given date range.
I have been able to get very close by writing a query for each of these cases and doing a union on the results:
1: CmsId assignment date range falls within RacfId date range 2: RacfId assignment date range falls within CmsId date range 3: CmsId assignment starts within a RacfId date range but goes into another RacfId date range 4: RacfId assignment starts within a CmsId date range but goes into another CmsId date range
The query doesn't work for all cases though and is very cumbersome. I was hoping there would be some type of function out there that can take two date sets: The first data set would have RacfId's with their date ranges The second would have CmsId's with their date ranges You would pass the field name from each data set you want unique date range combinations for. The result set would return those two fields and the date ranges for each combination.
Please let me know if anyone has done something similar to this or if sql server 2005 has something built in that I could utilize. Thanks!