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

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
Asked:
jcpw
  • 4
  • 3
  • 2
  • +1
1 Solution
 
Anthony PerkinsCommented:
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
 
Jay ToopsCommented:
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
 
morisceCommented:
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

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

0
 
morisceCommented:
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
 
ewahnerCommented:
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
 
morisceCommented:
>> 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
 
ewahnerCommented:
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
 
morisceCommented:
the query that i posted treat all cases even if there are 26000 visits !
let jcpw try it and decide
0
 
Anthony PerkinsCommented:
>>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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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