June 5, 2014 at 9:09 am
I have a group of patients with CHF that can have multiple visits over the year. There is a column that indicates multiple responses to questions during the visit (1=Yes, 2=No). I need to write a query that if they have a '1' in the answer column then a unique record with "1" should be indicated for each visitID per MedRecNum. Each MedRecNum is a unique patient ID that doesn't change. For example,
MedRecNum/VisitID/VisitDate/Answer
123/7777/03-14-14/1
123/7777/03-14-14/0
123/7777/03-14-14/0
123/8888/05-21-14/0
123/8888/05-21-14/0
456/3333/03-20-14/1
456/4444/05-14-14/0
What it should look like:
123/7777/03-14-14/1
123/8888/05-14-14/0
456/3333/03-20-14/1
456/4444/05-14-14/0
thanks so much for any help
June 5, 2014 at 9:38 am
This is untested, but it might give you an idea.
SELECT MedRecNum,
VisitID,
VisitDate,
MAX(CASE WHEN Answer <= 1 THEN Answer ELSE 0 END) AS Answer
FROM TheTable
GROUP BY MedRecNum,
VisitID,
VisitDate
June 5, 2014 at 9:49 am
It looks like this works:
SELECT MedRecNum, VisitID, VisitDate, MAX(Answer) AS MaxAns
FROM
(SELECT 123 AS MedRecNum,7777 As VisitID,'03-14-14' AS VisitDate,1 As Answer
UNION ALL
SELECT 123,7777,'03-14-14',0
UNION ALL
SELECT 123,8888,'05-21-14',0
UNION ALL
SELECT 123,8888,'05-21-14',0
UNION ALL
SELECT 456,3333,'03-20-14',1
UNION ALL
SELECT 456,4444,'05-14-14',0) x
GROUP BY MedRecNum, VisitID, VisitDate
ORDER BY MedRecNum, VisitID, VisitDate;
June 5, 2014 at 10:07 am
pietlinden (6/5/2014)
It looks like this works:
As long as you don't have a 2 on Answer column.
June 5, 2014 at 11:37 am
Yeah, answering based on flawed sample data will do that to you...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply