Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Combining 2 queries into one

Posted on 2007-11-26
3
Medium Priority
?
199 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 2000 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

URL rewriting in AWS CloudFront

A quick how-to guide to implement with a Lambda function!

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

722 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