asked on 10/4/2011

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

Last Comment

10/4/2011

10/4/2011

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

ralmada

10/4/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Dee

10/4/2011

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

ralmada

10/4/2011

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

Dee

10/4/2011

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

ralmada

10/4/2011

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

Dee

10/4/2011

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.