asked on

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.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jay Toops

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

morisce

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

ewahner

Try this:

First table ClientServiceAgg has 3 fields ClientId, DaysBetweenService, NumberOfVisits
Next is your table with your data.

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.

morisce

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.

ewahner

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.

morisce

>> 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.

ewahner

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?

morisce

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

Anthony Perkins

>>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