SELECT DI.IncidentIdentifier
, DI.SchoolId AS SchoolSwisId
, DI.SchoolId AS StudentDistrictId
, SB.StudentSwisId
, SB.StudentFirstName
, SB.StudentLastSurname
, DI.StaffUniqueId AS EducatorSwisId
, DI.EducatorFirstName
, DI.EducatorLastName
, DI.IncidentDate
, DI.IncidentTime
, SB.[First ProblemBehavior : ProblemBehaviorSubType]
, SB.[Second ProblemBehavior : ProblemBehaviorSubType]
, SB.[Third ProblemBehavior : ProblemBehaviorSubType]
, SB.[Fourth ProblemBehavior : ProblemBehaviorSubType]
, SB.[Fifth ProblemBehavior : ProblemBehaviorSubType]
, SB.[Unknown ProblemBehavior]
, DI.LocationId
, DI.MotivationId
, DI.OthersInvolvedId
, DA.[First AdminDecision : Duration]
, DA.[Second AdminDecision : Duration]
, DA.[Third AdminDecision : Duration]
, DA.[Fourth AdminDecision : Duration]
, DA.[Fifth AdminDecision : Duration]
, DA.[Unknown AdminDecision]
, DAC.[DisciplineIncidentLunchDetention]
, DAC.[DisciplineIncidentAfterSchoolDetention]
, DI.SeclusionRestraintId
, DI.OtherInfo
, SB.DisciplineIncidentOutsideIntervention
, SB.DisciplineIncidentReportIncident
, SB.DisciplineIncidentTechnologyViolation
, SB.DisciplineIncidentProblemBehavior
FROM
(
       SELECT P.IncidentIdentifier 
       , P.SchoolId
       , P.DisciplineDate
       , P.StudentUSI
       , P.[First AdminDecision : Duration]
       , P.[Second AdminDecision : Duration]
       , P.[Third AdminDecision : Duration]
       , P.[Fourth AdminDecision : Duration]
       , P.[Fifth AdminDecision : Duration]
       , P.[Unknown AdminDecision]
       FROM
       (
             SELECT I.IncidentIdentifier
             , I.SchoolId
             , I.DisciplineDate
             , I.StudentUSI
             , CASE
                    WHEN (I.OrderOfDiscipline = 1) THEN 'First AdminDecision : Duration'
                    WHEN (I.OrderOfDiscipline = 2) THEN 'Second AdminDecision : Duration'
                    WHEN (I.OrderOfDiscipline = 3) THEN 'Third AdminDecision : Duration'
                    WHEN (I.OrderOfDiscipline = 4) THEN 'Fourth AdminDecision : Duration'
                    WHEN (I.OrderOfDiscipline = 5) THEN 'Fifth AdminDecision : Duration'
                    ELSE 'Unknown AdminDecision'
               END AS "OrderName"
             , I.AdminAction
             FROM
             (
                    SELECT DADI.IncidentIdentifier
                    , DADI.SchoolId
                    , DA.DisciplineDate
                    , DA.StudentUSI
                    , DAX.OrderOfDiscipline
                    , D.CodeValue
                    , D.ShortDescription
                    , CONCAT(D.CodeValue, ' - ', D.ShortDescription, ' : ', COALESCE(CAST(DA.DisciplineActionLength AS NVARCHAR(10)), 'NULL')) AS "AdminAction"
                    FROM edfi.DisciplineAction DA
                    JOIN mi.DisciplineActionExtension DAX
                           ON DA.DisciplineActionIdentifier = DAX.DisciplineActionIdentifier
                           AND DA.DisciplineDate = DAX.DisciplineDate
                           AND DA.StudentUSI = DAX.StudentUSI
                    JOIN edfi.DisciplineActionStudentDisciplineIncidentAssociation DADI
                           ON DA.DisciplineActionIdentifier = DADI.DisciplineActionIdentifier
                           AND DA.DisciplineDate = DADI.DisciplineDate
                           AND DA.StudentUSI = DADI.StudentUSI
                    JOIN edfi.DisciplineActionDiscipline DAD
                           ON DA.DisciplineActionIdentifier = DAD.DisciplineActionIdentifier
                           AND DA.DisciplineDate = DAD.DisciplineDate
                           AND DA.StudentUSI = DAD.StudentUSI
                    JOIN edfi.DisciplineDescriptor DD
                           ON DAD.DisciplineDescriptorId = DD.DisciplineDescriptorId
                    JOIN edfi.Descriptor D
                           ON DD.DisciplineDescriptorId = D.DescriptorId 
             ) I
       ) D
       PIVOT
       (
       MAX(AdminAction)
       FOR OrderName IN ([First AdminDecision : Duration], [Second AdminDecision : Duration], [Third AdminDecision : Duration], [Fourth AdminDecision : Duration], [Fifth AdminDecision : Duration], [Unknown AdminDecision])
       ) P
) DA
LEFT JOIN
(
       SELECT DADI.IncidentIdentifier
       , DADI.SchoolId
       , DADI.StudentUSI
       , CLD.DisciplineIncidentLunchDetention
       , ASD.DisciplineIncidentAfterSchoolDetention
       FROM 
       (
             SELECT DADI.IncidentIdentifier
             , DADI.SchoolId
             , DADI.StudentUSI
             FROM edfi.DisciplineActionStudentDisciplineIncidentAssociation DADI
       ) DADI
       LEFT JOIN 
       (
             SELECT DADI.IncidentIdentifier
             , DADI.SchoolId
             , DADI.StudentUSI
             , DAX.DisciplineIncidentLunchDetention
             FROM edfi.DisciplineAction DA
             JOIN mi.DisciplineActionExtension DAX
                    ON DA.DisciplineActionIdentifier = DAX.DisciplineActionIdentifier
                    AND DA.DisciplineDate = DAX.DisciplineDate
                    AND DA.StudentUSI = DAX.StudentUSI
             JOIN edfi.DisciplineActionStudentDisciplineIncidentAssociation DADI
                    ON DA.DisciplineActionIdentifier = DADI.DisciplineActionIdentifier
                    AND DA.DisciplineDate = DADI.DisciplineDate
                    AND DA.StudentUSI = DADI.StudentUSI
             WHERE DAX.DisciplineIncidentLunchDetention IS NOT NULL
       ) CLD 
             ON CLD.IncidentIdentifier = DADI.IncidentIdentifier
             AND CLD.SchoolId = DADI.SchoolId
             AND CLD.StudentUSI = DADI.StudentUSI 
       LEFT JOIN 
       (
             SELECT DADI.IncidentIdentifier
             , DADI.SchoolId
             , DADI.StudentUSI
             , DAX.DisciplineIncidentAfterSchoolDetention
             FROM edfi.DisciplineAction DA
             JOIN mi.DisciplineActionExtension DAX
                    ON DA.DisciplineActionIdentifier = DAX.DisciplineActionIdentifier
                    AND DA.DisciplineDate = DAX.DisciplineDate
                    AND DA.StudentUSI = DAX.StudentUSI
             JOIN edfi.DisciplineActionStudentDisciplineIncidentAssociation DADI
                    ON DA.DisciplineActionIdentifier = DADI.DisciplineActionIdentifier
                    AND DA.DisciplineDate = DADI.DisciplineDate
                    AND DA.StudentUSI = DADI.StudentUSI
             WHERE DAX.DisciplineIncidentAfterSchoolDetention IS NOT NULL
       ) ASD 
             ON ASD.IncidentIdentifier = DADI.IncidentIdentifier
             AND ASD.SchoolId = DADI.SchoolId
             AND ASD.StudentUSI = DADI.StudentUSI 
       GROUP BY DADI.IncidentIdentifier
             , DADI.SchoolId
             , DADI.StudentUSI
             , CLD.DisciplineIncidentLunchDetention
             , ASD.DisciplineIncidentAfterSchoolDetention
) DAC
       ON DA.IncidentIdentifier = DAC.IncidentIdentifier
       AND DA.SchoolId = DAC.SchoolId
       AND DA.StudentUSI = DAC.StudentUSI 
JOIN
(
       SELECT DI.IncidentIdentifier
       , DI.SchoolId
       , DI.IncidentDate 
       , DI.IncidentTime
       , EO.EducationOrganizationId 
       , STF.StaffUniqueId 
       , STF.FirstName AS EducatorFirstName
       , STF.LastSurname AS EducatorLastName
       , IL.ShortDescription AS LocationId
       , DIPMD.MotivationId
       , DIOI.OthersInvolvedId
       , DISR.SeclusionRestraintId
       , DI.IncidentDescription AS OtherInfo
       , DIX.DisciplineIncidentOutsideIntervention
       , DIX.DisciplineIncidentReportIncident
       , DIX.DisciplineIncidentTechnologyViolation
       , DIX.DisciplineIncidentProblemBehavior
       FROM edfi.DisciplineIncident DI
       JOIN mi.DisciplineIncidentExtension DIX
             ON DI.IncidentIdentifier = DIX.IncidentIdentifier
             AND DI.SchoolId = DIX.SchoolId
       JOIN edfi.School S
             ON DI.SchoolId = S.SchoolId
       JOIN edfi.LocalEducationAgency LEA
             ON S.LocalEducationAgencyId = LEA.LocalEducationAgencyId
       JOIN edfi.EducationOrganization EO
             ON LEA.LocalEducationAgencyId = EO.EducationOrganizationId
       LEFT JOIN edfi.Staff STF
             ON DI.StaffUSI = STF.StaffUSI
       LEFT JOIN edfi.Descriptor IL
             ON DI.IncidentLocationDescriptorId = IL.DescriptorId
       LEFT JOIN 
       (
             SELECT D.CodeValue AS MotivationId
             , DIPMD.DisciplineIncidentPerceivedMotivationDescriptorId
             FROM mi.DisciplineIncidentPerceivedMotivationDescriptor DIPMD
             JOIN edfi.Descriptor D
                    ON DIPMD.DisciplineIncidentPerceivedMotivationDescriptorId = D.DescriptorId
       ) DIPMD
             ON DIPMD.DisciplineIncidentPerceivedMotivationDescriptorId = DIX.DisciplineIncidentPerceivedMotivationDescriptorId
       LEFT JOIN 
       (
             SELECT D.CodeValue AS OthersInvolvedId
             , DIOI.DisciplineIncidentOthersInvolvedDescriptorId
             FROM mi.DisciplineIncidentOthersInvolvedDescriptor DIOI
             JOIN edfi.Descriptor D
                    ON DIOI.DisciplineIncidentOthersInvolvedDescriptorId = D.DescriptorId
       ) DIOI
             ON DIOI.DisciplineIncidentOthersInvolvedDescriptorId = DIX.DisciplineIncidentOthersInvolvedDescriptorId
       LEFT JOIN 
       (
             SELECT D.CodeValue AS SeclusionRestraintId
             , DISR.DisciplineIncidentSeclusionRestraintDescriptorId
             FROM mi.DisciplineIncidentSeclusionRestraintDescriptor DISR
             JOIN edfi.Descriptor D
                    ON DISR.DisciplineIncidentSeclusionRestraintDescriptorId = D.DescriptorId
       ) DISR
             ON DISR.DisciplineIncidentSeclusionRestraintDescriptorId = DIX.DisciplineIncidentSeclusionRestraintDescriptorId
) DI
       ON DA.IncidentIdentifier = DI.IncidentIdentifier
       AND DA.SchoolId = DI.SchoolId
