Solved

Combining 2 queries into one

Posted on 2007-11-26
3
193 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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you so much!  
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now