August 18, 2016 at 3:04 pm
tried to get started but really lack the skills.
SELECT P.DischargeDate, P.VisitID
FROM Quality M
left outer join Patients P on P.PatientID=M.PatientID
where M.MortOccurence = '1'
and P.DischargeDate>DATEADD(m,-12,CURRENT_TIMESTAMP)
order by P.servicedate
Quality table has a list of all mortalities (M.MortOccurrence = '1')
Patient table has a list of all visits by patient (p.PatientID) with discharge dates (p.dischargedate)
I'd like the query to bring back all visits in Table Patients 3 months back from the last DC date, which essentially would be the date of mortality.
So if the table Patients P consists of:
Patient NumberDischargeDate
111/30/2015
11/1/2016
13/4/2016
15/8/2016 (3 months back from this date)
212/2/2015
212/29/2015
22/24/2016
25/28/2016
27/30/2016 (3 months back from this date)
the result would be:
13/4/2016
15/8/2016
25/28/2016
27/30/2016
August 18, 2016 at 3:23 pm
Here is my solution:
declare @test-2 table(
PatientNumber int,
DischargeDate date
);
insert into @test-2
values (1,'2015-11-30'),
(1,'2016-01-01'),
(1,'2016-03-04'),
(1,'2016-05-08'),
(2,'2015-12-02'),
(2,'2015-12-19'),
(2,'2016-02-24'),
(2,'2016-05-28'),
(2,'2016-07-30');
select * from @test-2;
with basedata as (
select
PatientNumber,
max(DischargeDate) DischargeDate
from
group by
PatientNumber
)
select
bd.PatientNumber,
ca1.DischargeDate
from
basedata bd
cross apply (select DischargeDate from @test-2 t1 where bd.PatientNumber = t1.PatientNumber and t1.DischargeDate > dateadd(month,-3,bd.DischargeDate)) ca1;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply