Need to combine multiple sql queries into one to produce a single outcome

  • 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

  • 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

  • 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