WITH I AS (
SELECT State_ID, _5_8_StrengthDate AS DATE, 'strengthsExplorerAccessCode' AS element, _5_8_AccessCode AS VALUE
FROM cust.CliftonStrengths
UNION
SELECT State_ID, _5_8_StrengthDate AS DATE, 'strengthsExplorer' AS element, _5_8_Strength1 AS VALUE
FROM cust.CliftonStrengths
UNION
SELECT State_ID, _5_8_StrengthDate AS DATE, 'strengthsExplorer' AS element, _5_8_Strength2 AS VALUE
FROM cust.CliftonStrengths
UNION
SELECT State_ID, _5_8_StrengthDate AS DATE, 'strengthsExplorer' AS element, _5_8_Strength3 AS VALUE
FROM cust.CliftonStrengths
UNION
SELECT State_ID, HS_StrengthDate AS DATE, 'strengthsFinder' AS element, HS_Strength1 AS VALUE
FROM cust.CliftonStrengths
UNION
SELECT State_ID, HS_StrengthDate AS DATE, 'strengthsFinder' AS element, HS_Strength2 AS VALUE
FROM cust.CliftonStrengths
UNION
SELECT State_ID, HS_StrengthDate AS DATE, 'strengthsFinder' AS element, HS_Strength3 AS VALUE
FROM cust.CliftonStrengths
UNION
SELECT State_ID, HS_StrengthDate AS DATE, 'strengthsFinder' AS element, HS_Strength4 AS VALUE
FROM cust.CliftonStrengths
UNION
SELECT State_ID, HS_StrengthDate AS DATE, 'strengthsFinder' AS element, HS_Strength5 AS VALUE
FROM cust.CliftonStrengths
)
 
--INSERT INTO CustomStudent (personID,attributeID,value,date)
SELECT personID,A.attributeID, COALESCE(D.code,CASE
WHEN I.value = 'Acheiving' THEN '01'
WHEN I.value = 'Responitily' THEN '29'
WHEN I.value = 'Disipline' THEN '15'
ELSE I.value
END) AS VALUE,COALESCE(I.date,'2023-10-15') AS DATE
FROM I
LEFT JOIN CampusAttribute A ON A.element = I.element
LEFT JOIN Person P ON P.stateID = I.State_ID
LEFT JOIN CampusDictionary D ON D.attributeID = A.attributeID AND I.value = D.value
WHERE I.value IS NOT NULL

WITH 
-- Students
S AS (
    SELECT P.personID,P.stateID AS studentNumber, I.lastName, I.firstName,E.grade,C.name AS calendarName
    FROM Person P
    JOIN [IDENTITY] I ON I.identityID = P.currentIdentityID
    JOIN Enrollment E ON E.personID = P.personID AND E.serviceType = 'P'
    JOIN Calendar C ON C.calendarID = E.calendarID AND C.summerSchool = 0 AND C.exclude = 0
    JOIN SchoolYear SY ON SY.endYear = C.endYear AND SY.active = 1
    JOIN School S ON S.schoolID = C.schoolID AND S.exclude = 0 --AND S.number = '2050'
),
--Clifton Strengths
CS AS (
    SELECT CS.personID, CS.[DATE], CD.value AS theme, CA.name AS strengths
    FROM CampusAttribute CA
    JOIN CustomStudent CS ON CS.attributeID = CA.attributeID
    JOIN CampusDictionary CD ON CD.attributeID = CS.attributeID AND CD.[code] = CS.[VALUE]
    WHERE CA.object = 'Clifton Strengths'
),
--Roster
R AS (
    SELECT C.name AS course, S.teacherDisplay AS teacher, Term.name AS term, PS.name AS periodSchedule, P.name AS [period], R.personID
    FROM Trial T
    JOIN Calendar Cal ON Cal.calendarID = T.calendarID AND T.active = 1 AND Cal.summerSchool = 0 AND Cal.exclude = 0
    JOIN SchoolYear SY ON SY.endYear = Cal.endYear AND SY.active = 1
    JOIN School Sch ON Sch.schoolID = Cal.schoolID AND Sch.exclude = 0 --AND Sch.number = '2050'
    JOIN SECTION S ON S.trialID = T.trialID
    JOIN Course C ON S.courseID = C.courseID
    JOIN Roster R ON R.sectionID = S.sectionID
    JOIN SectionPlacement SP ON SP.sectionID = S.sectionID
    JOIN [Period] P ON P.periodID = SP.periodID
    JOIN PeriodSchedule PS ON P.periodScheduleID = PS.periodScheduleID
    JOIN Term ON Term.termID = SP.termID
)
 
SELECT 
COALESCE(S.studentNumber,'') AS studentNumber, 
S.lastName, 
S.firstName, 
COALESCE(CS.strengths,'') AS strengths,
COALESCE(CS.theme,'') AS theme, 
--R.course, 
--R.teacher, 
--R.term,
--R.periodSchedule, 
--R.period,
S.grade,
S.calendarName
FROM S
--JOIN R ON S.personID = R.personID
LEFT JOIN CS ON CS.personID = S.personID