Temporal Query Problem

Question

Post reply

Temporal Query Problem

dataczar

SSC Enthusiast

Points: 182
More actions
February 15, 2010 at 10:36 am

#219205

I am trying to find a way to limit the amount of data coming through my cte and possibly perform the aggregation in a more efficient manner.

Here is the sql that I am using. I then join the cte to itself so that I can compare the current row with the prior. The end result should calculate the beginning balance + new claims + reopen claims - closed claims to arrive at an ending balance for each month of 2009.

For example, if a claim is closed at the beginning and remains closed throughout the reporting period, then it would be better for these rows never to come through the CTE.

Any help with this would be appreciated.

declare @Report_Begin_Date as datetime

declare @Report_End_Date as datetime

set @Report_Begin_Date = '20090101'

set @Report_End_Date = '20091231';

-- Create a temporary calendar table for only those dates requested for the report

select calendar.calendar_date

into #cal

from edw.dbo.calendar

where calendar.calendar_date between dateadd(mm,-1,@Report_Begin_Date) and @Report_End_Date

and calendar.month_end_indicator = 'Y';

with claims_cte (row_id

,year_number

,month_number

,office_name

,claim_number

,claim_status_group_text

)

as

(

select row_number() over(partition by claims.claim_number order by claims.claim_number

,year(calendar.calendar_date)

,month(calendar.calendar_date)) as row_id

,year(calendar.calendar_date) as year_number

,month(calendar.calendar_date) as month_number

,claims.team_name as office_name

,claims.claim_number

,claim_statuses_vw.claim_status_group_text

from #cal as calendar

inner join edw.dbo.claims on calendar.calendar_date between claims.row_begin_date and claims.row_end_date

inner join edw.dbo.claim_statuses_vw on claims.claim_number = claim_statuses_vw.claim_number

and calendar.calendar_date between claim_statuses_vw.row_begin_date and claim_statuses_vw.row_end_date

select *

from claims_cte as curr

left outer join claims_cte as pr on curr.claim_number = pr.claim_number

and curr.row_id = pr.row_id + 1

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Step one (increase performance):

Since you do a self join on your cte you actually perform your three-table-join twice.

Maybe it's more efficient to store the result of your cte into a separate temp table and use that one for the self join.

Step two (eliminate permanently closed claim):

I'd either add another join to the "three-table-join" to a subselect

holding the number of rows per claim to be different than 'closed' and add a clause "WHERE counts>0" or I'd reduce the result of the cte with such a count function on the cte itself.

Unfortunately, we don't have any table structure nor sample data nor approx. number of rows for the tables involved in your query so we cannot provide a tested code. (at least I do not provide untested code...)

If the verbal advice from above won't help you please provide sample data as described in the first link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

You should pre-aggregate (thank you Peso) the data by month and status and dump it into a temp table. Then, even the worst performing running total method will work in a comparative flash.

If you want to see what I mean, live up to your handle of "DataCzar" and post the data IAW the method shown in the article at the first link in my signature below. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

dataczar SSC Enthusiast Points: 182 More actions · Answer 3

Jeff,

I think you are thinking of Peso's Subscription speed challenge query, which I have looked at but this has a few more complexities because you need to calculate new, reopened, and closed in addition to the beginning and ending balances.

I am happy to have you prove me wrong. 🙂

Below is the code to create two of the tables that were in the CTE. I removed the claims table because it was just there to split by location and not relevant to the issue. Below that code, you will find a revised query for my original post.

My claim_statuses table contains 110,000 rows and then becomes 700,000 rows when joined to the calendar table to get what it looked like at each month end for the 12 months.

Claims 26-35 are all examples of claims that should be exclude during the creation of the #claims temp table because they were closed before the reporting period and remained closed during the reporting period. Claims 45,51,55 should be counted as both new and closed in the month they came in.

SELECT '1' AS [claim_number], '2008-07-02 00:00:00.000' AS [row_begin_date], '2008-07-08 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-02 00:00:00.000' AS [claim_status_date] UNION ALL



SELECT '1' AS [claim_number], '2008-08-14 00:00:00.000' AS [row_begin_date], '2008-08-14 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-14 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-09-04 00:00:00.000' AS [row_begin_date], '2008-09-09 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-04 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-09-16 00:00:00.000' AS [row_begin_date], '2008-09-29 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-16 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-10-09 00:00:00.000' AS [row_begin_date], '2008-10-29 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-09 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2009-01-08 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-08 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-06-18 00:00:00.000' AS [row_begin_date], '2008-06-18 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-06-19 00:00:00.000' AS [row_begin_date], '2008-07-01 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-06-19 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-07-09 00:00:00.000' AS [row_begin_date], '2008-08-13 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-09 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-08-15 00:00:00.000' AS [row_begin_date], '2008-09-03 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-15 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-09-10 00:00:00.000' AS [row_begin_date], '2008-09-15 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-10 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-09-30 00:00:00.000' AS [row_begin_date], '2008-10-08 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-10-30 00:00:00.000' AS [row_begin_date], '2009-01-07 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '2' AS [claim_number], '2008-06-18 00:00:00.000' AS [row_begin_date], '2008-09-02 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '2' AS [claim_number], '2009-12-02 00:00:00.000' AS [row_begin_date], '2010-01-06 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-08-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '2' AS [claim_number], '2009-08-18 00:00:00.000' AS [row_begin_date], '2009-12-01 00:00:00.000' AS [row_end_date], '22' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-08-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '2' AS [claim_number], '2008-09-03 00:00:00.000' AS [row_begin_date], '2009-07-31 00:00:00.000' AS [row_end_date], '26' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2008-09-03 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '2' AS [claim_number], '2009-08-01 00:00:00.000' AS [row_begin_date], '2009-08-17 00:00:00.000' AS [row_end_date], '36' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-08-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '2' AS [claim_number], '2010-01-07 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2010-01-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2008-08-04 00:00:00.000' AS [row_begin_date], '2008-08-17 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-04 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2008-08-21 00:00:00.000' AS [row_begin_date], '2008-08-25 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2009-01-12 00:00:00.000' AS [row_begin_date], '2009-01-14 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-12 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2009-01-30 00:00:00.000' AS [row_begin_date], '2009-02-08 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2008-06-19 00:00:00.000' AS [row_begin_date], '2008-08-03 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-19 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2008-12-11 00:00:00.000' AS [row_begin_date], '2009-01-11 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2008-08-18 00:00:00.000' AS [row_begin_date], '2008-08-20 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2008-08-26 00:00:00.000' AS [row_begin_date], '2008-12-10 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-26 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2009-01-15 00:00:00.000' AS [row_begin_date], '2009-01-29 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-15 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2009-02-09 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-02-09 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '4' AS [claim_number], '2008-07-14 00:00:00.000' AS [row_begin_date], '2008-11-30 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-14 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '4' AS [claim_number], '2008-12-02 00:00:00.000' AS [row_begin_date], '2008-12-28 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-02 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '4' AS [claim_number], '2008-06-19 00:00:00.000' AS [row_begin_date], '2008-07-13 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-19 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '4' AS [claim_number], '2008-12-01 00:00:00.000' AS [row_begin_date], '2008-12-01 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-13 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '4' AS [claim_number], '2008-12-29 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-02 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '5' AS [claim_number], '2008-08-13 00:00:00.000' AS [row_begin_date], '2008-08-17 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-13 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '5' AS [claim_number], '2008-09-10 00:00:00.000' AS [row_begin_date], '2008-09-24 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-10 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '5' AS [claim_number], '2008-10-11 00:00:00.000' AS [row_begin_date], '2008-10-16 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '5' AS [claim_number], '2008-06-20 00:00:00.000' AS [row_begin_date], '2008-08-12 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-20 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '5' AS [claim_number], '2008-10-02 00:00:00.000' AS [row_begin_date], '2008-10-10 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-02 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '5' AS [claim_number], '2008-10-17 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-17 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '5' AS [claim_number], '2008-08-18 00:00:00.000' AS [row_begin_date], '2008-09-09 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '5' AS [claim_number], '2008-09-25 00:00:00.000' AS [row_begin_date], '2008-10-01 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-25 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '6' AS [claim_number], '2008-08-12 00:00:00.000' AS [row_begin_date], '2008-09-18 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-12 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '6' AS [claim_number], '2008-09-22 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-22 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '6' AS [claim_number], '2008-06-23 00:00:00.000' AS [row_begin_date], '2008-08-11 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-23 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '6' AS [claim_number], '2008-09-19 00:00:00.000' AS [row_begin_date], '2008-09-21 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-19 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '7' AS [claim_number], '2008-08-18 00:00:00.000' AS [row_begin_date], '2008-09-22 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '7' AS [claim_number], '2008-10-02 00:00:00.000' AS [row_begin_date], '2008-10-21 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-02 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '7' AS [claim_number], '2008-11-11 00:00:00.000' AS [row_begin_date], '2008-12-01 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-11-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '7' AS [claim_number], '2008-12-09 00:00:00.000' AS [row_begin_date], '2009-01-29 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-09 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '7' AS [claim_number], '2008-06-24 00:00:00.000' AS [row_begin_date], '2008-08-17 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-24 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '7' AS [claim_number], '2008-09-23 00:00:00.000' AS [row_begin_date], '2008-10-01 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-23 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '7' AS [claim_number], '2008-10-22 00:00:00.000' AS [row_begin_date], '2008-11-10 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-22 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '7' AS [claim_number], '2008-12-02 00:00:00.000' AS [row_begin_date], '2008-12-08 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-02 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '7' AS [claim_number], '2009-01-30 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '8' AS [claim_number], '2008-09-09 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-09 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '8' AS [claim_number], '2008-08-21 00:00:00.000' AS [row_begin_date], '2008-09-08 00:00:00.000' AS [row_end_date], '35' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '8' AS [claim_number], '2008-06-25 00:00:00.000' AS [row_begin_date], '2008-08-20 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-25 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '9' AS [claim_number], '2008-06-26 00:00:00.000' AS [row_begin_date], '2008-06-30 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-06-26 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '9' AS [claim_number], '2008-12-04 00:00:00.000' AS [row_begin_date], '2008-12-04 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-04 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '9' AS [claim_number], '2009-01-04 00:00:00.000' AS [row_begin_date], '2009-01-11 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-04 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '9' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-12-03 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '9' AS [claim_number], '2008-12-05 00:00:00.000' AS [row_begin_date], '2009-01-03 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-05 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '9' AS [claim_number], '2009-01-12 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-12 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '10' AS [claim_number], '2008-12-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '10' AS [claim_number], '2008-06-27 00:00:00.000' AS [row_begin_date], '2008-08-20 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-27 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '10' AS [claim_number], '2008-08-21 00:00:00.000' AS [row_begin_date], '2008-09-28 00:00:00.000' AS [row_end_date], '35' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '10' AS [claim_number], '2008-10-01 00:00:00.000' AS [row_begin_date], '2008-11-30 00:00:00.000' AS [row_end_date], '35' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '10' AS [claim_number], '2008-09-29 00:00:00.000' AS [row_begin_date], '2008-09-30 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-09-29 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '11' AS [claim_number], '2008-08-14 00:00:00.000' AS [row_begin_date], '2008-08-17 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-14 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '11' AS [claim_number], '2008-06-27 00:00:00.000' AS [row_begin_date], '2008-08-13 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-06-27 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '11' AS [claim_number], '2008-08-18 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '12' AS [claim_number], '2008-08-21 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '12' AS [claim_number], '2008-06-30 00:00:00.000' AS [row_begin_date], '2008-08-20 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '13' AS [claim_number], '2008-06-30 00:00:00.000' AS [row_begin_date], '2008-08-13 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '13' AS [claim_number], '2008-10-29 00:00:00.000' AS [row_begin_date], '2008-11-04 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-04 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '13' AS [claim_number], '2008-08-14 00:00:00.000' AS [row_begin_date], '2008-10-28 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-14 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '13' AS [claim_number], '2008-11-05 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-04 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '14' AS [claim_number], '2008-10-07 00:00:00.000' AS [row_begin_date], '2008-11-30 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '14' AS [claim_number], '2008-12-11 00:00:00.000' AS [row_begin_date], '2008-12-28 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '14' AS [claim_number], '2009-01-14 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-14 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '14' AS [claim_number], '2008-09-17 00:00:00.000' AS [row_begin_date], '2008-10-06 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-17 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '14' AS [claim_number], '2008-12-01 00:00:00.000' AS [row_begin_date], '2008-12-10 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '14' AS [claim_number], '2008-12-29 00:00:00.000' AS [row_begin_date], '2009-01-13 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-29 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '14' AS [claim_number], '2008-06-30 00:00:00.000' AS [row_begin_date], '2008-09-16 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2008-06-30 00:00:00.000' AS [row_begin_date], '2008-08-05 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2010-02-04 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2010-01-20 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2008-08-20 00:00:00.000' AS [row_begin_date], '2008-09-29 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-20 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2008-10-15 00:00:00.000' AS [row_begin_date], '2008-10-29 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-15 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2008-11-04 00:00:00.000' AS [row_begin_date], '2008-11-25 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-11-04 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2008-12-15 00:00:00.000' AS [row_begin_date], '2009-02-02 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-15 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2009-02-09 00:00:00.000' AS [row_begin_date], '2010-02-03 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-02-09 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2008-08-06 00:00:00.000' AS [row_begin_date], '2008-08-19 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-06 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2008-09-30 00:00:00.000' AS [row_begin_date], '2008-10-14 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2008-10-30 00:00:00.000' AS [row_begin_date], '2008-11-03 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2008-11-26 00:00:00.000' AS [row_begin_date], '2008-12-14 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-11-26 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2009-02-03 00:00:00.000' AS [row_begin_date], '2009-02-08 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-02-03 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '16' AS [claim_number], '2008-08-21 00:00:00.000' AS [row_begin_date], '2008-09-28 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '16' AS [claim_number], '2008-10-07 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '16' AS [claim_number], '2008-09-29 00:00:00.000' AS [row_begin_date], '2008-10-06 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-29 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '16' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-08-20 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '17' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-08-20 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '17' AS [claim_number], '2008-09-29 00:00:00.000' AS [row_begin_date], '2008-10-06 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-29 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '17' AS [claim_number], '2008-10-29 00:00:00.000' AS [row_begin_date], '2008-11-03 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '17' AS [claim_number], '2008-08-21 00:00:00.000' AS [row_begin_date], '2008-09-28 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '17' AS [claim_number], '2008-10-07 00:00:00.000' AS [row_begin_date], '2008-10-28 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '17' AS [claim_number], '2008-11-04 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '18' AS [claim_number], '2008-08-14 00:00:00.000' AS [row_begin_date], '2008-09-28 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-14 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '18' AS [claim_number], '2008-10-07 00:00:00.000' AS [row_begin_date], '2008-11-30 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '18' AS [claim_number], '2008-12-22 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-22 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '18' AS [claim_number], '2008-09-29 00:00:00.000' AS [row_begin_date], '2008-10-06 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-29 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '18' AS [claim_number], '2008-12-01 00:00:00.000' AS [row_begin_date], '2008-12-21 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-22 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '18' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-08-13 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2008-08-13 00:00:00.000' AS [row_begin_date], '2008-10-14 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-08-13 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-07-02 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2008-07-17 00:00:00.000' AS [row_begin_date], '2008-07-27 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-17 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2008-08-11 00:00:00.000' AS [row_begin_date], '2008-08-12 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2008-10-15 00:00:00.000' AS [row_begin_date], '2008-10-21 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-15 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2008-12-30 00:00:00.000' AS [row_begin_date], '2009-01-12 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2008-10-22 00:00:00.000' AS [row_begin_date], '2008-12-29 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-22 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2009-01-13 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-13 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2008-07-03 00:00:00.000' AS [row_begin_date], '2008-07-16 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-03 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2008-07-28 00:00:00.000' AS [row_begin_date], '2008-08-10 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-28 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '20' AS [claim_number], '2008-10-31 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-31 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '20' AS [claim_number], '2008-10-22 00:00:00.000' AS [row_begin_date], '2008-10-30 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-22 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '20' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-10-21 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '21' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-08-26 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '21' AS [claim_number], '2008-08-27 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-27 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '22' AS [claim_number], '2008-08-11 00:00:00.000' AS [row_begin_date], '2009-02-03 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '22' AS [claim_number], '2008-07-23 00:00:00.000' AS [row_begin_date], '2008-08-10 00:00:00.000' AS [row_end_date], '35' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-23 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '22' AS [claim_number], '2009-02-18 00:00:00.000' AS [row_begin_date], '2009-03-16 00:00:00.000' AS [row_end_date], '36' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-02-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '22' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-07-22 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '22' AS [claim_number], '2009-02-04 00:00:00.000' AS [row_begin_date], '2009-02-17 00:00:00.000' AS [row_end_date], '18' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-02-04 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '22' AS [claim_number], '2009-03-17 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-03-17 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '23' AS [claim_number], '2009-03-17 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-03-17 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '23' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-07-22 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '23' AS [claim_number], '2008-07-23 00:00:00.000' AS [row_begin_date], '2008-09-03 00:00:00.000' AS [row_end_date], '35' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-23 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '23' AS [claim_number], '2008-09-04 00:00:00.000' AS [row_begin_date], '2009-03-16 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-04 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '24' AS [claim_number], '2008-08-07 00:00:00.000' AS [row_begin_date], '2008-09-23 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '24' AS [claim_number], '2008-10-07 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '24' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-08-06 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '24' AS [claim_number], '2008-09-24 00:00:00.000' AS [row_begin_date], '2008-10-06 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-24 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2008-07-07 00:00:00.000' AS [row_begin_date], '2008-07-30 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2008-08-14 00:00:00.000' AS [row_begin_date], '2008-09-11 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-14 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2008-09-18 00:00:00.000' AS [row_begin_date], '2008-10-20 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2008-11-19 00:00:00.000' AS [row_begin_date], '2008-11-23 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-11-19 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2010-02-12 09:53:42.500' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2010-02-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2008-07-02 00:00:00.000' AS [row_begin_date], '2008-07-06 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-07-02 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2010-02-11 08:26:37.500' AS [row_begin_date], '2010-02-12 09:53:41.500' AS [row_end_date], '14' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2010-02-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2008-07-31 00:00:00.000' AS [row_begin_date], '2008-08-13 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-31 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2008-09-12 00:00:00.000' AS [row_begin_date], '2008-09-17 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-12 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2008-11-24 00:00:00.000' AS [row_begin_date], '2010-02-11 08:26:36.500' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-11-24 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2008-10-21 00:00:00.000' AS [row_begin_date], '2008-11-18 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '26' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2002-09-06 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '27' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2003-04-28 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '28' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '1996-11-22 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '29' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2002-03-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '30' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2005-04-27 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '31' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2005-02-28 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '32' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2002-07-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '33' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2001-07-13 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '34' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2003-12-15 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '35' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2000-02-14 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '36' AS [claim_number], '2009-01-26 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-01-26 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '36' AS [claim_number], '2009-01-07 00:00:00.000' AS [row_begin_date], '2009-01-19 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-01-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '36' AS [claim_number], '2009-01-20 00:00:00.000' AS [row_begin_date], '2009-01-25 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-20 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '37' AS [claim_number], '2009-01-31 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-02-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '37' AS [claim_number], '2009-01-07 00:00:00.000' AS [row_begin_date], '2009-01-30 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-01-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '38' AS [claim_number], '2009-01-08 00:00:00.000' AS [row_begin_date], '2009-03-02 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-01-08 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '38' AS [claim_number], '2009-07-21 00:00:00.000' AS [row_begin_date], '2009-10-01 00:00:00.000' AS [row_end_date], '19' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-06-16 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '38' AS [claim_number], '2009-10-02 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '19' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-07-09 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '38' AS [claim_number], '2009-03-03 00:00:00.000' AS [row_begin_date], '2009-05-27 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-03-03 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '38' AS [claim_number], '2009-05-28 00:00:00.000' AS [row_begin_date], '2009-07-20 00:00:00.000' AS [row_end_date], '31' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-06-16 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '39' AS [claim_number], '2009-03-17 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-03-17 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '39' AS [claim_number], '2009-01-28 00:00:00.000' AS [row_begin_date], '2009-02-15 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-01-28 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '39' AS [claim_number], '2009-02-16 00:00:00.000' AS [row_begin_date], '2009-03-16 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-01-28 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '40' AS [claim_number], '2009-02-05 00:00:00.000' AS [row_begin_date], '2009-02-21 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-02-05 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '40' AS [claim_number], '2010-02-02 00:00:00.000' AS [row_begin_date], '2010-02-10 09:17:36.440' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2010-02-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '40' AS [claim_number], '2010-02-10 09:17:37.440' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2010-01-23 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '40' AS [claim_number], '2009-02-22 00:00:00.000' AS [row_begin_date], '2010-02-01 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-02-22 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '41' AS [claim_number], '2009-04-25 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-04-25 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '41' AS [claim_number], '2009-03-25 00:00:00.000' AS [row_begin_date], '2009-04-01 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-03-25 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '41' AS [claim_number], '2009-04-02 00:00:00.000' AS [row_begin_date], '2009-04-24 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-03-25 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '42' AS [claim_number], '2009-04-24 00:00:00.000' AS [row_begin_date], '2009-04-30 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-04-24 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '42' AS [claim_number], '2009-04-20 00:00:00.000' AS [row_begin_date], '2009-04-23 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-04-20 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '42' AS [claim_number], '2009-05-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-05-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '43' AS [claim_number], '2009-07-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '43' AS [claim_number], '2009-05-21 00:00:00.000' AS [row_begin_date], '2009-05-25 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-05-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '43' AS [claim_number], '2009-05-26 00:00:00.000' AS [row_begin_date], '2009-06-30 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-05-26 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '44' AS [claim_number], '2009-06-20 00:00:00.000' AS [row_begin_date], '2009-06-22 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-06-20 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '44' AS [claim_number], '2009-06-23 00:00:00.000' AS [row_begin_date], '2009-07-29 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-06-23 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '44' AS [claim_number], '2009-07-30 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-07-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '45' AS [claim_number], '2009-07-14 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '24' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-07-14 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '46' AS [claim_number], '2009-07-15 00:00:00.000' AS [row_begin_date], '2009-08-12 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-07-15 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '46' AS [claim_number], '2009-08-13 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-08-13 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '47' AS [claim_number], '2009-09-08 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-09-08 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '47' AS [claim_number], '2009-08-10 00:00:00.000' AS [row_begin_date], '2009-09-07 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-08-10 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '48' AS [claim_number], '2009-08-18 00:00:00.000' AS [row_begin_date], '2009-08-27 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-08-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '48' AS [claim_number], '2009-08-28 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '22' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-08-28 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '49' AS [claim_number], '2009-09-08 00:00:00.000' AS [row_begin_date], '2009-09-24 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-09-08 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '49' AS [claim_number], '2009-09-25 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-09-25 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '50' AS [claim_number], '2009-11-02 00:00:00.000' AS [row_begin_date], '2010-01-05 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-11-02 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '50' AS [claim_number], '2009-10-08 00:00:00.000' AS [row_begin_date], '2009-11-01 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-10-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '50' AS [claim_number], '2009-10-07 00:00:00.000' AS [row_begin_date], '2009-10-07 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-10-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '50' AS [claim_number], '2010-01-06 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-11-22 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '51' AS [claim_number], '2009-10-29 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '13' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-10-29 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '52' AS [claim_number], '2009-12-21 00:00:00.000' AS [row_begin_date], '2009-12-22 00:00:00.000' AS [row_end_date], '17' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-12-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '52' AS [claim_number], '2009-11-09 00:00:00.000' AS [row_begin_date], '2009-12-20 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-11-09 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '52' AS [claim_number], '2009-12-23 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-12-23 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '53' AS [claim_number], '2009-11-25 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '13' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-11-25 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '53' AS [claim_number], '2009-11-18 00:00:00.000' AS [row_begin_date], '2009-11-24 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-11-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '54' AS [claim_number], '2009-12-28 00:00:00.000' AS [row_begin_date], '2010-01-19 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-12-28 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '54' AS [claim_number], '2010-01-20 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2010-01-20 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '55' AS [claim_number], '2009-12-30 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-12-30 00:00:00.000' AS [claim_status_date]

Here is the revised version of the query in my original post:

declare @report_begin_date as datetime



declare @report_end_date as datetime

set @report_begin_date = '2009-01-01'

set @report_end_date = '2009-12-31'

if object_id('tempdb..#cal') is not null drop table #cal

if object_id('tempdb..#claim') is not null drop table #claim

--Create a temporary calendar table for only those dates requested for the report

select calendar.calendar_date

into #cal

from edw.dbo.calendar

where calendar.calendar_date between dateadd(mm,-1,@report_begin_date) and @report_end_date

  and calendar.month_end_indicator = 'Y';

-- Create a claim table with a row for each claim and month in the reporting period

select row_number() over(partition by claim_statuses.claim_number order by claim_statuses.claim_number

                                                                          ,year(#cal.calendar_date)

                                                                          ,month(#cal.calendar_date)) as row_id

      ,#cal.calendar_date

      ,claim_statuses.claim_number

      ,claim_statuses.claim_status_group_text

into #claim

from #cal

     inner join dbo.claim_statuses on #cal.calendar_date between claim_statuses.row_begin_date and claim_statuses.row_end_date

select year(curr.calendar_date) as year_number

      ,month(curr.calendar_date) as month_number

      ,count((case

                 when  pr.claim_status_group_text in ('Open','Pending') then pr.claim_number

              end)) as beginning_balance

      ,count((case

                 when curr.claim_status_group_text is not null and pr.claim_status_group_text is null  then curr.claim_number

              end))  as added_count

      ,count((case

                 when curr.claim_status_group_text <>'Closed' and pr.claim_status_group_text ='Closed' then curr.claim_number

              end))  as reopen_count

      ,count((case

                 when curr.claim_status_group_text = 'Closed' and pr.claim_status_group_text <> 'Closed'then curr.claim_number

                 when curr.claim_status_group_text='Closed' and pr.claim_status_group_text is null  then curr.claim_number

              end)) as closed_count

      ,count((case

                 when  curr.claim_status_group_text in ('Open','Pending') then curr.claim_number

              end)) as ending_balance            

from #claim as curr

     left outer join #claim as pr on curr.claim_number = pr.claim_number

                                 and curr.row_id = pr.row_id + 1

where curr.calendar_date >= @report_begin_date

group by year(curr.calendar_date)

,month(curr.calendar_date)

LutzM SSC Guru Points: 107049 More actions · Answer 4

Ok, now we have 214 SELECT statements. I'm guessing we could wrap those into a statement like



Select * into claim_statuses from (...)a

We still don't have definition and sample data for your calendar table. Please provide DDL and INSERT script ready to use.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

dataczar SSC Enthusiast Points: 182 More actions · Answer 5

I apologize. I pasted the SQL in a few times and overwrote the calendar part.

-- Create the calendar table in your development database



CREATE TABLE [dbo].[CALENDAR]

(

	[calendar_date] [smalldatetime] NOT NULL,

	[weekday_flag] [bit] NULL,

	[holiday_flag] [bit] NULL,

	[year_number] [smallint] NULL,

	[quarter_number] [tinyint] NULL,

	[month_number] [tinyint] NULL,

	[day_number] [tinyint] NULL,

	[day_of_week_number] [tinyint] NULL,

	[month_name] [varchar](9) NULL,

	[day_name] [varchar](9) NULL,

	[week_number] [tinyint] NULL,

	[month_end_indicator] [char](1) NULL,

	[year_end_indicator] [char](1) NULL,

 CONSTRAINT [PK_CALENDAR] PRIMARY KEY CLUSTERED

(

	[calendar_date] ASC

)

)

GO

-- Insert rows into the calendar table

INSERT INTO dbo.CALENDAR

(calendar_date, weekday_flag, holiday_flag, year_number, quarter_number, month_number, day_number, day_of_week_number, month_name, day_name, week_number, month_end_indicator, year_end_indicator)

SELECT '2008-12-31 00:00:00' AS [calendar_date], '1' AS [weekday_flag], '0' AS [holiday_flag], '2008' AS [year_number], '4' AS [quarter_number], '12' AS [month_number], '31' AS [day_number], '4' AS [day_of_week_number], 'December' AS [month_name], 'Wednesday' AS [day_name], '53' AS [week_number], 'Y' AS [month_end_indicator], 'Y' AS [year_end_indicator] UNION ALL

SELECT '2009-01-31 00:00:00' AS [calendar_date], '0' AS [weekday_flag], '0' AS [holiday_flag], '2009' AS [year_number], '1' AS [quarter_number], '1' AS [month_number], '31' AS [day_number], '7' AS [day_of_week_number], 'January' AS [month_name], 'Saturday' AS [day_name], '4' AS [week_number], 'Y' AS [month_end_indicator], NULL AS [year_end_indicator] UNION ALL

SELECT '2009-02-28 00:00:00' AS [calendar_date], '0' AS [weekday_flag], '0' AS [holiday_flag], '2009' AS [year_number], '1' AS [quarter_number], '2' AS [month_number], '28' AS [day_number], '7' AS [day_of_week_number], 'February' AS [month_name], 'Saturday' AS [day_name], '8' AS [week_number], 'Y' AS [month_end_indicator], NULL AS [year_end_indicator] UNION ALL

SELECT '2009-03-31 00:00:00' AS [calendar_date], '1' AS [weekday_flag], '0' AS [holiday_flag], '2009' AS [year_number], '1' AS [quarter_number], '3' AS [month_number], '31' AS [day_number], '3' AS [day_of_week_number], 'March' AS [month_name], 'Tuesday' AS [day_name], '13' AS [week_number], 'Y' AS [month_end_indicator], NULL AS [year_end_indicator] UNION ALL

SELECT '2009-04-30 00:00:00' AS [calendar_date], '1' AS [weekday_flag], '0' AS [holiday_flag], '2009' AS [year_number], '2' AS [quarter_number], '4' AS [month_number], '30' AS [day_number], '5' AS [day_of_week_number], 'April' AS [month_name], 'Thursday' AS [day_name], '17' AS [week_number], 'Y' AS [month_end_indicator], NULL AS [year_end_indicator] UNION ALL

SELECT '2009-05-31 00:00:00' AS [calendar_date], '0' AS [weekday_flag], '0' AS [holiday_flag], '2009' AS [year_number], '2' AS [quarter_number], '5' AS [month_number], '31' AS [day_number], '1' AS [day_of_week_number], 'May' AS [month_name], 'Sunday' AS [day_name], '22' AS [week_number], 'Y' AS [month_end_indicator], NULL AS [year_end_indicator] UNION ALL

SELECT '2009-06-30 00:00:00' AS [calendar_date], '1' AS [weekday_flag], '0' AS [holiday_flag], '2009' AS [year_number], '2' AS [quarter_number], '6' AS [month_number], '30' AS [day_number], '3' AS [day_of_week_number], 'June' AS [month_name], 'Tuesday' AS [day_name], '26' AS [week_number], 'Y' AS [month_end_indicator], NULL AS [year_end_indicator] UNION ALL

SELECT '2009-07-31 00:00:00' AS [calendar_date], '1' AS [weekday_flag], '0' AS [holiday_flag], '2009' AS [year_number], '3' AS [quarter_number], '7' AS [month_number], '31' AS [day_number], '6' AS [day_of_week_number], 'July' AS [month_name], 'Friday' AS [day_name], '30' AS [week_number], 'Y' AS [month_end_indicator], NULL AS [year_end_indicator] UNION ALL

SELECT '2009-08-31 00:00:00' AS [calendar_date], '1' AS [weekday_flag], '0' AS [holiday_flag], '2009' AS [year_number], '3' AS [quarter_number], '8' AS [month_number], '31' AS [day_number], '2' AS [day_of_week_number], 'August' AS [month_name], 'Monday' AS [day_name], '35' AS [week_number], 'Y' AS [month_end_indicator], NULL AS [year_end_indicator] UNION ALL

SELECT '2009-09-30 00:00:00' AS [calendar_date], '1' AS [weekday_flag], '0' AS [holiday_flag], '2009' AS [year_number], '3' AS [quarter_number], '9' AS [month_number], '30' AS [day_number], '4' AS [day_of_week_number], 'September' AS [month_name], 'Wednesday' AS [day_name], '39' AS [week_number], 'Y' AS [month_end_indicator], NULL AS [year_end_indicator] UNION ALL

SELECT '2009-10-31 00:00:00' AS [calendar_date], '0' AS [weekday_flag], '0' AS [holiday_flag], '2009' AS [year_number], '4' AS [quarter_number], '10' AS [month_number], '31' AS [day_number], '7' AS [day_of_week_number], 'October' AS [month_name], 'Saturday' AS [day_name], '43' AS [week_number], 'Y' AS [month_end_indicator], NULL AS [year_end_indicator] UNION ALL

SELECT '2009-11-30 00:00:00' AS [calendar_date], '1' AS [weekday_flag], '0' AS [holiday_flag], '2009' AS [year_number], '4' AS [quarter_number], '11' AS [month_number], '30' AS [day_number], '2' AS [day_of_week_number], 'November' AS [month_name], 'Monday' AS [day_name], '48' AS [week_number], 'Y' AS [month_end_indicator], NULL AS [year_end_indicator] UNION ALL

SELECT '2009-12-31 00:00:00' AS [calendar_date], '1' AS [weekday_flag], '0' AS [holiday_flag], '2009' AS [year_number], '4' AS [quarter_number], '12' AS [month_number], '31' AS [day_number], '5' AS [day_of_week_number], 'December' AS [month_name], 'Thursday' AS [day_name], '52' AS [week_number], 'Y' AS [month_end_indicator], 'Y' AS [year_end_indicator]

-- Create the claim status table

CREATE TABLE [dbo].[CLAIM_STATUSES]

(

	[claim_number] [int] NOT NULL,

	[row_begin_date] [datetime] NOT NULL,

	[row_end_date] [datetime] NOT NULL,

	[claim_status_id] [int] NOT NULL,

	[claim_status_group_text] [varchar](50) NOT NULL,

	[claim_status_date] [datetime] NOT NULL,

 CONSTRAINT [PK_CLAIM_STATUSES] PRIMARY KEY CLUSTERED

(

	[claim_number] ASC,

	[row_begin_date] ASC

)

)

GO

-- Insert rows into the CLAIM_STATUSES table

INSERT INTO dbo.CLAIM_STATUSES

(claim_number, row_begin_date, row_end_date, claim_status_id, claim_status_group_text, claim_status_date)

SELECT '1' AS [claim_number], '2008-07-02 00:00:00.000' AS [row_begin_date], '2008-07-08 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-02 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-08-14 00:00:00.000' AS [row_begin_date], '2008-08-14 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-14 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-09-04 00:00:00.000' AS [row_begin_date], '2008-09-09 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-04 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-09-16 00:00:00.000' AS [row_begin_date], '2008-09-29 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-16 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-10-09 00:00:00.000' AS [row_begin_date], '2008-10-29 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-09 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2009-01-08 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-08 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-06-18 00:00:00.000' AS [row_begin_date], '2008-06-18 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-06-19 00:00:00.000' AS [row_begin_date], '2008-07-01 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-06-19 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-07-09 00:00:00.000' AS [row_begin_date], '2008-08-13 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-09 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-08-15 00:00:00.000' AS [row_begin_date], '2008-09-03 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-15 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-09-10 00:00:00.000' AS [row_begin_date], '2008-09-15 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-10 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-09-30 00:00:00.000' AS [row_begin_date], '2008-10-08 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '1' AS [claim_number], '2008-10-30 00:00:00.000' AS [row_begin_date], '2009-01-07 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '2' AS [claim_number], '2008-06-18 00:00:00.000' AS [row_begin_date], '2008-09-02 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '2' AS [claim_number], '2009-12-02 00:00:00.000' AS [row_begin_date], '2010-01-06 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-08-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '2' AS [claim_number], '2009-08-18 00:00:00.000' AS [row_begin_date], '2009-12-01 00:00:00.000' AS [row_end_date], '22' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-08-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '2' AS [claim_number], '2008-09-03 00:00:00.000' AS [row_begin_date], '2009-07-31 00:00:00.000' AS [row_end_date], '26' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2008-09-03 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '2' AS [claim_number], '2009-08-01 00:00:00.000' AS [row_begin_date], '2009-08-17 00:00:00.000' AS [row_end_date], '36' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-08-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '2' AS [claim_number], '2010-01-07 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2010-01-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2008-08-04 00:00:00.000' AS [row_begin_date], '2008-08-17 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-04 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2008-08-21 00:00:00.000' AS [row_begin_date], '2008-08-25 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2009-01-12 00:00:00.000' AS [row_begin_date], '2009-01-14 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-12 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2009-01-30 00:00:00.000' AS [row_begin_date], '2009-02-08 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2008-06-19 00:00:00.000' AS [row_begin_date], '2008-08-03 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-19 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2008-12-11 00:00:00.000' AS [row_begin_date], '2009-01-11 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2008-08-18 00:00:00.000' AS [row_begin_date], '2008-08-20 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2008-08-26 00:00:00.000' AS [row_begin_date], '2008-12-10 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-26 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2009-01-15 00:00:00.000' AS [row_begin_date], '2009-01-29 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-15 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '3' AS [claim_number], '2009-02-09 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-02-09 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '4' AS [claim_number], '2008-07-14 00:00:00.000' AS [row_begin_date], '2008-11-30 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-14 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '4' AS [claim_number], '2008-12-02 00:00:00.000' AS [row_begin_date], '2008-12-28 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-02 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '4' AS [claim_number], '2008-06-19 00:00:00.000' AS [row_begin_date], '2008-07-13 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-19 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '4' AS [claim_number], '2008-12-01 00:00:00.000' AS [row_begin_date], '2008-12-01 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-13 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '4' AS [claim_number], '2008-12-29 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-02 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '5' AS [claim_number], '2008-08-13 00:00:00.000' AS [row_begin_date], '2008-08-17 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-13 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '5' AS [claim_number], '2008-09-10 00:00:00.000' AS [row_begin_date], '2008-09-24 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-10 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '5' AS [claim_number], '2008-10-11 00:00:00.000' AS [row_begin_date], '2008-10-16 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '5' AS [claim_number], '2008-06-20 00:00:00.000' AS [row_begin_date], '2008-08-12 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-20 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '5' AS [claim_number], '2008-10-02 00:00:00.000' AS [row_begin_date], '2008-10-10 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-02 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '5' AS [claim_number], '2008-10-17 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-17 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '5' AS [claim_number], '2008-08-18 00:00:00.000' AS [row_begin_date], '2008-09-09 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '5' AS [claim_number], '2008-09-25 00:00:00.000' AS [row_begin_date], '2008-10-01 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-25 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '6' AS [claim_number], '2008-08-12 00:00:00.000' AS [row_begin_date], '2008-09-18 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-12 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '6' AS [claim_number], '2008-09-22 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-22 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '6' AS [claim_number], '2008-06-23 00:00:00.000' AS [row_begin_date], '2008-08-11 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-23 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '6' AS [claim_number], '2008-09-19 00:00:00.000' AS [row_begin_date], '2008-09-21 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-19 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '7' AS [claim_number], '2008-08-18 00:00:00.000' AS [row_begin_date], '2008-09-22 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '7' AS [claim_number], '2008-10-02 00:00:00.000' AS [row_begin_date], '2008-10-21 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-02 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '7' AS [claim_number], '2008-11-11 00:00:00.000' AS [row_begin_date], '2008-12-01 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-11-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '7' AS [claim_number], '2008-12-09 00:00:00.000' AS [row_begin_date], '2009-01-29 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-09 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '7' AS [claim_number], '2008-06-24 00:00:00.000' AS [row_begin_date], '2008-08-17 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-24 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '7' AS [claim_number], '2008-09-23 00:00:00.000' AS [row_begin_date], '2008-10-01 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-23 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '7' AS [claim_number], '2008-10-22 00:00:00.000' AS [row_begin_date], '2008-11-10 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-22 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '7' AS [claim_number], '2008-12-02 00:00:00.000' AS [row_begin_date], '2008-12-08 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-02 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '7' AS [claim_number], '2009-01-30 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '8' AS [claim_number], '2008-09-09 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-09 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '8' AS [claim_number], '2008-08-21 00:00:00.000' AS [row_begin_date], '2008-09-08 00:00:00.000' AS [row_end_date], '35' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '8' AS [claim_number], '2008-06-25 00:00:00.000' AS [row_begin_date], '2008-08-20 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-25 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '9' AS [claim_number], '2008-06-26 00:00:00.000' AS [row_begin_date], '2008-06-30 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-06-26 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '9' AS [claim_number], '2008-12-04 00:00:00.000' AS [row_begin_date], '2008-12-04 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-04 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '9' AS [claim_number], '2009-01-04 00:00:00.000' AS [row_begin_date], '2009-01-11 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-04 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '9' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-12-03 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '9' AS [claim_number], '2008-12-05 00:00:00.000' AS [row_begin_date], '2009-01-03 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-05 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '9' AS [claim_number], '2009-01-12 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-12 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '10' AS [claim_number], '2008-12-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '10' AS [claim_number], '2008-06-27 00:00:00.000' AS [row_begin_date], '2008-08-20 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-27 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '10' AS [claim_number], '2008-08-21 00:00:00.000' AS [row_begin_date], '2008-09-28 00:00:00.000' AS [row_end_date], '35' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '10' AS [claim_number], '2008-10-01 00:00:00.000' AS [row_begin_date], '2008-11-30 00:00:00.000' AS [row_end_date], '35' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '10' AS [claim_number], '2008-09-29 00:00:00.000' AS [row_begin_date], '2008-09-30 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-09-29 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '11' AS [claim_number], '2008-08-14 00:00:00.000' AS [row_begin_date], '2008-08-17 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-14 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '11' AS [claim_number], '2008-06-27 00:00:00.000' AS [row_begin_date], '2008-08-13 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-06-27 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '11' AS [claim_number], '2008-08-18 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '12' AS [claim_number], '2008-08-21 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '12' AS [claim_number], '2008-06-30 00:00:00.000' AS [row_begin_date], '2008-08-20 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '13' AS [claim_number], '2008-06-30 00:00:00.000' AS [row_begin_date], '2008-08-13 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '13' AS [claim_number], '2008-10-29 00:00:00.000' AS [row_begin_date], '2008-11-04 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-04 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '13' AS [claim_number], '2008-08-14 00:00:00.000' AS [row_begin_date], '2008-10-28 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-14 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '13' AS [claim_number], '2008-11-05 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-04 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '14' AS [claim_number], '2008-10-07 00:00:00.000' AS [row_begin_date], '2008-11-30 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '14' AS [claim_number], '2008-12-11 00:00:00.000' AS [row_begin_date], '2008-12-28 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '14' AS [claim_number], '2009-01-14 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-14 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '14' AS [claim_number], '2008-09-17 00:00:00.000' AS [row_begin_date], '2008-10-06 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-17 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '14' AS [claim_number], '2008-12-01 00:00:00.000' AS [row_begin_date], '2008-12-10 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '14' AS [claim_number], '2008-12-29 00:00:00.000' AS [row_begin_date], '2009-01-13 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-29 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '14' AS [claim_number], '2008-06-30 00:00:00.000' AS [row_begin_date], '2008-09-16 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2008-06-30 00:00:00.000' AS [row_begin_date], '2008-08-05 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-06-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2010-02-04 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2010-01-20 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2008-08-20 00:00:00.000' AS [row_begin_date], '2008-09-29 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-20 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2008-10-15 00:00:00.000' AS [row_begin_date], '2008-10-29 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-15 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2008-11-04 00:00:00.000' AS [row_begin_date], '2008-11-25 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-11-04 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2008-12-15 00:00:00.000' AS [row_begin_date], '2009-02-02 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-15 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2009-02-09 00:00:00.000' AS [row_begin_date], '2010-02-03 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-02-09 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2008-08-06 00:00:00.000' AS [row_begin_date], '2008-08-19 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-06 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2008-09-30 00:00:00.000' AS [row_begin_date], '2008-10-14 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2008-10-30 00:00:00.000' AS [row_begin_date], '2008-11-03 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2008-11-26 00:00:00.000' AS [row_begin_date], '2008-12-14 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-11-26 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '15' AS [claim_number], '2009-02-03 00:00:00.000' AS [row_begin_date], '2009-02-08 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-02-03 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '16' AS [claim_number], '2008-08-21 00:00:00.000' AS [row_begin_date], '2008-09-28 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '16' AS [claim_number], '2008-10-07 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '16' AS [claim_number], '2008-09-29 00:00:00.000' AS [row_begin_date], '2008-10-06 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-29 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '16' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-08-20 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '17' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-08-20 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '17' AS [claim_number], '2008-09-29 00:00:00.000' AS [row_begin_date], '2008-10-06 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-29 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '17' AS [claim_number], '2008-10-29 00:00:00.000' AS [row_begin_date], '2008-11-03 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '17' AS [claim_number], '2008-08-21 00:00:00.000' AS [row_begin_date], '2008-09-28 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '17' AS [claim_number], '2008-10-07 00:00:00.000' AS [row_begin_date], '2008-10-28 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '17' AS [claim_number], '2008-11-04 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '18' AS [claim_number], '2008-08-14 00:00:00.000' AS [row_begin_date], '2008-09-28 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-14 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '18' AS [claim_number], '2008-10-07 00:00:00.000' AS [row_begin_date], '2008-11-30 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '18' AS [claim_number], '2008-12-22 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-22 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '18' AS [claim_number], '2008-09-29 00:00:00.000' AS [row_begin_date], '2008-10-06 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-29 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '18' AS [claim_number], '2008-12-01 00:00:00.000' AS [row_begin_date], '2008-12-21 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-22 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '18' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-08-13 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2008-08-13 00:00:00.000' AS [row_begin_date], '2008-10-14 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-08-13 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-07-02 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2008-07-17 00:00:00.000' AS [row_begin_date], '2008-07-27 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-17 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2008-08-11 00:00:00.000' AS [row_begin_date], '2008-08-12 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2008-10-15 00:00:00.000' AS [row_begin_date], '2008-10-21 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-15 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2008-12-30 00:00:00.000' AS [row_begin_date], '2009-01-12 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-12-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2008-10-22 00:00:00.000' AS [row_begin_date], '2008-12-29 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-22 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2009-01-13 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-13 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2008-07-03 00:00:00.000' AS [row_begin_date], '2008-07-16 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-03 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '19' AS [claim_number], '2008-07-28 00:00:00.000' AS [row_begin_date], '2008-08-10 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-28 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '20' AS [claim_number], '2008-10-31 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-31 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '20' AS [claim_number], '2008-10-22 00:00:00.000' AS [row_begin_date], '2008-10-30 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-22 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '20' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-10-21 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '21' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-08-26 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '21' AS [claim_number], '2008-08-27 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-27 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '22' AS [claim_number], '2008-08-11 00:00:00.000' AS [row_begin_date], '2009-02-03 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '22' AS [claim_number], '2008-07-23 00:00:00.000' AS [row_begin_date], '2008-08-10 00:00:00.000' AS [row_end_date], '35' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-23 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '22' AS [claim_number], '2009-02-18 00:00:00.000' AS [row_begin_date], '2009-03-16 00:00:00.000' AS [row_end_date], '36' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-02-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '22' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-07-22 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '22' AS [claim_number], '2009-02-04 00:00:00.000' AS [row_begin_date], '2009-02-17 00:00:00.000' AS [row_end_date], '18' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-02-04 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '22' AS [claim_number], '2009-03-17 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-03-17 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '23' AS [claim_number], '2009-03-17 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-03-17 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '23' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-07-22 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '23' AS [claim_number], '2008-07-23 00:00:00.000' AS [row_begin_date], '2008-09-03 00:00:00.000' AS [row_end_date], '35' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-23 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '23' AS [claim_number], '2008-09-04 00:00:00.000' AS [row_begin_date], '2009-03-16 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-04 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '24' AS [claim_number], '2008-08-07 00:00:00.000' AS [row_begin_date], '2008-09-23 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '24' AS [claim_number], '2008-10-07 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '24' AS [claim_number], '2008-07-01 00:00:00.000' AS [row_begin_date], '2008-08-06 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '24' AS [claim_number], '2008-09-24 00:00:00.000' AS [row_begin_date], '2008-10-06 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-24 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2008-07-07 00:00:00.000' AS [row_begin_date], '2008-07-30 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2008-08-14 00:00:00.000' AS [row_begin_date], '2008-09-11 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-08-14 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2008-09-18 00:00:00.000' AS [row_begin_date], '2008-10-20 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2008-11-19 00:00:00.000' AS [row_begin_date], '2008-11-23 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-11-19 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2010-02-12 09:53:42.500' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2010-02-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2008-07-02 00:00:00.000' AS [row_begin_date], '2008-07-06 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2008-07-02 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2010-02-11 08:26:37.500' AS [row_begin_date], '2010-02-12 09:53:41.500' AS [row_end_date], '14' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2010-02-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2008-07-31 00:00:00.000' AS [row_begin_date], '2008-08-13 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-07-31 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2008-09-12 00:00:00.000' AS [row_begin_date], '2008-09-17 00:00:00.000' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-09-12 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2008-11-24 00:00:00.000' AS [row_begin_date], '2010-02-11 08:26:36.500' AS [row_end_date], '7' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-11-24 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '25' AS [claim_number], '2008-10-21 00:00:00.000' AS [row_begin_date], '2008-11-18 00:00:00.000' AS [row_end_date], '8' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2008-10-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '26' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2002-09-06 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '27' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2003-04-28 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '28' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '1996-11-22 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '29' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2002-03-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '30' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2005-04-27 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '31' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2005-02-28 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '32' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2002-07-11 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '33' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2001-07-13 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '34' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2003-12-15 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '35' AS [claim_number], '1900-01-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2000-02-14 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '36' AS [claim_number], '2009-01-26 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-01-26 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '36' AS [claim_number], '2009-01-07 00:00:00.000' AS [row_begin_date], '2009-01-19 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-01-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '36' AS [claim_number], '2009-01-20 00:00:00.000' AS [row_begin_date], '2009-01-25 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-01-20 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '37' AS [claim_number], '2009-01-31 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-02-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '37' AS [claim_number], '2009-01-07 00:00:00.000' AS [row_begin_date], '2009-01-30 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-01-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '38' AS [claim_number], '2009-01-08 00:00:00.000' AS [row_begin_date], '2009-03-02 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-01-08 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '38' AS [claim_number], '2009-07-21 00:00:00.000' AS [row_begin_date], '2009-10-01 00:00:00.000' AS [row_end_date], '19' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-06-16 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '38' AS [claim_number], '2009-10-02 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '19' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-07-09 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '38' AS [claim_number], '2009-03-03 00:00:00.000' AS [row_begin_date], '2009-05-27 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-03-03 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '38' AS [claim_number], '2009-05-28 00:00:00.000' AS [row_begin_date], '2009-07-20 00:00:00.000' AS [row_end_date], '31' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-06-16 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '39' AS [claim_number], '2009-03-17 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-03-17 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '39' AS [claim_number], '2009-01-28 00:00:00.000' AS [row_begin_date], '2009-02-15 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-01-28 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '39' AS [claim_number], '2009-02-16 00:00:00.000' AS [row_begin_date], '2009-03-16 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-01-28 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '40' AS [claim_number], '2009-02-05 00:00:00.000' AS [row_begin_date], '2009-02-21 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-02-05 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '40' AS [claim_number], '2010-02-02 00:00:00.000' AS [row_begin_date], '2010-02-10 09:17:36.440' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2010-02-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '40' AS [claim_number], '2010-02-10 09:17:37.440' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2010-01-23 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '40' AS [claim_number], '2009-02-22 00:00:00.000' AS [row_begin_date], '2010-02-01 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-02-22 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '41' AS [claim_number], '2009-04-25 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-04-25 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '41' AS [claim_number], '2009-03-25 00:00:00.000' AS [row_begin_date], '2009-04-01 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-03-25 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '41' AS [claim_number], '2009-04-02 00:00:00.000' AS [row_begin_date], '2009-04-24 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-03-25 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '42' AS [claim_number], '2009-04-24 00:00:00.000' AS [row_begin_date], '2009-04-30 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-04-24 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '42' AS [claim_number], '2009-04-20 00:00:00.000' AS [row_begin_date], '2009-04-23 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-04-20 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '42' AS [claim_number], '2009-05-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-05-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '43' AS [claim_number], '2009-07-01 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-07-01 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '43' AS [claim_number], '2009-05-21 00:00:00.000' AS [row_begin_date], '2009-05-25 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-05-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '43' AS [claim_number], '2009-05-26 00:00:00.000' AS [row_begin_date], '2009-06-30 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-05-26 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '44' AS [claim_number], '2009-06-20 00:00:00.000' AS [row_begin_date], '2009-06-22 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-06-20 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '44' AS [claim_number], '2009-06-23 00:00:00.000' AS [row_begin_date], '2009-07-29 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-06-23 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '44' AS [claim_number], '2009-07-30 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-07-30 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '45' AS [claim_number], '2009-07-14 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '24' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-07-14 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '46' AS [claim_number], '2009-07-15 00:00:00.000' AS [row_begin_date], '2009-08-12 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-07-15 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '46' AS [claim_number], '2009-08-13 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-08-13 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '47' AS [claim_number], '2009-09-08 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-09-08 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '47' AS [claim_number], '2009-08-10 00:00:00.000' AS [row_begin_date], '2009-09-07 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-08-10 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '48' AS [claim_number], '2009-08-18 00:00:00.000' AS [row_begin_date], '2009-08-27 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-08-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '48' AS [claim_number], '2009-08-28 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '22' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-08-28 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '49' AS [claim_number], '2009-09-08 00:00:00.000' AS [row_begin_date], '2009-09-24 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-09-08 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '49' AS [claim_number], '2009-09-25 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-09-25 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '50' AS [claim_number], '2009-11-02 00:00:00.000' AS [row_begin_date], '2010-01-05 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-11-02 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '50' AS [claim_number], '2009-10-08 00:00:00.000' AS [row_begin_date], '2009-11-01 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-10-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '50' AS [claim_number], '2009-10-07 00:00:00.000' AS [row_begin_date], '2009-10-07 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-10-07 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '50' AS [claim_number], '2010-01-06 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '25' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-11-22 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '51' AS [claim_number], '2009-10-29 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '13' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-10-29 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '52' AS [claim_number], '2009-12-21 00:00:00.000' AS [row_begin_date], '2009-12-22 00:00:00.000' AS [row_end_date], '17' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-12-21 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '52' AS [claim_number], '2009-11-09 00:00:00.000' AS [row_begin_date], '2009-12-20 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-11-09 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '52' AS [claim_number], '2009-12-23 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2009-12-23 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '53' AS [claim_number], '2009-11-25 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '13' AS [claim_status_id], 'Closed' AS [claim_status_group_text], '2009-11-25 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '53' AS [claim_number], '2009-11-18 00:00:00.000' AS [row_begin_date], '2009-11-24 00:00:00.000' AS [row_end_date], '1' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-11-18 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '54' AS [claim_number], '2009-12-28 00:00:00.000' AS [row_begin_date], '2010-01-19 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-12-28 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '54' AS [claim_number], '2010-01-20 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '3' AS [claim_status_id], 'Open' AS [claim_status_group_text], '2010-01-20 00:00:00.000' AS [claim_status_date] UNION ALL

SELECT '55' AS [claim_number], '2009-12-30 00:00:00.000' AS [row_begin_date], '9999-12-31 00:00:00.000' AS [row_end_date], '2' AS [claim_status_id], 'Pending' AS [claim_status_group_text], '2009-12-30 00:00:00.000' AS [claim_status_date]

dataczar SSC Enthusiast Points: 182 More actions · Answer 6

dataczar

SSC Enthusiast

Points: 182

February 18, 2010 at 1:10 pm

#1121286

I wanted to follow up and see if anyone had further suggestions on this issue.

Thank you.

LutzM SSC Guru Points: 107049 More actions · Answer 7

I thought you were all set based on Jeffs recommendation so I didn't continue to follow that thread... Sorry about that.

Regarding a requirement from one of your previous posts:

dataczar (2/16/2010)

...

Claims 26-35 are all examples of claims that should be exclude during the creation of the #claims temp table because they were closed before the reporting period and remained closed during the reporting period. Claims 45,51,55 should be counted as both new and closed in the month they came in.

...

For claims 26-35 it's pretty clear how to handle it.

But why should claims 45,51,55 be counted as both new and closed?

If a claim is marked to be closed it's suppoesd to be opened before, isn't it? Why is that "open" row missing? (e.g. could it be possible that the claim has been opened before the start of the reporting period?)

Why should claim 55 be marked as closed, even if it's still "pending"?

What's the business reason for the special treatment of those claims? Is it because there is just one entry? What would happen if one of those rows would have row_begin and row_end of '2009-01-01 00:00:00.000'?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

dataczar SSC Enthusiast Points: 182 More actions · Answer 8

You are correct on 55, i misspoke about it being considered closed.

We are looking at the status at the end of the month and comparing it to the prior month, so for the claims 45 and 51 it would have a prior status of NULL and a current status of closed. It fits the definition of both new and closed (since it closed in the same month that it came in). It does have a row that shows open sometime during that month, but we are only looking at the snapshot at the end of the month.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

dataczar (2/16/2010)

Jeff,

I think you are thinking of Peso's Subscription speed challenge query, which I have looked at but this has a few more complexities because you need to calculate new, reopened, and closed in addition to the beginning and ending balances.

I am happy to have you prove me wrong. 🙂

Actually, I was thinking of the very old method of "Divide'n'Conquer" which, when associated with certain aggregations, Peso calls "pre-aggregation". It just happened to come out in the speed challenge but it's not a new technique... I was just thanking Peso (again) for the descriptive and more accurate term.

I'm sorry I lost track of this thread (I get 300-400 emails a day)... I'll try to pick up on it over the weekend.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

dc,

Account 2 does a pending, closed, pending, closed, pending, open.

How should that be considered, please?

Also, can I assume the "pending" means the same as "open" for this problem?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

dataczar SSC Enthusiast Points: 182 More actions · Answer 11

Jeff,

I would expect to see it as a reopen in Dec 2009. It would not ever appear in any counts before that date because it was always closed to closed when comparing against the prior month and was closed at the start of the reporting period.

Pending will equal Open for this.

Below are the details rows from the #claim temporary table

row_id calendar_date claim_number claim_status_group_text



-------------------- ----------------------- ------------ --------------------------------------------------

1                    2008-12-31 00:00:00     2            Closed

2                    2009-01-31 00:00:00     2            Closed

3                    2009-02-28 00:00:00     2            Closed

4                    2009-03-31 00:00:00     2            Closed

5                    2009-04-30 00:00:00     2            Closed

6                    2009-05-31 00:00:00     2            Closed

7                    2009-06-30 00:00:00     2            Closed

8                    2009-07-31 00:00:00     2            Closed

9                    2009-08-31 00:00:00     2            Closed

10                   2009-09-30 00:00:00     2            Closed

11                   2009-10-31 00:00:00     2            Closed

12                   2009-11-30 00:00:00     2            Closed

13 2009-12-31 00:00:00 2 Pending

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

dataczar (2/18/2010)

It does have a row that shows open sometime during that month, but we are only looking at the snapshot at the end of the month.

I guess I wouldn't do it the way you're doing it. You're starting out by isolating a year... I think you need to calculate everything using all of the table and then isolate the year.

To be a bit more clear... I think you need to determine each "date opened" and "date closed" pair. If you found all of the "date opened" values and searched for the MIN "date closed" that was greater than the "date opened" in a correlated subquery, that you'd be able to very quickly isolate the rows. Obviously if there isn't a date closed, it will have the "end of time" date of 9999-12-31. This should be a whole lot faster than cross joining with a date table because it will inherently not include most of the rows from the table.

Then, use Peso's code from the Speed Phreak challenge. Don't forget that his solution resolved the whole problem on a million rows in only 300 milliseconds. His entire solution is as fast as the cross join that you used for just one year.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

dataczar SSC Enthusiast Points: 182 More actions · Answer 13

Jeff,

Here is my attempt at your suggestion. I left the WHERE commented because otherwise I lose claim numbers 45 and 51, whose first status was closed. I am not sure if the end result should flatten the rows if there are multiple open rows. Also, I am not sure how I will identify a reopen (claim number 2 is the example). Would I do that because there would be more than one pair of open/closed.

select claim_number



      ,(case

           when claim_status_group_text = 'Pending' then 'Open'

           else claim_status_group_text

        end) as claim_status_group_text

      ,row_begin_date as open_date

      ,(select min(row_begin_date)

        from dbo.claim_statuses as cs

        where cs.claim_status_group_text = 'Closed'

          and claim_statuses.claim_number = cs.claim_number

          and claim_statuses.row_begin_date <= cs.row_begin_date) as closed_date

from dbo.claim_statuses

--where claim_statuses.claim_status_group_text in ('Open','Pending')

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

My bad... I didn't explain well enough. You need to find the OPEN for each CLOSED in order to pick up on the "reopens".

So far as 45 and 51 go, you said that somewhere in time in the whole table, there was an OPEN. I also said that you need to run any code against the whole table. I don't know if you did that or not but it seems to me that the OPEN's for those two rows should have been picked up by your code.

Here... try this to build the "work" table to run your code from. It makes the assumption that standalone "CLOSED" had an open somewhere before it. I didn't do anything with Client Numbers that have multiple entries that started out with "CLOSED"... that should be somewhere in the data if you run this against the whole table... and I really believe that because of the poor condition of the data, you really need to run this against the whole table. I could do this for you with a "quirky update" that would run in scant seconds against a million rows, but we'll try this first...

;



WITH

ctePrepareData AS

( --=== Change "Pending" to "Open" and add any missing "Open" rows

 SELECT Claim_Number,

        Row_Begin_Date,

        CASE WHEN Claim_Status_Group_Text = 'Pending' THEN 'Open' ELSE Claim_Status_Group_Text END AS Claim_Status_Group_Text

   FROM dbo.Claim_Statuses

  UNION ALL

 SELECT src.Claim_Number,

        CAST(0 AS DATETIME) AS Row_Begin_Date,

        'Open' AS Claim_Status_Group_Text

   FROM dbo.Claim_Statuses src

  INNER JOIN

        (--==== Find all single entry claim numbers

         SELECT Claim_Number

           FROM dbo.Claim_Statuses

          GROUP BY CLaim_Number

         HAVING COUNT(*) = 1

        ) single

     ON src.Claim_Number = single.Claim_Number

    AND src.Claim_Status_Group_Text = 'Closed'

)

,

cteGroupData AS

( --=== Create a special grouping column so we can later find the first instance of each group of statuses for each claim by date

 SELECT ROW_NUMBER() OVER (ORDER BY Claim_Number, Row_Begin_Date, Claim_Status_Group_Text DESC)

      - ROW_NUMBER() OVER (PARTITION BY Claim_Number, Claim_Status_Group_Text ORDER BY Claim_Number, Claim_Status_Group_Text) AS MyGroup,

        Claim_Number,

        Row_Begin_Date,

        Claim_Status_Group_Text

   FROM ctePrepareData

)

,

cteNumberGroups AS

(

 SELECT ROW_NUMBER() OVER (PARTITION BY MyGroup ORDER BY Claim_Number, Row_Begin_Date, Claim_Status_Group_Text DESC) AS StatusCount,

        MyGroup,

        Claim_Number,

        Row_Begin_Date,

        Claim_Status_Group_Text

   FROM cteGroupData

) --=== Now, do your work from the temp table created by the following

 SELECT ISNULL(ROW_NUMBER() OVER (ORDER BY Claim_Number, Row_Begin_Date),0) AS RowNum,

        Claim_Number,

        Row_Begin_Date,

        DATEADD(mm,DATEDIFF(mm,0,Row_Begin_Date),0) AS EventMonth,

        Claim_Status_Group_Text AS EventStatus

   INTO #Work

   FROM cteNumberGroups

  WHERE StatusCount = 1;

--===== Just displaying what's in the work table

 SELECT * FROM #Work ORDER BY RowNum;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions