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.