Solved

Combining 2 queries into one

Posted on 2007-11-26
3
197 Views
Last Modified: 2010-04-21
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
Comment
Question by:tracimcp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 27

Accepted Solution

by:
MikeToole earned 500 total points
ID: 20351255
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20352583
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
 

Author Closing Comment

by:tracimcp
ID: 31411006
Thank you so much!  
0

Featured Post

Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question