Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Combining 2 queries into one

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

916 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