Hello~
I have the following 2 queries that I need to combine into one for reporting purposes. For ex. I want the title to have weight, waist, Waist-to-hip ratio, body fat and blood pressure but currently they use different qfldname's. The value numbers are also different based on the qfldnames. I'm rusty with case statements!
QUERY 1
SELECT Case rf.SeqNo,
WHEN 1 THEN 'Weight'
WHEN 9 THEN 'Waist'
WHEN 10 THEN 'Waist-to-hip ratio'
WHEN 11 THEN 'Body fat'
END AS Title,
COUNT(*) AS Total,
COUNT(CASE rf.Value WHEN '1' THEN 1 ELSE NULL END) AS Healthy, COUNT(CASE rf.Value WHEN '2' THEN 1 ELSE NULL END) AS Average,
COUNT(CASE rf.Value WHEN '3' THEN 1 ELSE NULL END) AS AtRisk, COUNT(CASE rf.Value WHEN '4' THEN 1 ELSE NULL END) AS Incomplete
FROM tbOSetItem AS osi INNER JOIN
vuRspField AS rf ON rf.RspId = osi.ObjId
WHERE (osi.OSetId = 13598) AND (rf.QFldName = 'Lifestyle')--and rf.seqno in (1,9,10,11)
GROUP BY rf.SeqNo
ORDER BY rf.SeqNo
QUERY 2
SELECT CASE rf.SeqNo WHEN 1 THEN 'Blood pressure' END AS Title,
COUNT(*) AS Total,
COUNT(CASE rf.Value WHEN '3' THEN 1 ELSE NULL END) AS Good,
COUNT(CASE rf.Value WHEN '4' THEN 1 ELSE NULL END) AS BorderlineHigh,
COUNT(CASE rf.Value WHEN '5' THEN 1 ELSE NULL END) AS TooHigh,
COUNT(CASE rf.Value WHEN '6' THEN 1 ELSE NULL END) AS dontknow,
COUNT(CASE rf.Value WHEN '7' THEN 1 ELSE NULL END) AS NotDone,
COUNT(CASE rf.Value WHEN '8' THEN 1 ELSE NULL END) AS Incomplete
FROM tbOSetItem AS osi INNER JOIN
vuRspField AS rf ON rf.RspId = osi.ObjId
WHERE (osi.OSetId = 13598) AND (rf.QFldName = 'labresult')
GROUP BY rf.SeqNo
ORDER BY rf.SeqNo
THANKS!
Start Free Trial
