• Status: Solved
• Priority: Medium
• Security: Public
• Views: 692

# calculate number of days between dates within a table

I'm analyzing health data. I have a table which contains patient numbers and service dates. A service date is a date a patient saw a doctor. Any given patient can have 1 to many service dates.

What I want to do is determine the average number of days between service dates. So, for example, if patient 1 has 2 service dates: 3/1/06 and 3/6/06, I want to select out a result that looks something like "patient 1,  5". If patient 2 has 3 service dates: 4/1/06, 4/20/06, and 5/2/06, then the result will be in 2 records - "patient 2, 19; patient 2, 12". Then once I have this information, I can easily determine the average number of days between service dates.

I think I need to do some kind of self-join, but I haven't had any luck so far.
0
jcpw
• 4
• 3
• 2
• +1
1 Solution

Commented:
Unfortunately, you are going to have to resort to using a CURSOR and a temporary table or varaible of type table to do that.
0

Commented:
The easiest way to maintin this data is to setup annother table that u update
upon inserting into the "service dates" table.
then join against that for the average

Jay
0

Commented:
Assume your table is Tab with the fields ClientId and ServiceDate

select T1.ClienId, datediff(day,T2.ServiceDate,T3.ServieDate)
from Tab T1, Tab T2
where T2.ClientId = T2.ClientId and T2.ServiceDate = (select max(T3.ServiceDate) from Tab T3 where T3.ClientId = T1.ClientId and T3.ServiceDate < T1.ServiceDate)

Now you can perhaps extract the average with :
select T1.ClienId, avg(datediff(day,T1.ServiceDate,T2.ServieDate))
from Tab T1, Tab T2
where T2.ClientId = T2.ClientId and T2.ServiceDate = (select max(T3.ServiceDate) from Tab T3 where T3.ClientId = T1.ClientId and T3.ServiceDate < T1.ServiceDate)
group by T1.ClienId
0

Commented:
Try this:

First table ClientServiceAgg has 3 fields ClientId, DaysBetweenService, NumberOfVisits

insert into dbo.ClientServiceAgg( ClientId ) select distinct ClientId from your_table

declare curs cursor fast_forward for select ClientId, Servicedate order by ClientId, ServiceDate

declare @lastClient int
declare @lastServiceDate datetime
declare @ClientId int
declare @ServiceDate datetime
open curs

fetch next from curs into @ClientId, @ServiceDate
while @@fetch_status = 0
begin
if @ClientId <> @lastClient
begin
set @lastClient = @ClientId
set @lastServiceDate = @ServiceDate
end
else
begin
update dbo.ClientServiceAgg
set DaysBetweenService = IsNull(DaysBetweenService,0) + datediff(d,@ServiceDate,@lastServiceDate),
NumberOfVisits = IsNull(NumberOfVisits,0) + 1
where ClientId = @ClientId
end
fetch next from curs into @ClientId, @ServiceDate
end

close curs
deallocate curs

select ClientId, DaysBetweenService / NumberOfVists as AvgDaysBetweenVisits
from dbo.ClientServiceAgg

Now I would suggest that this be a day one population and then you follow this up with a trigger on your client table to update this ClientServiceAgg table.

0

Commented:
The first best of the best of parctices of db programming : avoid to use cursor when it's possible.
Try all ways before using cursors.
0

Commented:
I whole heartedly agree, when possible.  I don't believe its possible given the current problem.  Your method assumes only two visits by each client.

Oh yeah BTW need to put

set @lastServiceDate = @ServiceDate

after the update.
0

Commented:
>> Your method assumes only two visits by each client.
I don't agree. I'm using 3 instances of the same table to treat perfectly this case.
0

Commented:
Okay, but what if there are 26 visits, will you use 26 aliases to that table?  At what point would you conceed that a cursor is better?
0

Commented:
the query that i posted treat all cases even if there are 26000 visits !
let jcpw try it and decide
0

Commented:
>>the query that i posted treat all cases even if there are 26000 visits !<<
I susggest you re-read the question.  You are missing the point.

>>let jcpw try it and decide<<
I suspect jcpw is MIA
0

## Featured Post

• 4
• 3
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.