Moving Records

Last Post 08 Oct 2008 01:24 PM by c9jad. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
c9jad
New Member
New Member

--
06 Oct 2008 02:02 PM
am trying to set the claim_id equal to the original if the previous rows end_date < 180 days from the current rows begin_date

Here is a SQL statement with some sample data:

declare @test table
(
customer_id int,
claim_id int,
begin_date datetime,
end_date datetime
)

insert into @test
select 1,1, '2005-09-12 00:00:00.000', '2005-09-12 00:00:00.000' union all
select 1,2, '2005-10-10 00:00:00.000', '2005-10-10 00:00:00.000' union all
select 1,3, '2005-12-06 00:00:00.000', '2006-01-16 00:00:00.000' union all
select 1,4, '2007-07-02 00:00:00.000', '2007-07-23 00:00:00.000' union all
select 1,5, '2007-10-05 00:00:00.000', NULL


select *
from @test

Here is the result: (I took away the time from end_date so it would fit)

5 row(s) affected)
customer_id claim_id begin_date end_date
----------- ----------- ----------------------- -----------------------
1 1 2005-09-12 00:00:00.000 2005-09-12
1 2 2005-10-10 00:00:00.000 2005-10-10
1 3 2005-12-06 00:00:00.000 2006-01-16
1 4 2007-07-02 00:00:00.000 2007-07-23
1 5 2007-10-05 00:00:00.000 NULL

(5 row(s) affected)


I would like my result to look like this: the claim_id changed on records 2,3 and 5.

customer_id claim_id begin_date end_date
----------- ----------- ----------------------- -----------------------
1 1 2005-09-12 00:00:00.000 2005-09-12
1 12005-10-10 00:00:00.000 2005-10-10
1 12005-12-06 00:00:00.000 2006-01-16
1 4 2007-07-02 00:00:00.000 2007-07-23
1 42007-10-05 00:00:00.000 NULL
c9jad
New Member
New Member

--
06 Oct 2008 05:48 PM
In my query I am using a row_number to order the records by begin_date. As I read through the records, if the current begin_date is 180 days or less than the previous end_date then I want to consider the row to be the same claim as the previous row.

As in my example result set, claim_id 2 and 3 would become claim_id 1 and claim_id 5 would become claim_id 4.
c9jad
New Member
New Member

--
07 Oct 2008 06:21 AM
Sorry, here is the query that I am using:

declare @test table
(
customer_id int,
claim_id int,
begin_date datetime,
end_date datetime
)

insert into @test
select 1,1, '2007-11-05 00:00:00.000', '2008-03-31 00:00:00.000' union all
select 1,2, '2008-04-30 00:00:00.000', NULL


;with cte
as
(
select customer_id
,claim_id
,begin_date
,end_date
,datediff(day, 0, begin_date) / 180 grp
from @test
)

select c.customer_id
,d.claim_id
,c.begin_date
,c.end_date
from (
select customer_id
,grp
,min(claim_id) claim_id
from cte
group by customer_id
,grp
) d
join cte c
on c.customer_id = d.customer_id
and c.grp = d.grp

This works on some of the data, but the formula for calculating the grp does not always work, such as in this example. It does work for my original example.

How would I accomplish this with a cursor?

c9jad
New Member
New Member

--
08 Oct 2008 01:24 PM
Thank you for your help with this.

I tested it with adding another customer to the data set, but I did not get the expected result. I think because I need to add something that limits looking at the claims within a particular customer.

insert into @test
select 1,1, '2005-09-12 00:00:00.000', '2005-09-12 00:00:00.000' union all
select 1,2, '2005-10-10 00:00:00.000', '2005-10-10 00:00:00.000' union all
select 1,3, '2005-12-06 00:00:00.000', '2006-01-16 00:00:00.000' union all
select 1,4, '2007-07-02 00:00:00.000', '2007-07-23 00:00:00.000' union all
select 1,5, '2007-10-05 00:00:00.000', NULL union all
select 2,7, '2005-01-12 00:00:00.000', '2005-01-12' union all
select 2,8, '2005-06-12 00:00:00.000', '2005-06-12' union all
select 2,9, '2005-11-12 00:00:00.000', '2005-11-12' union all
select 2,10, '2006-04-12 00:00:00.000', '2006-04-12'


original data:
customer_id claim_id begin_date end_date
----------- ----------- ----------------------- -----------------------
1 1 2005-09-12 00:00:00.000 2005-09-12 00:00:00.000
1 2 2005-10-10 00:00:00.000 2005-10-10 00:00:00.000
1 3 2005-12-06 00:00:00.000 2006-01-16 00:00:00.000
1 4 2007-07-02 00:00:00.000 2007-07-23 00:00:00.000
1 5 2007-10-05 00:00:00.000 NULL
2 7 2005-01-12 00:00:00.000 2005-01-12 00:00:00.000
2 8 2005-06-12 00:00:00.000 2005-06-12 00:00:00.000
2 9 2005-11-12 00:00:00.000 2005-11-12 00:00:00.000
2 10 2006-04-12 00:00:00.000 2006-04-12 00:00:00.000

expected result
customer_id claim_id begin_date end_date
----------- ----------- ----------------------- -----------------------
1 1 2005-09-12 00:00:00.000 2005-09-12 00:00:00.000
1 1 2005-10-10 00:00:00.000 2005-10-10 00:00:00.000
1 1 2005-12-06 00:00:00.000 2006-01-16 00:00:00.000
1 4 2007-07-02 00:00:00.000 2007-07-23 00:00:00.000
1 4 2007-10-05 00:00:00.000 NULL
2 1 2005-01-12 00:00:00.000 2005-01-12 00:00:00.000
2 1 2005-06-12 00:00:00.000 2005-06-12 00:00:00.000
2 1 2005-11-12 00:00:00.000 2005-11-12 00:00:00.000
2 1 2006-04-12 00:00:00.000 2006-04-12 00:00:00.000
You are not authorized to post a reply.

Acceptable Use Policy