RAA

DECLARE @ReportID INT = 3571

DECLARE @NUT INT = (SELECT ISNULL(TR2.NUT,0) FROM Training_Report AS TR2 WHERE TR2.ID = @ReportID)

DECLARE @NumRegByPOPH INT =
ISNULL((SELECT COUNT(*) FROM Training_Registration AS TRG
    INNER JOIN Training_Report AS TR
        ON TR.ID = @ReportID
    WHERE TRG.Training_Session_ID = TR.SessionID
        AND TRG.Registration_Status = 7
        AND ISNULL(TRG.NUT,0) = ISNULL(TR.NUT,0)),0)

DECLARE @NumRegByPOPH2 INT =
ISNULL((SELECT COUNT(*) FROM Training_Registration AS TRG
    INNER JOIN Training_Report AS TR
        ON TR.ID = @ReportID
    WHERE TRG.Training_Session_ID = TR.SessionID
        AND TRG.Registration_Status = 7
        AND ISNULL(TRG.NUT,0) <> ISNULL(TR.NUT,0)),0)

DECLARE @NumPresence INT =
CASE @NUT WHEN 0
THEN
    (SELECT COUNT(*) FROM (SELECT TP.Employee_No FROM Training_Presence TP
        INNER JOIN Training_Registration TRGT ON TP.Training_Registration_ID = TRGT.ID
        INNER JOIN Training_Report AS TR ON TR.ID = @ReportID
        INNER JOIN Training_Sessions TS2 ON TS2.ID = TRGT.Training_Session_ID
    WHERE TS2.ID = TR.SessionID
        AND TP.Presence = 1 AND ISNULL(TRGT.POPH,0) = 0
    GROUP BY TP.Employee_No) AS Temp)

ELSE
    (SELECT COUNT(*) FROM (SELECT TP.Employee_No FROM Training_Presence TP
        INNER JOIN Training_Registration TRGT ON TP.Training_Registration_ID = TRGT.ID
        INNER JOIN Training_Report AS TR ON TR.ID = @ReportID
        INNER JOIN Training_Sessions TS2 ON TS2.ID = TRGT.Training_Session_ID
    WHERE TS2.ID = TR.SessionID
            AND TP.Presence = 1 AND ISNULL(TRGT.POPH,0) = 1
    GROUP BY TP.Employee_No) AS Temp)
END

DECLARE @Num_Entries INT =
CASE @NUT WHEN 0
THEN
    ((SELECT COUNT(TRG.ID)
    FROM Training_Registration TRG
        INNER JOIN Training_Registration_Status TRS ON TRS.ID = TRG.Registration_Status AND TRS.Allow_Registration = 0
        INNER JOIN Training_Report AS TR ON TR.ID = @ReportID
    WHERE TRG.Training_Session_ID = TR.SessionID) -
    @NumRegByPOPH)
ELSE
    @NumRegByPOPH
END

DECLARE @Num_Failed INT =
(SELECT COUNT(*) FROM Training_Registration AS TRG
    INNER JOIN Training_Report AS TR
        ON TR.ID = @ReportID
WHERE TRG.Training_Session_ID = TR.SessionID
    AND TRG.AssortmentCode = 2
    AND ISNULL(TRG.NUT,0) = ISNULL(TR.NUT,0))

DECLARE @Num_Slot INT =
CASE @NUT
WHEN 0
THEN
    ((SELECT TS.Num_Slot FROM Training_Sessions AS TS
        INNER JOIN Training_Report AS TR
            ON TR.ID = @ReportID
        WHERE TS.ID = TR.SessionID) - @NumRegByPOPH2)
ELSE
    @NumRegByPOPH
END

DECLARE @Trainer_Hours INT =
(SELECT ISNULL(SUM(ISNULL(TP.Num_Hours,0)),0) FROM Training_Presence TP
    INNER JOIN Training_Registration TRGT ON TP.Training_Registration_ID = TRGT.ID
    INNER JOIN Training_Report AS TR ON TR.ID = @ReportID
    INNER JOIN Training_Sessions TS2 ON TS2.ID = TRGT.Training_Session_ID
WHERE TS2.ID = TR.SessionID AND ISNULL(TR.NUT,0) = ISNULL(TRGT.NUT,0) AND TP.Presence = 1)

DECLARE @ExecutionRate DECIMAL(9,2) =
((@Trainer_Hours * 100) / ((SELECT ISNULL(SUM(ISNULL(TP.Num_Hours,0)),0) FROM Training_Presence TP
    INNER JOIN Training_Registration TRGT ON TP.Training_Registration_ID = TRGT.ID
    INNER JOIN Training_Sessions TS ON TS.ID = TRGT.Training_Session_ID
    INNER JOIN Training_Report AS TR ON TR.ID = @ReportID
WHERE TS.ID = TR.SessionID AND ISNULL(TRGT.NUT,0) = ISNULL(TR.NUT,0))))

DECLARE @Prevision_Hours INT = (SELECT ISNULL(SUM(ISNULL(TP.Num_Hours,0)),0) FROM Training_Presence TP
    INNER JOIN Training_Registration TRGT ON TP.Training_Registration_ID = TRGT.ID
    INNER JOIN Training_Report AS TR ON TR.ID = @ReportID
    INNER JOIN Training_Sessions TS2 ON TS2.ID = TRGT.Training_Session_ID
WHERE TS2.ID = TR.SessionID AND ISNULL(TR.NUT,0) = ISNULL(TRGT.NUT,0))

SELECT (SELECT CASE TR.NUT WHEN NULL THEN '' ELSE (' - ' + GC_NUT.[Description]) END) AS PlusNameNut, ISNULL(TS.TrainingReference,'') AS TrainingReference, TR.*, TS.Start_Date, ISNULL('Esta ação de formação foi ainda financiada pelo projecto: ' + TS.FinancingProject + '.', '') AS FinancingProject,TS.End_Date, TC.Course_Description, TS.POPH, TS.Training_Plan,
(ISNULL(TS.Num_Hours,0) + ISNULL(TS.Num_WorkHours,0)) AS NumHours, TS.TrainingYear, TS.LegalTrainingYear, TS.Training_Course_Goals,
(SELECT @Num_Slot) AS 'Num_Slot',
(SELECT @Num_Entries) AS 'Num_Entries',
(SELECT @NumPresence) AS 'NumPresences',
(SELECT @Num_Failed) AS 'NumNonPresences',
(SELECT @Trainer_Hours) AS 'TrainerHours',
(SELECT @ExecutionRate) AS 'ExecutionRate',
(SELECT @Prevision_Hours) AS 'PrevisionHours',
TRE.Conclusion
FROM Training_Report TR
LEFT JOIN General_Codes AS GC_NUT ON GC_NUT.Code = TR.NUT AND GC_NUT.[TYPE] = 'NUT2'
INNER JOIN Training_Sessions TS ON TS.ID = TR.SessionID
INNER JOIN Training_Courses TC ON TC.ID = TS.Course_ID
LEFT JOIN Training_ReportEvaluation TRE ON TRE.ReportID = TR.ID

WHERE TR.ID = @ReportID