JOIN 
(
       SELECT P.IncidentIdentifier 
       , P.SchoolId
       , P.StudentUSI
       , P.StudentSwisId
       , P.StudentFirstName
       , P.StudentLastSurname
       , P.[First ProblemBehavior : ProblemBehaviorSubType]
       , P.[Second ProblemBehavior : ProblemBehaviorSubType]
       , P.[Third ProblemBehavior : ProblemBehaviorSubType]
       , P.[Fourth ProblemBehavior : ProblemBehaviorSubType]
       , P.[Fifth ProblemBehavior : ProblemBehaviorSubType]
       , P.[Unknown ProblemBehavior]
       , P.DisciplineIncidentOutsideIntervention
       , P.DisciplineIncidentReportIncident
       , P.DisciplineIncidentTechnologyViolation
       , P.DisciplineIncidentProblemBehavior
       FROM
       (
             SELECT I.StudentUSI
             , I.IncidentIdentifier
             , I.SchoolId
             , I.StudentSwisId
             , I.StudentFirstName
             , I.StudentLastSurname
             , I.MotivationId
             , I.OthersInvolvedId
             , I.SeclusionRestraintId
             , I.SwisProblemBehaviorPlusSubType
             , CASE
                    WHEN (I.OrderOfBehavior = 1) THEN 'First ProblemBehavior : ProblemBehaviorSubType'
                    WHEN (I.OrderOfBehavior = 2) THEN 'Second ProblemBehavior : ProblemBehaviorSubType'
                    WHEN (I.OrderOfBehavior = 3) THEN 'Third ProblemBehavior : ProblemBehaviorSubType'
                    WHEN (I.OrderOfBehavior = 4) THEN 'Fourth ProblemBehavior : ProblemBehaviorSubType'
                    WHEN (I.OrderOfBehavior = 5) THEN 'Fifth ProblemBehavior : ProblemBehaviorSubType'
                    ELSE 'Unknown ProblemBehavior'
               END AS "OrderName"
             , I.DisciplineIncidentOutsideIntervention
             , I.DisciplineIncidentReportIncident
             , I.DisciplineIncidentTechnologyViolation
             , I.DisciplineIncidentProblemBehavior
             FROM
             (
                    SELECT SDIA.StudentUSI
                    , SDIA.IncidentIdentifier
                    , SDIA.SchoolId
                    , S.StudentUniqueId AS StudentSwisId
                    , S.FirstName AS StudentFirstName
                    , S.LastSurname AS StudentLastSurname
                    , DIPMD.MotivationId
                    , DIOI.OthersInvolvedId
                    , DISR.SeclusionRestraintId
                    , B.SwisProblemBehaviorPlusSubType
                    , B.OrderOfBehavior
                    , SDIAX.DisciplineIncidentOutsideIntervention
                    , SDIAX.DisciplineIncidentReportIncident
                    , SDIAX.DisciplineIncidentTechnologyViolation
                    , SDIAX.DisciplineIncidentProblemBehavior
                    FROM edfi.StudentDisciplineIncidentAssociation SDIA
                    JOIN edfi.Student S
                          ON SDIA.StudentUSI = S.StudentUSI
                    JOIN mi.StudentDisciplineIncidentAssociationExtension SDIAX
                           ON SDIA.StudentUSI = SDIAX.StudentUSI
                           AND SDIA.SchoolId = SDIAX.SchoolId
                           AND SDIA.IncidentIdentifier = SDIAX.IncidentIdentifier
                    LEFT JOIN 
                    (
                           SELECT D.CodeValue AS MotivationId
                           , DIPMD.DisciplineIncidentPerceivedMotivationDescriptorId
                           FROM mi.DisciplineIncidentPerceivedMotivationDescriptor DIPMD
                           JOIN edfi.Descriptor D
                                 ON DIPMD.DisciplineIncidentPerceivedMotivationDescriptorId = D.DescriptorId
                    ) DIPMD
                           ON DIPMD.DisciplineIncidentPerceivedMotivationDescriptorId = SDIAX.DisciplineIncidentPerceivedMotivationDescriptorId
                    LEFT JOIN 
                    (
                           SELECT D.CodeValue AS OthersInvolvedId
                           , DIOI.DisciplineIncidentOthersInvolvedDescriptorId
                           FROM mi.DisciplineIncidentOthersInvolvedDescriptor DIOI
                           JOIN edfi.Descriptor D
                                 ON DIOI.DisciplineIncidentOthersInvolvedDescriptorId = D.DescriptorId
                    ) DIOI
                           ON DIOI.DisciplineIncidentOthersInvolvedDescriptorId = SDIAX.DisciplineIncidentOthersInvolvedDescriptorId
                    LEFT JOIN 
                    (
                           SELECT D.CodeValue AS SeclusionRestraintId
                           , DISR.DisciplineIncidentSeclusionRestraintDescriptorId
                           FROM mi.DisciplineIncidentSeclusionRestraintDescriptor DISR
                           JOIN edfi.Descriptor D
                                 ON DISR.DisciplineIncidentSeclusionRestraintDescriptorId = D.DescriptorId
                    ) DISR
                           ON DISR.DisciplineIncidentSeclusionRestraintDescriptorId = SDIAX.DisciplineIncidentSeclusionRestraintDescriptorId
                    LEFT JOIN 
                    (
                           SELECT SDIASB.IncidentIdentifier
                           , SDIASB.SchoolId
                           , SDIASB.StudentUSI
                           , BD.SwisProblemBehaviorId
                           , SDIASB.OrderOfBehavior
                           , BST.SwisProblemBehaviorSubTypeIdentifier
                           , CONCAT(BD.SwisProblemBehaviorId, ' - ', BD.SwisProblemBehavior, ' : ', COALESCE(BST.SwisProblemBehaviorSubTypeIdentifier, 'NULL')) AS "SwisProblemBehaviorPlusSubType"
                           FROM mi.StudentDisciplineIncidentAssociationBehaviorExtension SDIASB
                           JOIN 
                           (
                                 SELECT BD.BehaviorDescriptorId
                                 , D.CodeValue AS SwisProblemBehaviorId
                                 , D.ShortDescription AS SwisProblemBehavior
                                 FROM edfi.BehaviorDescriptor BD
                                 JOIN edfi.Descriptor D
                                        ON BD.BehaviorDescriptorId = D.DescriptorId
                           ) BD
                                 ON SDIASB.BehaviorDescriptorId = BD.BehaviorDescriptorId
                           LEFT JOIN
                           (
                                 SELECT DIBT.DisciplineIncidentBehaviorSubTypeDescriptorId
                                 , D.CodeValue AS SwisProblemBehaviorSubTypeIdentifier
                                 FROM mi.DisciplineIncidentBehaviorSubTypeDescriptor DIBT
                                 JOIN edfi.Descriptor D
                                        ON DIBT.DisciplineIncidentBehaviorSubTypeDescriptorId = D.DescriptorId
                           ) BST
                                 ON SDIASB.DisciplineIncidentBehaviorSubTypeDescriptorId = BST.DisciplineIncidentBehaviorSubTypeDescriptorId
                    ) B
                           ON SDIA.IncidentIdentifier = B.IncidentIdentifier
                           AND SDIA.SchoolId = B.SchoolId
                           AND SDIA.StudentUSI = B.StudentUSI
             ) I
       ) D
       PIVOT
       (
       MAX(SwisProblemBehaviorPlusSubType)
       FOR OrderName IN ([First ProblemBehavior : ProblemBehaviorSubType], [Second ProblemBehavior : ProblemBehaviorSubType], [Third ProblemBehavior : ProblemBehaviorSubType], [Fourth ProblemBehavior : ProblemBehaviorSubType], [Fifth ProblemBehavior : ProblemBehaviorSubType], [Unknown ProblemBehavior])
       ) P
) SB
       ON     DA.IncidentIdentifier = SB.IncidentIdentifier
       AND DA.SchoolId = SB.SchoolId

ORDER BY SB.StudentLastSurname
, SB.StudentFirstName
, DI.IncidentDate ;