I need to run a qry and depending on the results, run a 2nd qry

I need to run a query and depending on the results being > 20, run a second query using the value returned from the first query.

My DB admin furnished both queries, logic and syntax, for me to use in an SSRS report.

I can get query 1 to run independently which returns appcount.  I don't understand all the syntax in query 2, but I tried running it independently and plugging in a literal in place of appcount just to see if it would run.  I get Incorrect syntax near '='. (before =NTOT).

I am using MS SQL Server 2008R2 and SSRS
--Query 1) Get appcount

--If appcount > 20 then use appcount in query 2 and return the the results from query 2
--else return "App count less than 20"

---------------------------------------------
--Query 1 -- appcount:
SELECT appcount=CASE WHEN SUM(1) IS null then 0 else SUM(1) end

FROM Table1
where application_recvd_dt=CAST(Getdate()-1 as date)
-----------------------------------------------------


---Query 2
select * from
(
select VarName, Metric
from(
select VarName
,Mean = case when Mean between MeanMin and MeanMax then 0 else 1 end
,Minimum = case when Minimum >= MinMin and Minimum <= MaxMax then 0 else 1 end
,Maximum = case when Maximum <= MaxMax and Maximum >= MinMin then 0 else 1 end
,NAprop = case when NAprop between NAmin and NAMax then 0 else 1 end
,INprop = case when INprop between INmin and INMax then 0 else 1 end
,Median = case when Median between MinMed and MaxMed then 0 else 1 end


FROM Table2 --us

unpivot
(Outbounds for metric IN (Mean, Minimum, Maximum, NAprop, INprop, Median)) as st
where 
Outbounds = 1) VR
left join
(select VarName,appcount=NTOT 
FROM [ACA_DW].[ods].[APMAFAppChimneyStatisticsDaily]) SS
on VR.VarName=SS.VarName

Open in new window

Delta7428Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Delta7428Author Commented:
'us' after Table2 should not have been commented out.
--1) Get appcount
--2) If appcount is > 20, then return results from Query 2

---------------------------------------------
--Query 1 -- appcount:
SELECT appcount=CASE WHEN SUM(1) IS null then 0 else SUM(1) end

FROM Table1
where application_recvd_dt=CAST(Getdate()-1 as date)
-----------------------------------------------------



select * from
(
select VarName, Metric
from(
select VarName
,Mean = case when Mean between MeanMin and MeanMax then 0 else 1 end
,Minimum = case when Minimum >= MinMin and Minimum <= MaxMax then 0 else 1 end
,Maximum = case when Maximum <= MaxMax and Maximum >= MinMin then 0 else 1 end
,NAprop = case when NAprop between NAmin and NAMax then 0 else 1 end
,INprop = case when INprop between INmin and INMax then 0 else 1 end
,Median = case when Median between MinMed and MaxMed then 0 else 1 end

FROM Table2 us

unpivot
(Outbounds for metric IN (Mean, Minimum, Maximum, NAprop, INprop, Median)) as st
where 
Outbounds = 1) VR
left join
(select VarName,appcount=NTOT 
FROM [ACA_DW].[ods].[APMAFAppChimneyStatisticsDaily]) SS
on VR.VarName=SS.VarName

Open in new window

0
ralmadaCommented:
What about something like this?
if (SELECT count(application_recvd_dt) FROM Table1 where application_recvd_dt=CAST(Getdate()-1 as date)) > 20
begin
	select VR.VarName, VR.Metric, SS.appcount
	from(
		select VarName, Metric 
		from
		( select VarName
			,Mean = case when Mean between MeanMin and MeanMax then 0 else 1 end
			,Minimum = case when Minimum >= MinMin and Minimum <= MaxMax then 0 else 1 end
			,Maximum = case when Maximum <= MaxMax and Maximum >= MinMin then 0 else 1 end
			,NAprop = case when NAprop between NAmin and NAMax then 0 else 1 end
			,INprop = case when INprop between INmin and INMax then 0 else 1 end
			,Median = case when Median between MinMed and MaxMed then 0 else 1 end
		FROM Table2 )	us
		unpivot	(Outbounds for metric IN (Mean, Minimum, Maximum, NAprop, INprop, Median)) as st
		where Outbounds = 1
	) VR
	left join
	(
		select 	VarName,
			appcount=NTOT 
		FROM [ACA_DW].[ods].[APMAFAppChimneyStatisticsDaily]
	
	) SS on VR.VarName=SS.VarName
end;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Delta7428Author Commented:
ralmada your query runs.  Now I'm trying understand the query enough to validate the output.

You are getting the correct appcount of 57.

Here's your query results:  

VarName                           Metric      appcount
----------------------------------------------------------
Income                               Mean      57
Income                               Median      57
residence_payment_amt      Median      57


Attached is a text file of the results from my query "SELECT * FROM Table1 where VarName = 'Income' or VarName = 'residence_payment_amt'"

Does that give you info to tell in results are correct?

(By the way, [ACA_DW].[ods].[APMAFAppChimneyStatisticsDaily and Table2 are the same table.  I meant to replace both with "Table2" alias in my previous post)
0ChimDailyStatitics.txt
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

ralmadaCommented:
>>Does that give you info to tell in results are correct?<<

Well I'm sorry but  I cannot answer since I'm not familiar with your data and table structure. You will have to validate that.

Now, if you find that the result is not right, please post some sample data from your initial tables and what is the expected result.


0
Delta7428Author Commented:
I'm trying to run the second query independenlty to see what it returns (with appcount commented out)
But I'm getting the following syntax error.  Any help there?

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'VarName'.  


select * from
(
select VarName, Metric
from(
select VarName
,Mean = case when Mean between MeanMin and MeanMax then 0 else 1 end
,Minimum = case when Minimum >= MinMin and Minimum <= MaxMax then 0 else 1 end
,Maximum = case when Maximum <= MaxMax and Maximum >= MinMin then 0 else 1 end
,NAprop = case when NAprop between NAmin and NAMax then 0 else 1 end
,INprop = case when INprop between INmin and INMax then 0 else 1 end
,Median = case when Median between MinMed and MaxMed then 0 else 1 end

FROM Table2 us

unpivot
(Outbounds for metric IN (Mean, Minimum, Maximum, NAprop, INprop, Median)) as st
where
Outbounds = 1) VR
left join
(select VarName --,appcount=NTOT
FROM Table2) SS
on VR.VarName=SS.VarName
0
ralmadaCommented:
it should be like this:


select * from
(
      select VarName, Metric
      from(
            select VarName
                  ,Mean = case when Mean between MeanMin and MeanMax then 0 else 1 end
                  ,Minimum = case when Minimum >= MinMin and Minimum <= MaxMax then 0 else 1 end
                  ,Maximum = case when Maximum <= MaxMax and Maximum >= MinMin then 0 else 1 end
                  ,NAprop = case when NAprop between NAmin and NAMax then 0 else 1 end
                  ,INprop = case when INprop between INmin and INMax then 0 else 1 end
                  ,Median = case when Median between MinMed and MaxMed then 0 else 1 end
            FROM Table2
      ) us
      
      unpivot (Outbounds for metric IN (Mean, Minimum, Maximum, NAprop, INprop, Median)) as st
      where Outbounds = 1
) VR
left join
(
      select VarName --,appcount=NTOT
      FROM Table2
) SS
on VR.VarName=SS.VarName
0
Delta7428Author Commented:
ralmada, I don't think your output is correct according to the sample data I attached to my last post.

I am trying verify the output with the original author of query 2 in my original post.    I will wait on that in case I have another question for you.  

Either way, I will give you full points for supplying logic with If statement.  Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.