I'm hoping someone has a better way of populating a table than we currently have.
This is all happing in a MS SQL 2005 db.
Let's say we have a table we'll call customer_months (millions of rows in the table) and it contains the following columns:
Customer_Month smalldatetime (always MM/01/YYYY)
The rows look like this:
12345E1 01/01/2006 MonthType1
12345E1 02/01/2006 MonthType1
12345E1 03/01/2006 MonthType1
12345E1 04/01/2006 MonthType1
12345E1 03/01/2006 MonthType2
12345E1 04/01/2006 MonthType2
12345E1 08/01/2006 MonthType1
12345E1 09/01/2006 MonthType1
34404S1 02/01/2007 MonthType1
22237S1 08/01/2007 MonthType4
22237S1 09/01/2007 MonthType4
22237S1 03/01/2006 MonthType3
We then need to take this data and populate a table we'll call customer_month_spans containing the following columns:
Using our example data the table customer_month_spans would look like this once populated:
12345E1 01/01/2006 04/01/2006 4 MonthType1
12345E1 08/01/2006 09/01/2006 2 MonthType1
12345E1 03/01/2006 04/01/2006 2 MonthType2
34404S1 02/01/2007 02/01/2007 1 MonthType1
22237S1 08/01/2007 09/01/2007 2 MonthType4
22237S1 03/01/2006 03/01/2006 1 MonthType3
A couple of things to note:
-The purpose of populating customer_month_spans is to show a rolled up version of what is going on with a customer over a date range.
-We only want contiguous spans. For example 12345E1 has a MonthType1 of for 6 months but in two separate contiguous spans.
-Sometimes a span is only 1 month.
-A contiguous span can span years like 2006 to 2007 and we would want to show it as such. For example a span might be 09/01/2006 - 02/01/2008 which would produce 18 for Customer_Month_Span_Months_Count.
Our current way of populating Customer_Month_Spans is using a cursor to load a Customer_ID and a Month_Type then querying for the Min and Max Customer_Month where the Customer_ID = @Customer_ID and Month_Type = @Month_Type. Then figuring out if the spans are contiguous or not and inserting the proper row(s) into customer_month_spans based on what we find. As you can guess the process is extremely slow. I'm hoping someone has a query that is not cursor based.