September 30, 2013 at 10:27 am
I need to create a query that is comprised of subqueries, I am using a CTE, but one of the subqueries also is using a CTE...Can you nest CTE like this??
WITH CTE_Results AS
(
SELECT
CASE WHEN HISTORY >= 2 AND
ELA >= 4 AND
MATH >= 4 AND
SCIENCE >= 3 AND
FL >= 3 AND
VA >= 1 AND
Prep >= 0
THEN 'Yes'
ELSE 'No' END AS [On Target?]
FROM (
SELECT
COUNT(CASE WHEN CRS.U1 = 'A' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS HISTORY,
COUNT(CASE WHEN CRS.U1 = 'B' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS ELA,
COUNT(CASE WHEN CRS.U1 = 'C' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS MATH,
COUNT(CASE WHEN CRS.U1 = 'D' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS SCIENCE,
COUNT(CASE WHEN CRS.U1 = 'E' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS FL,
COUNT(CASE WHEN CRS.U1 = 'F' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS VA,
COUNT(CASE WHEN CRS.U1 = 'G' AND (HIS.MK NOT LIKE '%FD%') 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 = 4068968
) AS derived
UNION ALL
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 = 4068968
UNION ALL
SELECTr.CN,
r.U1
FROMdbo.HIS AS i
INNER JOINdbo.CRS AS r ON r.CN = i.CN
WHEREi.PID = 4068968
)
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;
SELECT CONVERT(VARCHAR(5),SUM(CASE WHEN [On Target?] = 'Yes' THEN 1 ELSE 0 END)) + '/2'
FROM CTE_Results
September 30, 2013 at 10:32 am
you can seperate CTEs by using a comma i.e.
WITH cte_name AS (select 1 col1),
cte_name_2 as (select 2 col2)
select * from cte_name
union all
select * from cte_name_2
September 30, 2013 at 11:27 am
Taking what Keith said, you could re-write your query like this:
WITH cteSource(CN, U1)
AS (
SELECTr.CN, r.U1
FROM dbo.SSS AS s
INNER JOINdbo.STU AS t ON t.SN = s.SN
INNER JOINdbo.CRS AS r ON r.CN = s.CN
WHERE t.ID = 4068968
UNION ALL
SELECTr.CN, r.U1
FROM dbo.HIS AS i
INNER JOINdbo.CRS AS r ON r.CN = i.CN
WHERE i.PID = 4068968
),
CTE_Results AS
(
SELECT
CASE WHEN HISTORY >= 2 AND
ELA >= 4 AND
MATH >= 4 AND
SCIENCE >= 3 AND
FL >= 3 AND
VA >= 1 AND
Prep >= 0
THEN 'Yes'
ELSE 'No' END AS [On Target?]
FROM
(
SELECT
COUNT(CASE WHEN CRS.U1 = 'A' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS HISTORY,
COUNT(CASE WHEN CRS.U1 = 'B' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS ELA,
COUNT(CASE WHEN CRS.U1 = 'C' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS MATH,
COUNT(CASE WHEN CRS.U1 = 'D' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS SCIENCE,
COUNT(CASE WHEN CRS.U1 = 'E' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS FL,
COUNT(CASE WHEN CRS.U1 = 'F' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS VA,
COUNT(CASE WHEN CRS.U1 = 'G' AND (HIS.MK NOT LIKE '%FD%') 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 = 4068968
) AS derived
UNION ALL
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?]
FROM cteSource AS s
PIVOT ( COUNT(s.CN)
FOR s.U1 IN ([A], , [C], [D], [E], [F], [G])) AS p
)
SELECTCONVERT(VARCHAR(5),
SUM(CASE WHEN [On Target?] = 'Yes' THEN 1 ELSE 0 END)) + '/2'
FROM CTE_Results
-- Itzik Ben-Gan 2001
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply