• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

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

0
Delta7428
Asked:
Delta7428
  • 4
  • 3
1 Solution
 
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
 
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
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!

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now