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: 1004318 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)

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)