Dee
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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].[APMAFAppCh imneyStati sticsDaily and Table2 are the same table. I meant to replace both with "Table2" alias in my previous post)
0ChimDailyStatitics.txt
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].[APMAFAppCh
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.
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.
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
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
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
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.
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.
ASKER
Open in new window