Hello
I'm returning to SQL after a number of years and struggling tbh. I'm not entirely sure I will be making sense. This is probably quite simple. This code creates XML that is pulled into another system and i've been asked to edit to incorporate attendance but it has to be a subquery as it imports more than one field.
The specific error is on this part
( SELECT
sd.PossibleMarks
,sd.PresentMarks
FROM sd) AS Attendance
Invalid object name 'sd'.
This is the whole query. Could someone help set me straight please?
DECLARE @academicYearID varchar(20)
SET @academicYearID = '20/21'
SELECT
sd.studentid as ID
,LTRIM(RTRIM(sd.FirstForename)) as Forename
,LTRIM(RTRIM(sd.Surname)) as Surname
,sd.FirstForename as LegalForename
,LTRIM(RTRIM(sd.Surname)) as LegalSurname
,CASE sd.Sex WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' ELSE '' END as Gender
,sd.UniqueLearnerNo as ULN
,convert(varchar(10), sd.DateOfBirth, 120) as DateOfBirth
,replace(replace(replace(sd.Address1+'|'+isnull(sd.Address2,'')+'|'+
isnull(sd.Address3,'')+'|'+isnull(sd.Address4,'')+'|'+
ltrim(rtrim(sd.PostcodeOut))+' '+ltrim(rtrim(sd.PostcodeIn)),'|','<>'),'><',''),'<>',CHAR(13)+CHAR(10)) as AddressBlock
,LTRIM(RTRIM(coalesce(sd.MobileTel,sd.Tel1,''))) as Telephone
,coalesce(LTRIM(RTRIM(sd.Email)),'') as Email
,sd.EthnicGroup AS Ethnicity
,( SELECT
sd.PossibleMarks
,sd.PresentMarks
FROM sd) AS Attendance
,(
select
sn.NeedType
,sn.NeedTypeCode
from
(
select
sd1.StudentDetailID
,ld.Description as NeedType
,'L'+CONVERT(varchar(10),ld.LearningDifficultyID) as NeedTypeCode
from ProSolution..StudentDetail sd1 with (nolock)
join ProSolution..LearningDifficulty ld with (nolock)
on sd1.LearningDiffOrDisID=ld.LearningDifficultyID
where ld.LearningDifficultyID =1
UNION
select
sd2.StudentDetailID
,d.Description as NeedType
,'D'+Convert(varchar(10),d.DisabilityCategoryID) as NeedTypeCode
from ProSolution..StudentDisability sd2 with (nolock)
join ProSolution..DisabilityCategory d with (nolock)
on sd2.DisabilityCategoryID=d.DisabilityCategoryID
where d.DisabilityCategoryID < 98
) sn
where sd.StudentDetailID=sn.StudentDetailID
for xml path('SpecialNeed'), type, elements
)
from
(
select
distinct
sd.studentid
,sd.StudentDetailID
,sd.FirstForename
,sd.Surname
,sd.sex
,sd.DateOfBirth
,sd.Address1
,sd.Address2
,sd.Address3
,sd.Address4
,sd.PostcodeOut
,sd.PostcodeIn
,sd.UniqueLearnerNo
,sd.MobileTel
,sd.Email
,sd.Tel1
,sd.EthnicGroup
,sd.PossibleMarks
,sd.PresentMarks
from ProSolutionReports.dbo.OurStudentDetailCPOMS sd
join ProSolution..Enrolment e with (nolock)
on sd.StudentDetailID=e.StudentDetailID
--and e.UserDefined13=1
where sd.AcademicYearID=@academicyearid
-- and ProSolution.dbo.GetAgeOn31Aug(sd.dateofbirth,sd.academicyearID) < 25
--and e.CompletionStatusID='1'
) sd
order by sd.StudentID
Thankyou for any asistance