Solved

Combining 2 queries into one

Posted on 2007-11-26
3
196 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

752 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