September 27, 2013 at 12:27 am
Greetings, I have several queries that all produce a "yes" or "no" outcome. I need to consolidate these queries in a way that will produce a count for each "yes" and produce a result like 2 out of 3. So if 4/4 is produced it would say challenge, 3/4 benchmark, 2/4 strategic, 1/4 & 0/4 intensive. What's the best way to do this? Is it even possible? Greatly appreciate any help on this.
Here are the queries:
1.)
SELECT
CASE WHEN HISTORY >= 6 AND
ELA >= 6 AND
MATH >= 6 AND
SCIENCE >= 6 AND
FL >= 6 AND
VA >= 6 AND
Prep >= 6
THEN 'Yes'
ELSE 'No' END AS Yes_No
FROM (
SELECT
STU.ID,
COUNT(CASE WHEN CRS.U1 = 'A' THEN HIS.CN END) AS HISTORY,
COUNT(CASE WHEN CRS.U1 = 'B' THEN HIS.CN END) AS ELA,
COUNT(CASE WHEN CRS.U1 = 'C' THEN HIS.CN END) AS MATH,
COUNT(CASE WHEN CRS.U1 = 'D' THEN HIS.CN END) AS SCIENCE,
COUNT(CASE WHEN CRS.U1 = 'E' THEN HIS.CN END) AS FL,
COUNT(CASE WHEN CRS.U1 = 'F' THEN HIS.CN END) AS VA,
COUNT(CASE WHEN CRS.U1 = 'G' THEN HIS.CN END) AS Prep
FROM dbo.CRS INNER JOIN
dbo.HIS ON CRS.CN = HIS.CN INNER JOIN
dbo.STU ON HIS.PID = STU.ID
WHERE
STU.ID = @ID
) AS derived
2.)
WITH cteSource(CN, U1)
AS (
SELECTr.CN,
r.U1
FROMdbo.SSS AS s
INNER JOINdbo.STU AS t ON t.SN = s.SN
INNER JOINdbo.CRS AS r ON r.CN = s.CN
WHEREt.ID = @ID
UNION ALL
SELECTr.CN,
r.U1
FROMdbo.HIS AS i
INNER JOINdbo.CRS AS r ON r.CN = i.CN
WHEREi.PID = @ID
)
SELECT
CASE
WHEN p.A >= 6 AND p.B >= 6 AND p.C >= 6 AND p.D >= 6 AND p.E >= 6 AND p.F >= 6 AND p.G >= 6 THEN 'Yes'
ELSE 'No'
END AS [On Target?]
FROMcteSource AS s
PIVOT(
COUNT(s.CN)
FOR s.U1 IN ([A], , [C], [D], [E], [F], [G])
) AS p;
3.)
SELECT MIN(CASE WHEN TST.SS > 349 AND CTL.NM = 'Math- total' AND CTL.ID = 'CAHSEE' THEN 'Yes' WHEN TST.SS > 349 AND CTL.NM = 'ELA- total' AND
CTL.ID = 'CAHSEE' THEN 'Yes' ELSE 'No' END) AS Pass
FROM TST INNER JOIN
CTL ON TST.PT = CTL.PT
WHERE (CTL.NM LIKE '%- total') AND (TST.PID = @ID) AND (TST.ID = 'CAHSEE')
4.)
SELECT CASE WHEN [Total Absences] < @Absences THEN 'Yes' ELSE 'No' END AS [On Target?]
FROM (SELECT SUM(CASE WHEN Val = 'A' THEN 1 ELSE 0 END) AS [Total Absences]
FROM (SELECT ATT.*
FROM ATT INNER JOIN
STU ON ATT.SC = STU.SC AND ATT.SN = STU.SN
WHERE STU.ID = @ID) t UNPIVOT (Val FOR Cat IN ([A1], [A2], [A3], [A4], [A5], [A6])) u) AS derived
September 27, 2013 at 12:40 am
You can use UNION ALL to string the resultsets together in one resultset.
This resultset you can use as a subquery for another query which counts the Yes answers.
WITH CTE_Results AS
(
result1
UNION ALL
result2
UNION ALL
...
UNION ALL
resultN
)
SELECT CONVERT(VARCHAR(5),SUM(CASE WHEN Yes_No = 'Yes' THEN 1 ELSE 0 END)) + '/n'
FROM CTE_Results
Maybe use a bit column as result for the different queries instead of strings.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 29, 2013 at 10:47 am
Got it thanks so much!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply