Link to home
Start Free TrialLog in
Avatar of Dee
DeeFlag for United States of America

asked on

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

Avatar of Dee
Dee
Flag of United States of America image

ASKER

'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

ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dee

ASKER

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
>>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.


Avatar of Dee

ASKER

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
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
Avatar of Dee

ASKER

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.