Jeff,
I have been reviewing your solution and have been struggling with bringing all the rows of the table in. I ran your solution just as it is against my full data set and it took 13 seconds and brought back 161,995 rows. I know it would bring back a lot less when aggregated. I also struggled with how to aggregate things into the months using your end result and determine the reopens.
Here is the code (modified from your code) that I think should be used to eliminate single rows where the status is closed and is before the report date. This cuts the number of rows in half in my production data. I unfortunately will have records that are closed as an initial status. This happens when it is open and closed all in the same day (our data warehouse run is daily).
declare @report_begin_date as datetime
declare @report_end_date as datetime
set @report_begin_date = '20090101'
set @report_end_date = '20091231';
select *
from dbo.claim_statuses
where claim_number not in (select src.claim_number
from edw.dbo.claim_statuses as src
inner join (select claim_number
from edw.dbo.claim_statuses
group by claim_number
having count(*) = 1
) as single on src.claim_number = single.claim_number
and src.claim_status_group_text = 'Closed'
and src.row_begin_date < @report_begin_date
)
Thank you,
dczar