I am getting this error 'cteSource' has fewer columns than were specified in the column list. Any help greatly appreciated!
DECLARE @GR INT = 10;
DECLARE @SC INT = 30;
WITH cteSource ( NAME, ID, GR, SC, Ethnicity, Gate, SpecialED, LF, [A-G C- or Better], Affliation, Tardies, Referrals, Absences, [A-G Schedule] )
AS ( SELECT s.ID ,
s.GR ,
s.SC ,
s.LN + ', ' + s.FN AS NAME ,
( CASE WHEN s.RC1 = 700
AND ETH = 'Y' THEN 'Hispanic'
WHEN s.RC1 = 600
AND ETH = 'N' THEN 'African American'
ELSE 'Other'
END ) AS Ethnicity ,
( CASE WHEN s.U7 = 'G' THEN 'Yes'
ELSE 'No'
END ) AS Gate ,
( CASE WHEN s.U9 IN ( 'R', 'D', 'S' ) THEN 'Yes'
ELSE 'No'
END ) AS SpecialEd ,
( CASE WHEN s.LF = '1' THEN 'English Only'
WHEN s.LF = '2'
THEN 'Initially Fluent English Proficient'
WHEN s.LF = '3' THEN 'English Learner'
WHEN s.LF = '4'
THEN 'Redesignated Fluent English Proficient'
WHEN s.LF = '5' THEN 'TBD'
ELSE '?'
END ) AS LF ,
( SELECT CASE WHEN HISTORY >= 0
AND ELA >= 1
AND MATH >= 1
AND SCIENCE >= 1
AND FL >= 1
AND VA >= 0
AND Prep >= 0 THEN 'Yes'
ELSE 'No'
END AS [A-G C- or Better]
FROM ( SELECT SUM(HISTORY) AS HISTORY ,
SUM(ELA) AS ELA ,
SUM(MATH) AS MATH ,
SUM(SCIENCE) AS SCIENCE ,
SUM(FL) AS FL ,
SUM(VA) AS VA ,
SUM(Prep) AS Prep ,
COUNT(*) AS Items
FROM ( SELECT SUM(CASE
WHEN c.U1 = 'A'
THEN 1
ELSE 0
END) AS HISTORY ,
SUM(CASE
WHEN c.U1 = 'B'
THEN 1
ELSE 0
END) AS ELA ,
SUM(CASE
WHEN c.U1 = 'C'
THEN 1
ELSE 0
END) AS MATH ,
SUM(CASE
WHEN c.U1 = 'D'
THEN 1
ELSE 0
END) AS SCIENCE ,
SUM(CASE
WHEN c.U1 = 'E'
THEN 1
ELSE 0
END) AS FL ,
SUM(CASE
WHEN c.U1 = 'F'
THEN 1
ELSE 0
END) AS VA ,
SUM(CASE
WHEN c.U1 = 'G'
THEN 1
ELSE 0
END) AS Prep
FROM CRS AS c
INNER JOIN HIS AS h ON h.CN = c.CN
AND ( h.MK NOT IN (
'F', 'F+', 'F-',
'D', 'D-', 'D+',
'NM', 'NC' ) )
INNER JOIN STU AS s ON s.ID = h.PID
AND s.GR = @GR
AND s.SC = @SC
AND s.TG NOT IN (
'N', '*', 'I' )
WHERE c.U1 IN ( 'A', 'B',
'C', 'D', 'E',
'F', 'G' )
UNION ALL
SELECT SUM(CASE
WHEN c.U1 = 'A'
THEN 1
ELSE 0
END) AS HISTORY ,
SUM(CASE
WHEN c.U1 = 'B'
THEN 1
ELSE 0
END) AS ELA ,
SUM(CASE
WHEN c.U1 = 'C'
THEN 1
ELSE 0
END) AS MATH ,
SUM(CASE
WHEN c.U1 = 'D'
THEN 1
ELSE 0
END) AS SCIENCE ,
SUM(CASE
WHEN c.U1 = 'E'
THEN 1
ELSE 0
END) AS FL ,
SUM(CASE
WHEN c.U1 = 'F'
THEN 1
ELSE 0
END) AS VA ,
SUM(CASE
WHEN c.U1 = 'G'
THEN 1
ELSE 0
END) AS Prep
FROM dbo.CRS AS c
INNER JOIN GRD AS g ON g.CN = c.CN
AND ( g.M1 NOT IN (
'F', 'F+', 'F-',
'D', 'D-', 'D+',
'NM', 'NC' ) )
INNER JOIN STU AS s ON s.SN = g.SN
AND s.GR = @GR
AND s.SC = @SC
AND s.TG NOT IN (
'N', '*', 'I' )
WHERE c.U1 IN ( 'A', 'B',
'C', 'D', 'E',
'F', 'G' )
) AS d
) AS d
UNION ALL
SELECT COALESCE(MIN(CASE WHEN a.SD IS NOT NULL
AND a.ED IS NULL
AND a.CD NOT BETWEEN 600 AND 699
THEN 'Yes'
ELSE 'No'
END), 'No') AS Affliation
FROM STU AS s
LEFT JOIN ACT AS a ON a.PID = s.ID
WHERE s.TG NOT IN ( 'N', '*', 'I' )
AND s.GR = @GR
AND s.SC = @SC
UNION ALL
SELECT CASE WHEN COALESCE([Total Tardies], 0) <= 6
THEN 'Yes'
ELSE 'No'
END AS Tardies
FROM ( SELECT SUM(CASE WHEN a.[A1] IN ( 'T',
'Z', 'B' )
THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A2] IN (
'T', 'Z', 'B' )
THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A3] IN (
'T', 'Z', 'B' )
THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A4] IN (
'T', 'Z', 'B' )
THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A5] IN (
'T', 'Z', 'B' )
THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A6] IN (
'T', 'Z', 'B' )
THEN 1
ELSE 0
END) AS [Total Tardies]
FROM ATT AS a
INNER JOIN STU AS s ON s.SN = a.SN
AND s.TG NOT IN (
'N', '*', 'I' )
AND s.GR = @GR
AND s.SC = @SC
) AS d
UNION ALL
SELECT CASE WHEN COUNT(a.PID) >= 2
AND SUM(CASE WHEN a.DS = 'SUS'
THEN 1
ELSE 0
END) >= 1 THEN 'No'
ELSE 'Yes'
END AS Referrals
FROM STU AS s
LEFT JOIN dbo.ADS AS a ON a.PID = s.ID
WHERE s.TG NOT IN ( 'N', '*', 'I' )
AND s.GR = @GR
AND s.SC = @SC
UNION ALL
SELECT CASE WHEN [Total Absenecs] <= 12
THEN 'Yes'
ELSE 'No'
END AS Absences
FROM ( SELECT SUM(CASE WHEN a.[A1] IN ( 'A',
'E', 'I', 'K',
'L', 'S', 'U',
'X' ) THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A2] IN (
'A', 'E', 'I',
'K', 'L', 'S',
'U', 'X' )
THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A3] IN (
'A', 'E', 'I',
'K', 'L', 'S',
'U', 'X' )
THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A4] IN (
'A', 'E', 'I',
'K', 'L', 'S',
'U', 'X' )
THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A5] IN (
'A', 'E', 'I',
'K', 'L', 'S',
'U', 'X' )
THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A6] IN (
'A', 'E', 'I',
'K', 'L', 'S',
'U', 'X' )
THEN 1
ELSE 0
END) AS [Total Absenecs]
FROM ATT AS a
INNER JOIN dbo.STU AS s ON s.SN = a.SN
AND s.TG NOT IN (
'N', '*', 'I' )
AND s.SC = @SC
AND s.GR = @GR
) AS d
UNION ALL
SELECT CASE WHEN A >= 0
AND B >= 1
AND C >= 1
AND D >= 1
AND E >= 1
AND F >= 0
AND G >= 0 THEN 'Yes'
ELSE 'No'
END AS [A-G Schedule]
FROM ( SELECT SUM(A) AS A ,
SUM(B) AS B ,
SUM(C) AS C ,
SUM(D) AS D ,
SUM(E) AS E ,
SUM(F) AS F ,
SUM(G) AS G
FROM ( SELECT SUM(CASE
WHEN r.U1 = 'A'
THEN 1
ELSE 0
END) AS A ,
SUM(CASE
WHEN r.U1 = 'B'
THEN 1
ELSE 0
END) AS B ,
SUM(CASE
WHEN r.U1 = 'C'
THEN 1
ELSE 0
END) AS C ,
SUM(CASE
WHEN r.U1 = 'D'
THEN 1
ELSE 0
END) AS D ,
SUM(CASE
WHEN r.U1 = 'E'
THEN 1
ELSE 0
END) AS E ,
SUM(CASE
WHEN r.U1 = 'F'
THEN 1
ELSE 0
END) AS F ,
SUM(CASE
WHEN r.U1 = 'G'
THEN 1
ELSE 0
END) AS G
FROM STU AS t
LEFT JOIN dbo.SSS
AS s ON s.SN = t.SN
LEFT JOIN dbo.CRS
AS r ON r.CN = s.CN
AND r.U1 IN (
'A', 'B', 'C',
'D', 'E', 'F',
'G' )
WHERE t.TG NOT IN ( 'N',
'*', 'I' )
AND t.GR = @GR
AND t.SC = @SC
UNION ALL
SELECT SUM(CASE
WHEN r.U1 = 'A'
THEN 1
ELSE 0
END) AS A ,
SUM(CASE
WHEN r.U1 = 'B'
THEN 1
ELSE 0
END) AS B ,
SUM(CASE
WHEN r.U1 = 'C'
THEN 1
ELSE 0
END) AS C ,
SUM(CASE
WHEN r.U1 = 'D'
THEN 1
ELSE 0
END) AS D ,
SUM(CASE
WHEN r.U1 = 'E'
THEN 1
ELSE 0
END) AS E ,
SUM(CASE
WHEN r.U1 = 'F'
THEN 1
ELSE 0
END) AS F ,
SUM(CASE
WHEN r.U1 = 'G'
THEN 1
ELSE 0
END) AS G
FROM STU AS s
LEFT JOIN dbo.HIS
AS i ON i.PID = s.ID
LEFT JOIN dbo.CRS
AS r ON r.CN = i.CN
AND r.U1 IN (
'A', 'B', 'C',
'D', 'E', 'F',
'G' )
WHERE s.TG NOT IN ( 'N',
'*', 'I' )
AND s.GR = @GR
AND s.SC = @SC
) AS d
) AS d
) AS dervied
FROM STU AS s
)
SELECT *
FROM cteSource
GROUP BY NAME ,
ID ,
GR ,
SC ,
Ethnicity ,
Gate ,
SpecialED ,
LF ,
[A-G C- or Better] ,
Affliation ,
Tardies ,
Referrals ,
Absences ,
[A-G Schedule]
Cartesian Yak
help with cte
Posted - 11/07/2013 : 02:20:31
