First post here, but can anyone guide or help me understand the following problem.For the following table "Patient_Table", how can i find out the total number of days patient_id 22 was sick.
ID Patient_ID Listing_Number Date Status
-----------------------------------------------------------------
1 22 1 01/01/2016 Healthy
2 22 2 01/11/2016 Sick
3 34 1 01/13/2016 Healthy
4 22 3 01/20/2016 Healthy
5 22 4 01/22/2016 Sick
6 22 5 01/23/2016 Healthy
below is my logic so far, but i'm not sure of the proper syntax.
declare
@count int = 1,
@Days_sicks int = 0
while @count <= (select max (Listing_number) from Patient_Table where Patient_id = '22')
begin
case
when (select status from Patient_Table where Patient_id = '22' and Listing_number = @count) = 'Sick'
then @Days_sicks = @Days_sicks + datediff('dd', (select min date from Patient_Table where patient_id = 22 and listing_number > @count and status != 'Sick'), (select date from patient_table where patient_id = 22 and listing_number = @count)
else @Days_sicks
end as Days_sicks
set @Count = @Count + 1
END;
I've also tried this one but it's not working very well and i'm having issue with the group by clause
SELECT t1.patient_id,
DATEDIFF(dd,
t1.date,
(SELECT MIN(t3.date)
FROM Patient_Table t3
WHERE t3.patient_id = t1.patient_id
AND t3.date> t1.date) as Days_sicks
)
FROM Patient_Table t1
WHERE EXISTS(
SELECT 'NEXT'
FROM Patient_Table t2
WHERE t2.patient_id = t1.patient_id
AND t2.date> t1.date
AND t2.status != 'sick')
and t1.patient_id = '22'
Desired Result
Patient id Days_sicks
22 10
Use the lead() function and then aggregation:
select patient_id,
sum(datediff(day, date, coalesce(next_date, getdate())))
from (select pt.*,
lead(date) over (partition by patient_id order by date) as next_date
from patient_table pt
) pt
where status = 'Sick'
group by patient_id;
Note: If someone is currently sick, then this uses the current date for the end of the "sick" state.
Also, this will work if the patient is sick on multiple visits.