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
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'
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
order by sd.StudentID
Thankyou for any asistance