• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

Combining 2 queries into one

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!
0
tracimcp
Asked:
tracimcp
1 Solution
 
MikeTooleCommented:
You need to include the extra data via your Where clause:
WHERE     (osi.OSetId = 13598) AND ((rf.QFldName = 'Lifestyle')OR (rf.QFldName = 'labresult'))--and rf.seqno in (1,9,10,11)

Then next the Case statement for the title, e.g:
SELECT    
Case When rf.QFldname = 'labresult' Then 'Blood pressure' Else
  Case  rf.SeqNo,WHEN 1 THEN 'Weight' WHEN 9 THEN 'Waist' WHEN 10 THEN 'Waist-to-hip ratio' WHEN 11 THEN 'Body fat'
  END
End AS Title,

For the other fields you can use the other form of Case:

COUNT(CASE WHEN (rf.Value = '1') And  (rf.QFldname <> 'labresult') THEN 1 ELSE NULL END) AS Healthy
0
 
imitchieCommented:
try this one.
the inner select applies the filters, which is important for large tables
the values are then sorted into Bucket1 - for title, and Bucket2 - for columns

I have also swapped COUNT for SUM. SUM will get rid of this warning for you, and means really the same thing

"Warning: Null value is eliminated by an aggregate or other SET operation."
SELECT
Case bucket1
	WHEN 'I1' THEN 'Weight'
	WHEN 'I9' THEN 'Waist'
	WHEN 'I10' THEN 'Waist-to-hip ratio'
	WHEN 'I11' THEN 'Body fat'
	WHEN 'A1' THEN 'Blood pressure'
	END AS Title,
COUNT(*) AS Total,
SUM(CASE bucket2 WHEN 'I1' THEN 1 ELSE 0 END) AS Healthy,
SUM(CASE bucket2 WHEN 'I2' THEN 1 ELSE 0 END) AS Average,
SUM(CASE bucket2 WHEN 'I3' THEN 1 ELSE 0 END) AS AtRisk,
SUM(CASE bucket2 WHEN 'I4' THEN 1 ELSE 0 END) AS Incomplete,
SUM(CASE bucket2 WHEN 'A3' THEN 1 ELSE 0 END) AS Good,
SUM(CASE bucket2 WHEN 'A4' THEN 1 ELSE 0 END) AS BorderlineHigh,
SUM(CASE bucket2 WHEN 'A5' THEN 1 ELSE 0 END) AS TooHigh,
SUM(CASE bucket2 WHEN 'A6' THEN 1 ELSE 0 END) AS dontknow,
SUM(CASE bucket2 WHEN 'A7' THEN 1 ELSE 0 END) AS NotDone,
SUM(CASE bucket2 WHEN 'A8' THEN 1 ELSE 0 END) AS Incomplete
FROM
(
SELECT rf.SeqNo, rf.Value,
	left(rf.QFldName,2) + cast(rf.SeqNo as varchar) as bucket1,
	left(rf.QFldName,2) + rf.Value as bucket2
FROM tbOSetItem AS osi
INNER JOIN vuRspField AS rf ON rf.RspId = osi.ObjId
WHERE osi.OSetId = 13598
  AND rf.QFldName in ('Lifestyle', 'labresult')
) data
GROUP BY SeqNo
ORDER BY SeqNo

Open in new window

0
 
tracimcpAuthor Commented:
Thank you so much!  
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now