Solved

# calculate number of days between dates within a table

Posted on 2006-05-01
680 Views
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
Question by:jcpw

LVL 75

Accepted Solution

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

LVL 10

Expert Comment

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

LVL 5

Expert Comment

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

LVL 2

Expert Comment

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

LVL 5

Expert Comment

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

LVL 2

Expert Comment

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

LVL 5

Expert Comment

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

LVL 2

Expert Comment

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

LVL 5

Expert Comment

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

LVL 75

Expert Comment

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.