Very confusing problem..Please help me friends!!
-
Hi Guys
I have got a table of patients where each patient has got a unique patient number.There are different services which a patient can undertake such as resthome, dementia etc.
Here is the sample data
Service patientNo startDate EndDate
resthome 12 01/04/2003 03/05/2003
resthome 13 12/9/2004 13/10/2006
Dementia 44 12/08/2002 13/01/2004
dementia 12 05/05/2003 06/12/2006
................................. ............ ................................
Each patient has got a start date when he started undertaking that service and end date when the service finished .
Now I need to count total patients for each catagory but here is the main issue....
A patient can move from one servcice to another but cant be in two services at the same time and I need to count the most recent service which the patient is currently enrolled in..
How do I do that????
Pls help me guys as you have always done:
Thanks
-
For the most recent service which the patient is currently enrolled in the EndDate would be null ?
In this case:
select
service,
patientno,
count(*)
where enddate is null
group by
service,
patientNo
-
This is a messy piece of code- but it might do it for you.
I'm assuming the result you're looking for from the supplied values is
Dementia = 2
resthome = 1
?
btw- I changed the dateformat to mm/dd/yyyy
create
table mytable
([Service] varchar(30),
patientNo int,
startdate datetime,
enddate datetime)
insert
mytable values ('resthome',12,'4/1/2003','05/03/2003')
insert mytable values ('resthome',13,'9/12/2004','10/13/2006')
insert mytable values ('Dementia',44,'8/12/2002','1/13/2004')
insert mytable values ('Dementia',12,'05/05/2003','12/6/2006')
select
distinct count(my1.patientno),my1.service,max(my2.enddate) from mytable my1
inner join mytable my2
on my1.service = my2.service and my1.patientno = my2.patientno
where my1.enddate in (select max(enddate) from mytable group by patientno)group by my1.service
-
Hi
thanks for yor query.
I have given it a go but its taking forever to show the results. My table is a huge one with 4 million rows in it.
The answer to the above post is no , the most recent service does not have a null end date
Thanks
-
perhaps the above query might work if you improve the indexing suituation on your tables. check out the execution plan for the query and see what that throws up.
-
DECLARE
@MyTable TABLE ([Service] VARCHAR(30), PatientNo INT, StartDate DATETIME, EndDate DATETIME)
INSERT
@MyTable
SELECT
'resthome', 12, '4/1/2003', '05/03/2003' UNION ALL
SELECT
'resthome', 13, '9/12/2004', '10/13/2006' UNION ALL
SELECT
'Dementia', 44, '8/12/2002', '1/13/2004' UNION ALL
SELECT
'Dementia', 12, '05/05/2003', '12/6/2006'
;
WITH Yak ([Service], PatientNo, StartDate, EndDate, RecID)
AS
(
SELECT [Service], PatientNo, StartDate, EndDate,
ROW_NUMBER() OVER (PARTITION BY PatientNo ORDER BY EndDate DESC)
FROM @MyTable
),
Yak2 ([Service], PatientNo, StartDate, EndDate)
AS
(
SELECT [Service], PatientNo, StartDate, EndDate
FROM Yak WHERE RecID = 1
)
select
[Service], COUNT(*) as patients
FROM
yak2
group
by [Service]
order
by [Service]
N 56°04'39.16"
E 12°55'05.25" -
Hi
Thanks guys..It worked perfectly!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply