11-01-2017 4:25 PM
Hi
I am getting an error when querying an SQL database stating that only SELECT statements are permitted when attempting to run the following query. Can I not use variables?
DECLARE @acadyear AS VARCHAR(20);
SET @acadyear = '2012/2013';
DECLARE @studentcount AS INTEGER;
DECLARE @date AS SMALLDATETIME;
DECLARE @eventstart AS SMALLDATETIME;
DECLARE @eventend AS SMALLDATETIME;
SET @eventstart = dfsi.dbo.Getyearstartdate(@Acadyear);
SET @eventend = dfsi.dbo.Getyearenddate(@Acadyear);
SET @date = CASE
WHEN Getdate() > @eventend THEN @eventend
ELSE Getdate()
END;
SELECT @acadyear AS academicyear,
'Attendance' as Cat_text,
'Average' as text,
'Whole School' as text_Whole,
Count(DISTINCT person_id) AS groupcount,
CASE
WHEN Sum(possible) = 0 THEN 0
ELSE Cast(Sum(attendancetotal) * 100 / Sum(possible) AS DECIMAL(5, 2))
END AS attendance,
Sum(attendancetotal) AS attendancetotal,
CASE
WHEN Sum(possible) = 0 THEN 0
ELSE Cast(Sum(presenttotal) * 100 / Sum(possible) AS DECIMAL(5, 2))
END AS present,
Sum(presenttotal) AS presenttotal,
CASE
WHEN Sum(possible) = 0 THEN 0
ELSE Cast(Sum(aeatotal) * 100 / Sum(possible) AS DECIMAL(5, 2))
END AS aea,
Sum(aeatotal) AS aeatotal,
CASE
WHEN Sum(possible) = 0 THEN 0
ELSE Cast(Sum(authorisedabsencetotal) * 100 / Sum(possible) AS DECIMAL(5, 2))
END AS aa,
Sum(authorisedabsencetotal) AS authorisedabsencetotal,
CASE
WHEN Sum(possible) = 0 THEN 0
ELSE Cast(Sum(unauthorisedabsencetotal) * 100 / Sum(possible) AS DECIMAL(5, 2))
END AS uaa,
Sum(unauthorisedabsencetotal) AS unauthorisedabsencetotal,
CASE
WHEN Sum(possible) = 0 THEN 0
ELSE Cast(Sum(latebeforetotal) * 100 / Sum(possible) AS DECIMAL(5, 2))
END AS latebefore,
Sum(latebeforetotal) AS latebeforetotal,
CASE
WHEN Sum(possible) = 0 THEN 0
ELSE Cast(Sum(lateaftertotal) * 100 / Sum(possible) AS DECIMAL(5, 2))
END AS lateafter,
Sum(lateaftertotal) AS lateaftertotal,
CASE
WHEN Sum(possible) = 0 THEN 0
ELSE Cast(Sum(latestotal) * 100 / Sum(possible) AS DECIMAL(5, 2))
END AS lates,
Sum(latestotal) AS latestotal,
CASE
WHEN Sum(possible) = 0 THEN 0
ELSE Cast(Sum(missingtotal) * 100 / Sum(possible) AS DECIMAL(5, 2))
END AS missing,
Sum(missingtotal) AS missingtotal,
Sum(possible) AS possibletotal
FROM (SELECT *,
CASE
WHEN possible = 0 THEN 0
ELSE Cast(( present + aea ) * 100 / possible AS DECIMAL(5, 2))
END AS attendancepc,
( present + aea ) AS attendancetotal,
CASE
WHEN possible = 0 THEN 0
ELSE Cast(present * 100 / possible AS DECIMAL(5, 2))
END AS presentpc,
present AS presenttotal,
CASE
WHEN possible = 0 THEN 0
ELSE Cast(aea * 100 / possible AS DECIMAL(5, 2))
END AS aeapc,
aea AS aeatotal,
CASE
WHEN possible = 0 THEN 0
ELSE Cast(authorisedabsence * 100 / possible AS DECIMAL(5, 2))
END AS authorisedpc,
authorisedabsence AS authorisedabsencetotal,
CASE
WHEN possible = 0 THEN 0
ELSE Cast(( unauthorisedabsence ) * 100 / possible AS DECIMAL(5, 2))
END AS unauthorisedpc,
unauthorisedabsence AS unauthorisedabsencetotal,
CASE
WHEN possible = 0 THEN 0
ELSE Cast(( latebefore + lateafter ) * 100 / possible AS DECIMAL(5, 2))
END AS latepc,
( latebefore + lateafter ) AS latestotal,
CASE
WHEN possible = 0 THEN 0
ELSE Cast(latebefore * 100 / possible AS DECIMAL(5, 2))
END AS latebeforepc,
latebefore AS latebeforetotal,
CASE
WHEN possible = 0 THEN 0
ELSE Cast(lateafter * 100 / possible AS DECIMAL(5, 2))
END AS lateafterpc,
lateafter AS lateaftertotal,
CASE
WHEN possible = 0 THEN 0
ELSE Cast(missing * 100 / possible AS DECIMAL(5, 2))
END AS missingpc,
missing AS missingtotal,
CASE
WHEN possible = 0 THEN 0
ELSE Cast(unexplained * 100 / possible AS DECIMAL(5, 2))
END AS unexplainedpc,
unexplained AS unexplainedtotal
FROM (SELECT sd.person_id,
chosen_forename,
chosen_surname,
gender,
year_group,
reg,
house,
@eventstart AS yearstart,
@date AS date,
Sum(present) AS present,
Sum(present) - Sum(lates) AS present_minus_Lates,
Sum(attended) AS attended,
Sum(authorisedabsence) AS authorisedabsence,
Sum(unauthorisedabsence) AS unauthorisedabsence,
Sum(aea) AS aea,
Sum(latebefore) AS latebefore,
Sum(lateafter) AS lateafter,
Sum(lates) AS lates,
Sum(Minutes_late) AS Minutes_late,
Cast(Avg(Minutes_late)AS DECIMAL(5, 1)) AS Minutes_late_Ave,
Sum(possible) AS possible,
Sum(not_required) AS not_required,
Sum(missing) AS missing,
Sum(unexplained) AS unexplained
FROM (SELECT * FROM dfsi.dbo.attendance_summary_cache a
WHERE Summary_Type = 'Person Day'
AND session_start >= @eventstart
AND session_start <= @date
--[APPENDFILTER(TERM, Term_or_holiday)]
--[APPENDFILTER(ATTENDANCEDATE, session_start)]
--[APPENDCLIENTFILTER(MARKDATE, session_start)]
--[APPENDCLIENTFILTER(ATTENDANCEDATE, session_start)]
--[APPENDCLIENTFILTER(TERM, Term_or_holiday)]
) dt1
JOIN dfsi.dbo.Stud_details_cache sd
ON sd.person_id = dt1.att_person_id and sd.acadyear = @acadyear
WHERE 1=1
--[APPENDFILTER(YG,year_group)]
--[APPENDFILTER(GENDER,GENDER)]
--[APPENDFILTER(HOUSE,HOUSE)]
--[APPENDFILTER(FSM,FSM)]
--[APPENDFILTER(PREMIUM,PREMIUM)]
--[APPENDFILTER(SEN,SEN_STATUS)]
--[APPENDFILTER(GIFTED,GIFTED)]
--[APPENDFILTER(ETHNICITY,ETHNICITY)]
--[APPENDFILTER(EAL,EAL)]
--[APPENDFILTER(AttendanceDate,session_start)]
--[APPENDFILTER(TERM,Term_or_holiday)]
--[APPENDCLIENTFILTERS]
Group by sd.person_id,
chosen_forename,
chosen_surname,
gender,
year_group,
reg,
house
) dt2)dt3
[Private information removed by Moderator]
11-01-2017 8:37 PM
11-02-2017 9:05 PM
Shouldn't this be posted on MSDN instead? Very much confused by this question... And why oh why copy-paste pages of SQL?