How to create "fill in" rows for date ranges that have gaps?

View previous topic View next topic Go down

How to create "fill in" rows for date ranges that have gaps?

Post  cjportil on Wed Jul 14, 2010 5:58 pm

I need some help building some SQL that will create "fill in" rows for date ranges that have gaps. The data I'm working with looks something like this:

Student_ID EFF_DATE EXP_DATE
100 1/1/2009 5/1/2009
100 8/1/2009 12/31/2009
101 12/1/2009 4/1/2010
101 7/1/2010 9/1/2010
101 11/1/2010 12/1/2010
102 1/1/2010 6/1/2010

In this example students 100 and 101, both have gaps for their respective date ranges. I'll need to come up with some SQL that will return the following 3 rows:

Student ID 100: 5/2/2009-7/31/2009
Student ID 101: 4/2/2010-6/30/2010
Student ID 101: 9/2/2010-10/31/2010

Any ideas how I'll be able to code this in SQL?

cjportil

Posts : 6
Join date : 2010-07-14

View user profile

Back to top Go down

Re: How to create "fill in" rows for date ranges that have gaps?

Post  warrent on Fri Jul 16, 2010 4:31 pm

The following is based on code from a design tip I wrote last year on assigning end dates to historical rows in a dimension. It is Oracle based, and it uses a Customer_Master table, but I think it does what you are looking for.

INSERT INTO Customer_master
SELECT TabA.Customer_Key, TabA.Source_Cust_ID, TabA.First_Name, TabA.Last_Name,
TabA.Address, TabA.City, TabA.State, TabA.Zip,
TabA.End_Date + 1 AS Eff_Date, TabB.Eff_Date -1 AS End_Date, 'N', 'INS'
FROM
(SELECT ROW_NUMBER() OVER(Partition by Source_Cust_ID Order by Eff_Date) AS RowNumA,
Customer_Key, Source_Cust_ID, First_Name, Last_Name, Address, City, State,
Zip, Eff_Date, End_Date, Current_Flag, Change_Reason
from Customer_master ) TabA -- First row for a customer
LEFT OUTER JOIN
(SELECT ROW_NUMBER() OVER(Partition by Source_Cust_ID Order by Eff_Date) AS RowNumB,
Source_Cust_ID, Eff_Date, End_Date
from Customer_master) TabB -- Second row for the same customer
ON TabA.RowNumA = TabB.RowNumB - 1
AND TabA. Source_Cust_ID = TabB. Source_Cust_ID
WHERE TabB.Eff_Date - TabA.End_Date > 1; -- Must have more than one day difference

Hope this helps,
--Warren
avatar
warrent

Posts : 41
Join date : 2008-08-18

View user profile

Back to top Go down

Re: How to create "fill in" rows for date ranges that have gaps?

Post  cjportil on Thu Aug 19, 2010 1:37 pm

Thanks Warren! This works perfectly!

cjportil

Posts : 6
Join date : 2010-07-14

View user profile

Back to top Go down

Re: How to create "fill in" rows for date ranges that have gaps?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum