Query to split date range into multiple rows based on date insertion - help

Question

Query to split date range into multiple rows based on date insertion - help

rubyzou88

SSC Veteran

Points: 203
More actions
October 10, 2014 at 8:34 pm

#291474

I need a way to split rows with date range into multiple rows based on date insertion. I have a table that has a memberID, Startdate, Enddate. But when a new Member is added later, all previous Member records will split into multiple rows based on Startdate of new addition. If the new addition is in the middle of month, the month will need to futher split into two record. All the newly added lines for M1 with have a 'N' status'
Example, input will start from a member table
Member , Start date, End date, Status
M1 1/1/2014 12/31/2014 Y
M2 5/1/2014 12/31/2014 Y
Output I need:
M1 1/1/2014 4/30/2014 Y
M1 5/1/2014 12/31/2014 N
M2 5/1/2014 12/31/2014 Y
Example with mid-month split
What i have:
M1 1/1/2014 12/31/2014 Y
M2 4/14/2014 12/31/2014 Y
Output I need:
M1 1/1/2014 3/31/2014 Y
M1 4/1/2014 4/13/2014 N
M2 4/14/2014 4/30/2014 N
M4 5/1/2014 12/31/2014 N
M2 5/1/2014 12/31/2014 Y
All previous records before the new member add with need to be splitted
M1 1/1/2014 12/31/2014 Y
M2 1/1/2014 12/31/2014 Y
M3 5/12/2014 12/31/2014 Y
M4 5/12/2014 12/31/2014 Y
Result for the the above table will have 4 M1 rows, plus 4 M2 rows, and 1 M3 and 1 M4. Only last 3 rows has status of 'N', every other row has 'Y' status. However, if there is a M5 Rows with a even later StartDate such as 07/01/14 to 12/31/2014, it cause futher split from last row of M1 and last row of M2 , and M3, M4, each split into two rows based on the new StartDate introduced. (each split into 6/1/2014 to 6/30/2014 and 7/1/2014 to 12/31/2014)
Split rows based on StartDate is the first step. The 2nd step is to split based on the endDate. Original M1's StartDate is always the smallest and M1's EndDate is always the largest and all Member rows's EndDate is expected to be the same with M1's EndDate. However, if a Member's EndDate is less than M1' EndDate, it cause a split of other Members records in the table
Example:
M1 1/1/2014 12/31/2014 Y
M2. 1/1/2014 10/9/2014 Y
Resultset will be:
M1 1/1/2014 9/30/2014 Y
M1 10/1/2014 10/9/2014 N
M1 10/10/2014 10/31/2014 N
M1 11/1/2014 12/31/2014 N
M2 1/1/2014 10/9/2014 Y
Please help if you good suggestion how to achieve the cure

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

Example, input will start from a member table
Member , Start date, End date, Status
M1 1/1/2014 12/31/2014 Y
M2 5/1/2014 12/31/2014 Y
Output I need:
M1 1/1/2014 4/30/2014 Y
M1 5/1/2014 12/31/2014 N
M2 5/1/2014 12/31/2014 Y

This is unclear and confusing. Are you saying that you begin with one row in the table

M1 1/1/2014 12/31/2014 Y

and when you add a new member M2 you end up with this in the table

M1 1/1/2014 4/30/2014 Y

M1 5/1/2014 12/31/2014 N

M2 5/1/2014 12/31/2014 Y

Is this correct?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]