Partitioning query with a reset count

Database
N/A

Partitioning query with a reset count

Background:

I have a history (or duration) table which has an EFFT_D and EXPY_D date field to show which is the effective record for any point in time. There are overlapping records which I need to identify and then recalculate the EFFT_D/EXPY_D dates so there are no overlaps, taking into account any number of overlapping records and different scenarios.  Identifying the overlaps is not hard by using the OVERLAPS function to get all the records. Then I can join to the Calendar table to break it down to a daily granularity, and rank it by the relevant columns to get one record for each day. The issue I am having is to then aggregate the table back down to get the duration records again. When there are two records which overlap at the start or end then it is easy to just do a MIN and MAX Period_Date partitioning by the primary key values to get the new EFFT_D/EXPY_D values. When there are more complex overlaps then I am having difficulty.

Problem:

One of the problems comes when one record is completely contained within the dates of the other record. An example of this would be:

Account Number

Type

From_Date

To_Date

Period_Date

A1

R

6/09/2013

10/09/2013

6/09/2013

A1

R

6/09/2013

10/09/2013

7/09/2013

A1

T

8/09/2013

8/09/2013

8/09/2013

A1

R

6/09/2013

10/09/2013

9/09/2013

A1

R

6/09/2013

10/09/2013

10/09/2013

The 3rd record has a different Type code of 'T' not 'R'. What I need to do is get the maximum and minimum Period_Date values for each of the 3 groups of data, resetting the count every time the Type field changes. Note that this means that the order of the rows is important as they have to be ordered by the Period_Date. I have tried doing Group By and partition by but this always ends up with the date range of ‘6/09/2013’ to ‘10/09/2013’ as this cant recognise that there are actually two groups on either side of the different record.

What I need is to turn this:

Account Number

Type

From_Date

To_Date

Period_Date

A1

R

6/09/2013

10/09/2013

6/09/2013

A1

R

6/09/2013

10/09/2013

7/09/2013

A1

T

8/09/2013

8/09/2013

8/09/2013

A1

R

6/09/2013

10/09/2013

9/09/2013

A1

R

6/09/2013

10/09/2013

10/09/2013

Into the desired outcome of:

Account Number

Type

From_Date

To_Date

A1

R

6/09/2013

7/09/2013

A1

T

8/09/2013

8/09/2013

A1

R

9/09/2013

10/09/2013

Does anyone have any suggestions as to how this could be done?! The data is in a table, so it can be joined to itself. I have to use a query, so I cant do a stored procedure or anything like that. Thanks for any ideas!

1 REPLY
Junior Contributor

Re: Partitioning query with a reset count

What's your TD release?

There have been multiple threads on normalising overlapping periods and your approach seems overly complex.

Could you please post the original data and the expected result in a more readable format?

Dieter