namespace TMSDAL.Query.TrainingPlanDashboard { public static class TrainingPlanDashboardQB { // ── 1. SUMMARY KPIs ────────────────────────────────────────────────── public const string GET_TRAINING_PLAN_SUMMARY = @" SELECT (SELECT COUNT(1) FROM DBO.TTRAININGNEED WHERE TENANTID = @TenantId) AS TotalTrainingNeeds, (SELECT COUNT(1) FROM DBO.TTRAININGINSTANCE WHERE TENANTID = @TenantId) AS TotalInstances, (SELECT COUNT(1) FROM DBO.TTRAININGENROLLMENT WHERE TENANTID = @TenantId) AS TotalEnrollments, (SELECT COUNT(1) FROM DBO.TTRAININGCOMPLETION WHERE TENANTID = @TenantId AND COMPLETIONSTATUS = 0 AND COMPLETEDON >= DATEFROMPARTS(YEAR(GETDATE()),1,1) AND COMPLETEDON <= DATEFROMPARTS(YEAR(GETDATE()),12,31)) AS CompletedThisYear, ISNULL(CAST( (SELECT COUNT(1) FROM DBO.TTRAININGCOMPLETION WHERE TENANTID = @TenantId AND COMPLETIONSTATUS = 0) * 100.0 / NULLIF((SELECT COUNT(1) FROM DBO.TTRAININGCOMPLETION WHERE TENANTID = @TenantId), 0) AS DECIMAL(5,2)), 0) AS OverallCompletionRate, ISNULL(CAST( (SELECT COUNT(1) FROM DBO.TTRAININGCOMPLETION WHERE TENANTID = @TenantId AND ISPASSED = 1) * 100.0 / NULLIF((SELECT COUNT(1) FROM DBO.TTRAININGCOMPLETION WHERE TENANTID = @TenantId AND COMPLETIONSTATUS IN (0,2)), 0) AS DECIMAL(5,2)), 0) AS OverallPassRate, (SELECT COUNT(1) FROM DBO.TTRAININGCERTIFICATE WHERE TENANTID = @TenantId AND ISSUEDON >= DATEFROMPARTS(YEAR(GETDATE()),1,1) AND ISSUEDON <= DATEFROMPARTS(YEAR(GETDATE()),12,31)) AS CertificatesIssuedThisYear, (SELECT COUNT(1) FROM DBO.TTRAININGCERTIFICATE WHERE TENANTID = @TenantId AND CERTSTATUS = 0 AND EXPIRYDATE IS NOT NULL AND EXPIRYDATE >= CAST(GETDATE() AS DATE) AND EXPIRYDATE <= DATEADD(DAY, 30, GETDATE())) AS ExpiringCertificates30Days, (SELECT COUNT(1) FROM DBO.TTRAININGENROLLMENT WHERE TENANTID = @TenantId AND ENROLLMENTSTATUS = 0) AS PendingNominations, (SELECT COUNT(1) FROM DBO.TTRAININGENROLLMENT WHERE TENANTID = @TenantId AND ENROLLMENTSTATUS = 1) AS PendingHRApprovals, (SELECT COUNT(1) FROM DBO.MTRAININGDOMAIN WHERE TENANTID = @TenantId) AS TotalDomains, (SELECT COUNT(1) FROM DBO.MTRAINERPROFILE WHERE TENANTID = @TenantId) AS TotalTrainers, (SELECT COUNT(1) FROM DBO.MTRAININGPROGRAMME WHERE TENANTID = @TenantId) AS TotalProgrammes, (SELECT COUNT(DISTINCT EMPLOYEEID) FROM DBO.TTRAININGCOMPLETION WHERE TENANTID = @TenantId AND COMPLETIONSTATUS = 0) AS TotalEmployeesTrained;"; // ── 2. ENROLLMENT FUNNEL ───────────────────────────────────────────── public const string GET_ENROLLMENT_FUNNEL = @" SELECT SUM(CASE WHEN ENROLLMENTSTATUS = 0 THEN 1 ELSE 0 END) AS Nominated, SUM(CASE WHEN ENROLLMENTSTATUS = 1 THEN 1 ELSE 0 END) AS ManagerApproved, SUM(CASE WHEN ENROLLMENTSTATUS = 2 THEN 1 ELSE 0 END) AS HRApproved, SUM(CASE WHEN ENROLLMENTSTATUS = 3 THEN 1 ELSE 0 END) AS Waitlisted, SUM(CASE WHEN ENROLLMENTSTATUS = 4 THEN 1 ELSE 0 END) AS Enrolled, SUM(CASE WHEN ENROLLMENTSTATUS = 5 THEN 1 ELSE 0 END) AS Completed, SUM(CASE WHEN ENROLLMENTSTATUS = 6 THEN 1 ELSE 0 END) AS NoShow, SUM(CASE WHEN ENROLLMENTSTATUS = 7 THEN 1 ELSE 0 END) AS Withdrawn, SUM(CASE WHEN ENROLLMENTSTATUS = 8 THEN 1 ELSE 0 END) AS Rejected, COUNT(1) AS Total FROM DBO.TTRAININGENROLLMENT WHERE TENANTID = @TenantId;"; // ── 3. ALL INSTANCES (full detail) ─────────────────────────────────── public const string GET_ACTIVE_INSTANCES = @" SELECT TI.INSTANCEID AS InstanceId, TI.INSTANCECODE AS InstanceCode, TI.INSTANCETITLE AS InstanceTitle, TI.PROGRAMMEID AS ProgrammeId, MP.PROGRAMMECODE AS ProgrammeCode, MP.PROGRAMMETITLE AS ProgrammeTitle, MP.PROGRAMMETYPE AS ProgrammeType, ISNULL(TI.TRAININGDOMAINID, 0) AS TrainingDomainId, ISNULL(MD.DOMAINNAME, '') AS DomainName, TI.PLANNEDSTARTDATE AS PlannedStartDate, TI.PLANNEDENDDATE AS PlannedEndDate, TI.ACTUALSTARTDATE AS ActualStartDate, TI.ACTUALENDDATE AS ActualEndDate, TI.INSTANCESTATUS AS InstanceStatus, TI.ENROLLEDCOUNT AS EnrolledCount, TI.MAXCAPACITY AS MaxCapacity, TI.MINCAPACITY AS MinCapacity, TI.COSTPERPARTICIPANT AS CostPerParticipant, TI.TRAINERTYPE AS TrainerType, ISNULL(MTP.TRAINERNAME, '') AS TrainerName, ISNULL(MV.VENUENAME, '') AS VenueName, ISNULL(MV.VENUELOCATION, '') AS VenueLocation, (SELECT COUNT(1) FROM DBO.TINSTANCESESSION S WHERE S.INSTANCEID = TI.INSTANCEID) AS TotalSessions, (SELECT COUNT(1) FROM DBO.TINSTANCESESSION S WHERE S.INSTANCEID = TI.INSTANCEID AND S.SESSIONSTATUS = 2) AS CompletedSessions, ISNULL(TI.BATCHNOTE, '') AS BatchNote FROM DBO.TTRAININGINSTANCE TI INNER JOIN DBO.MTRAININGPROGRAMME MP ON MP.PROGRAMMEID = TI.PROGRAMMEID AND MP.TENANTID = TI.TENANTID LEFT JOIN DBO.MTRAININGDOMAIN MD ON MD.TRAININGDOMAINID = TI.TRAININGDOMAINID AND MD.TENANTID = TI.TENANTID LEFT JOIN DBO.MTRAINERPROFILE MTP ON MTP.TRAINERID = TI.PRIMARYTRAINERID AND MTP.TENANTID = TI.TENANTID LEFT JOIN DBO.MTRAININGVENUE MV ON MV.TRAININGVENUEID = TI.VENUEID AND MV.TENANTID = TI.TENANTID WHERE TI.TENANTID = @TenantId ORDER BY TI.PLANNEDSTARTDATE DESC;"; // ── 4. DOMAIN-WISE SUMMARY ─────────────────────────────────────────── public const string GET_DOMAIN_COMPLETION = @" SELECT MD.TRAININGDOMAINID AS TrainingDomainId, MD.DOMAINCODE AS DomainCode, MD.DOMAINNAME AS DomainName, COUNT(DISTINCT TI.INSTANCEID) AS TotalInstances, COUNT(DISTINCT TE.ENROLLMENTID) AS TotalEnrollments, COUNT(DISTINCT TE.EMPLOYEEID) AS TotalEmployees, SUM(CASE WHEN TC.COMPLETIONSTATUS = 0 THEN 1 ELSE 0 END) AS Completed, SUM(CASE WHEN TC.COMPLETIONSTATUS = 2 THEN 1 ELSE 0 END) AS Failed, SUM(CASE WHEN TC.COMPLETIONSTATUS = 1 THEN 1 ELSE 0 END) AS InProgress, SUM(CASE WHEN TC.COMPLETIONSTATUS = 4 THEN 1 ELSE 0 END) AS Withdrawn, ISNULL(CAST( SUM(CASE WHEN TC.COMPLETIONSTATUS = 0 THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(TC.TRAININGCOMPLETIONID), 0) AS DECIMAL(5,2)), 0) AS CompletionRate, ISNULL(CAST( SUM(CASE WHEN TC.ISPASSED = 1 THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(TC.TRAININGCOMPLETIONID), 0) AS DECIMAL(5,2)), 0) AS PassRate, ISNULL(AVG(CASE WHEN TC.POSTASSESSMENTSCORE > 0 THEN TC.POSTASSESSMENTSCORE END), 0) AS AvgPostAssessmentScore, ISNULL(AVG(CASE WHEN TC.OVERALLATTENDANCEPCT > 0 THEN TC.OVERALLATTENDANCEPCT END), 0) AS AvgAttendancePct, COUNT(DISTINCT CERT.TRAININGCERTIFICATEID) AS CertificatesIssued FROM DBO.MTRAININGDOMAIN MD LEFT JOIN DBO.TTRAININGINSTANCE TI ON TI.TRAININGDOMAINID = MD.TRAININGDOMAINID AND TI.TENANTID = MD.TENANTID LEFT JOIN DBO.TTRAININGENROLLMENT TE ON TE.INSTANCEID = TI.INSTANCEID AND TE.TENANTID = MD.TENANTID LEFT JOIN DBO.TTRAININGCOMPLETION TC ON TC.ENROLLMENTID = TE.ENROLLMENTID AND TC.TENANTID = MD.TENANTID LEFT JOIN DBO.TTRAININGCERTIFICATE CERT ON CERT.ENROLLMENTID = TE.ENROLLMENTID AND CERT.TENANTID = MD.TENANTID WHERE MD.TENANTID = @TenantId GROUP BY MD.TRAININGDOMAINID, MD.DOMAINCODE, MD.DOMAINNAME ORDER BY TotalEnrollments DESC;"; // ── 5. DOMAIN EMPLOYEE DRILL-DOWN ──────────────────────────────────── public const string GET_DOMAIN_EMPLOYEES = @" SELECT ME.EMPLOYEEID AS EmployeeId, ME.EMPLOYEECODE AS EmployeeCode, ME.EMPLOYEENAME AS EmployeeName, TE.ENROLLMENTID AS EnrollmentId, TE.ENROLLMENTCODE AS EnrollmentCode, TE.ENROLLMENTSTATUS AS EnrollmentStatus, TE.NOMINATIONSOURCE AS NominationSource, TE.NOMINATEDON AS NominatedOn, TI.INSTANCEID AS InstanceId, TI.INSTANCECODE AS InstanceCode, TI.INSTANCETITLE AS InstanceTitle, MP.PROGRAMMEID AS ProgrammeId, MP.PROGRAMMETITLE AS ProgrammeTitle, MP.PROGRAMMECODE AS ProgrammeCode, TI.PLANNEDSTARTDATE AS PlannedStartDate, TI.PLANNEDENDDATE AS PlannedEndDate, TC.COMPLETIONSTATUS AS CompletionStatus, TC.COMPLETEDON AS CompletedOn, TC.OVERALLATTENDANCEPCT AS OverallAttendancePct, TC.PREASSESSMENTSCORE AS PreAssessmentScore, TC.POSTASSESSMENTSCORE AS PostAssessmentScore, TC.SCOREIMPROVEMENT AS ScoreImprovement, TC.ISPASSED AS IsPassed, TC.CERTIFICATEISSUED AS CertificateIssued, CERT.CERTIFICATENUMBER AS CertificateNumber, CERT.CERTIFICATETITLE AS CertificateTitle, CERT.ISSUEDON AS CertificateIssuedOn, CERT.EXPIRYDATE AS CertificateExpiryDate, CERT.CERTSTATUS AS CertStatus, (SELECT TOP 1 AA.SCOREPCT FROM DBO.TASSESSMENTATTEMPT AA WHERE AA.ENROLLMENTID = TE.ENROLLMENTID ORDER BY AA.COMPLETEDON DESC) AS LatestAssessmentScore, (SELECT COUNT(1) FROM DBO.TASSESSMENTATTEMPT AA2 WHERE AA2.ENROLLMENTID = TE.ENROLLMENTID) AS AssessmentAttempts, ISNULL(MTP.TRAINERNAME, '') AS TrainerName, ISNULL(MV.VENUENAME, '') AS VenueName FROM DBO.TTRAININGENROLLMENT TE INNER JOIN DBO.MEMPLOYEE ME ON ME.EMPLOYEEID = TE.EMPLOYEEID INNER JOIN DBO.TTRAININGINSTANCE TI ON TI.INSTANCEID = TE.INSTANCEID AND TI.TENANTID = TE.TENANTID INNER JOIN DBO.MTRAININGPROGRAMME MP ON MP.PROGRAMMEID = TI.PROGRAMMEID AND MP.TENANTID = TE.TENANTID LEFT JOIN DBO.TTRAININGCOMPLETION TC ON TC.ENROLLMENTID = TE.ENROLLMENTID AND TC.TENANTID = TE.TENANTID LEFT JOIN DBO.TTRAININGCERTIFICATE CERT ON CERT.ENROLLMENTID = TE.ENROLLMENTID AND CERT.TENANTID = TE.TENANTID LEFT JOIN DBO.MTRAINERPROFILE MTP ON MTP.TRAINERID = TI.PRIMARYTRAINERID AND MTP.TENANTID = TE.TENANTID LEFT JOIN DBO.MTRAININGVENUE MV ON MV.TRAININGVENUEID = TI.VENUEID AND MV.TENANTID = TE.TENANTID WHERE TE.TENANTID = @TenantId AND TI.TRAININGDOMAINID = @TrainingDomainId ORDER BY ME.EMPLOYEENAME, TE.NOMINATEDON DESC;"; // ── 6. TRAINER WORKLOAD ─────────────────────────────────────────────── public const string GET_TRAINER_WORKLOAD = @" SELECT MTP.TRAINERID AS TrainerId, MTP.TRAINERCODE AS TrainerCode, MTP.TRAINERNAME AS TrainerName, MTP.TRAINERTYPE AS TrainerType, COUNT(DISTINCT TI.INSTANCEID) AS AssignedInstances, SUM(CASE WHEN S.SESSIONSTATUS = 0 AND S.SCHEDULEDDATE >= CAST(GETDATE() AS DATE) THEN 1 ELSE 0 END) AS UpcomingSessions, SUM(CASE WHEN S.SESSIONSTATUS = 2 AND S.SCHEDULEDDATE >= DATEFROMPARTS(YEAR(GETDATE()),1,1) THEN 1 ELSE 0 END) AS CompletedSessionsThisYear, COUNT(DISTINCT TE.ENROLLMENTID) AS TotalEnrollees, COUNT(DISTINCT CASE WHEN TC.COMPLETIONSTATUS = 0 THEN TC.TRAININGCOMPLETIONID END) AS CompletedEnrollees, ISNULL(AVG(ER.OVERALLRATING), 0) AS AvgRating FROM DBO.MTRAINERPROFILE MTP LEFT JOIN DBO.TTRAININGINSTANCE TI ON TI.PRIMARYTRAINERID = MTP.TRAINERID AND TI.TENANTID = MTP.TENANTID LEFT JOIN DBO.TINSTANCESESSION S ON S.INSTANCEID = TI.INSTANCEID AND S.TRAINERID = MTP.TRAINERID LEFT JOIN DBO.TTRAININGENROLLMENT TE ON TE.INSTANCEID = TI.INSTANCEID AND TE.TENANTID = MTP.TENANTID LEFT JOIN DBO.TTRAININGCOMPLETION TC ON TC.ENROLLMENTID = TE.ENROLLMENTID AND TC.TENANTID = MTP.TENANTID LEFT JOIN DBO.TEVALUATIONINSTANCE EI ON EI.ENROLLMENTID = TE.ENROLLMENTID AND EI.TENANTID = MTP.TENANTID AND EI.EVALSTATUS = 2 LEFT JOIN DBO.TEVALUATIONRESPONSE ER ON ER.EVALUATIONINSTANCEID = EI.EVALUATIONINSTANCEID AND ER.TENANTID = MTP.TENANTID WHERE MTP.TENANTID = @TenantId GROUP BY MTP.TRAINERID, MTP.TRAINERCODE, MTP.TRAINERNAME, MTP.TRAINERTYPE ORDER BY AssignedInstances DESC;"; // ── 7. EXPIRING CERTIFICATES (next 90 days) ────────────────────────── public const string GET_EXPIRING_CERTIFICATES = @" SELECT TC.TRAININGCERTIFICATEID AS TrainingCertificateId, TC.CERTIFICATENUMBER AS CertificateNumber, TC.CERTIFICATETITLE AS CertificateTitle, MP.PROGRAMMETITLE AS ProgrammeTitle, TC.EMPLOYEEID AS EmployeeId, ME.EMPLOYEECODE AS EmployeeCode, ME.EMPLOYEENAME AS EmployeeName, TC.ISSUEDON AS IssuedOn, TC.EXPIRYDATE AS ExpiryDate, DATEDIFF(DAY, GETDATE(), TC.EXPIRYDATE) AS DaysToExpiry, TC.RENEWALWINDOWDAYS AS RenewalWindowDays, TC.CERTSTATUS AS CertStatus, TC.CERTIFICATEURL AS CertificateUrl FROM DBO.TTRAININGCERTIFICATE TC INNER JOIN DBO.MTRAININGPROGRAMME MP ON MP.PROGRAMMEID = TC.PROGRAMMEID AND MP.TENANTID = TC.TENANTID INNER JOIN DBO.MEMPLOYEE ME ON ME.EMPLOYEEID = TC.EMPLOYEEID WHERE TC.TENANTID = @TenantId AND TC.CERTSTATUS = 0 AND TC.EXPIRYDATE IS NOT NULL AND TC.EXPIRYDATE >= CAST(GETDATE() AS DATE) AND TC.EXPIRYDATE <= DATEADD(DAY, 90, GETDATE()) ORDER BY TC.EXPIRYDATE;"; // ── 8. TRAINING NEEDS PIPELINE ─────────────────────────────────────── public const string GET_TRAINING_NEEDS = @" SELECT TN.TRAININGNEEDID AS TrainingNeedId, TN.NEEDCODE AS NeedCode, TN.NEEDTITLE AS NeedTitle, TN.DESCRIPTION AS Description, TN.TRAININGDOMAINID AS TrainingDomainId, ISNULL(MD.DOMAINNAME, '') AS DomainName, TN.NEEDSOURCETYPE AS NeedSourceType, TN.PRIORITY AS Priority, TN.AUDIENCETYPE AS AudienceType, TN.ISMANDATORY AS IsMandatory, TN.NEEDSTATUS AS NeedStatus, TN.TARGETHEADCOUNT AS TargetHeadcount, TN.COMPLIANCEDEADLINE AS ComplianceDeadline, TN.RAISEDON AS RaisedOn, ISNULL(RB.EMPLOYEENAME, '') AS RaisedByName, ISNULL(DEPT.DEPARTMENTNAME, '') AS DepartmentName, (SELECT COUNT(1) FROM DBO.TTRAININGINSTANCE TI WHERE TI.TRAININGNEEDID = TN.TRAININGNEEDID AND TI.TENANTID = TN.TENANTID) AS LinkedInstances FROM DBO.TTRAININGNEED TN LEFT JOIN DBO.MTRAININGDOMAIN MD ON MD.TRAININGDOMAINID = TN.TRAININGDOMAINID AND MD.TENANTID = TN.TENANTID LEFT JOIN DBO.MEMPLOYEE RB ON RB.EMPLOYEEID = TN.RAISEDBYID LEFT JOIN DBO.MDEPARTMENT DEPT ON DEPT.DEPARTMENTID = TN.DEPARTMENTID WHERE TN.TENANTID = @TenantId ORDER BY TN.PRIORITY, TN.RAISEDON DESC;"; // ── 9. GANTT TIMELINE — employee × instance bars ───────────────────── // Returns one flat row per employee+instance combination. // DAL groups these into GanttRowDTO (one row per employee, list of bars). public const string GET_GANTT_TIMELINE = @" SELECT ME.EMPLOYEEID AS EmployeeId, ME.EMPLOYEECODE AS EmployeeCode, ME.EMPLOYEENAME AS EmployeeName, ME.THUMBNAIL AS EmployeeThumbnail, TI.INSTANCEID AS InstanceId, TI.INSTANCECODE AS InstanceCode, MP.PROGRAMMETITLE AS ProgrammeTitle, MP.PROGRAMMETYPE AS ProgrammeType, TI.PLANNEDSTARTDATE AS PlannedStartDate, TI.PLANNEDENDDATE AS PlannedEndDate, TI.ACTUALSTARTDATE AS ActualStartDate, TI.ACTUALENDDATE AS ActualEndDate, DATEDIFF(DAY, TI.PLANNEDSTARTDATE, TI.PLANNEDENDDATE) AS PlannedDurationDays, CASE WHEN TI.ACTUALSTARTDATE IS NOT NULL AND TI.ACTUALENDDATE IS NOT NULL THEN DATEDIFF(DAY, TI.ACTUALSTARTDATE, TI.ACTUALENDDATE) ELSE NULL END AS ActualDurationDays, TE.ENROLLMENTSTATUS AS EnrollmentStatus, TC.COMPLETIONSTATUS AS CompletionStatus, CASE WHEN TI.ACTUALENDDATE IS NOT NULL AND TI.ACTUALENDDATE > TI.PLANNEDENDDATE THEN 1 ELSE 0 END AS IsDelayed, CASE WHEN MP.PROGRAMMETYPE = 13 THEN 1 ELSE 0 END AS IsRetraining FROM DBO.TTRAININGENROLLMENT TE INNER JOIN DBO.MEMPLOYEE ME ON ME.EMPLOYEEID = TE.EMPLOYEEID INNER JOIN DBO.TTRAININGINSTANCE TI ON TI.INSTANCEID = TE.INSTANCEID AND TI.TENANTID = TE.TENANTID INNER JOIN DBO.MTRAININGPROGRAMME MP ON MP.PROGRAMMEID = TI.PROGRAMMEID AND MP.TENANTID = TE.TENANTID LEFT JOIN DBO.TTRAININGCOMPLETION TC ON TC.ENROLLMENTID = TE.ENROLLMENTID AND TC.TENANTID = TE.TENANTID WHERE TE.TENANTID = @TenantId AND (@TrainingDomainId = 0 OR TI.TRAININGDOMAINID = @TrainingDomainId) ORDER BY ME.EMPLOYEENAME, TI.PLANNEDSTARTDATE;"; // ── 10b. PLAN HEADER — dept name + last updation date from DB ──────── // UpdationResponsibility and ReviewFrequency come from the caller (query params). // Department: from the most-active dept in TTRAININGNEED for this tenant/domain. // UpdationDate: latest MODIFIEDON from TTRAININGNEED for this tenant/domain. public const string GET_PLAN_HEADER = @" SELECT TOP 1 TN.DEPARTMENTID AS DepartmentId, ISNULL(DEPT.DEPARTMENTNAME, '') AS DepartmentName, MAX(TN.MODIFIEDON) OVER (PARTITION BY TN.TENANTID) AS UpdationDate, MD.TRAININGDOMAINID AS TrainingDomainId, ISNULL(MD.DOMAINNAME, '') AS DomainName FROM DBO.TTRAININGNEED TN LEFT JOIN DBO.MDEPARTMENT DEPT ON DEPT.DEPARTMENTID = TN.DEPARTMENTID LEFT JOIN DBO.MTRAININGDOMAIN MD ON MD.TRAININGDOMAINID = TN.TRAININGDOMAINID AND MD.TENANTID = TN.TENANTID WHERE TN.TENANTID = @TenantId AND (@TrainingDomainId = 0 OR TN.TRAININGDOMAINID = @TrainingDomainId) ORDER BY TN.MODIFIEDON DESC;"; // ── 10. PROGRAMME PERFORMANCE ───────────────────────────────────────── public const string GET_PROGRAMME_PERFORMANCE = @" SELECT MP.PROGRAMMEID AS ProgrammeId, MP.PROGRAMMECODE AS ProgrammeCode, MP.PROGRAMMETITLE AS ProgrammeTitle, ISNULL(MD.DOMAINNAME, '') AS DomainName, MP.PROGRAMMETYPE AS ProgrammeType, MP.DELIVERYMODE AS DeliveryMode, MP.DURATIONHOURS AS DurationHours, COUNT(DISTINCT TI.INSTANCEID) AS TotalInstances, COUNT(DISTINCT TE.ENROLLMENTID) AS TotalEnrollments, COUNT(DISTINCT TC.TRAININGCOMPLETIONID) AS TotalCompletions, ISNULL(CAST( COUNT(DISTINCT TC.TRAININGCOMPLETIONID) * 100.0 / NULLIF(COUNT(DISTINCT TE.ENROLLMENTID), 0) AS DECIMAL(5,2)), 0) AS CompletionRate, ISNULL(AVG(CASE WHEN TC.POSTASSESSMENTSCORE > 0 THEN TC.POSTASSESSMENTSCORE END), 0) AS AvgPostAssessmentScore, MP.PASSMARK AS PassMark, COUNT(DISTINCT CERT.TRAININGCERTIFICATEID) AS CertificatesIssued FROM DBO.MTRAININGPROGRAMME MP LEFT JOIN DBO.MTRAININGDOMAIN MD ON MD.TRAININGDOMAINID = MP.TRAININGDOMAINID AND MD.TENANTID = MP.TENANTID LEFT JOIN DBO.TTRAININGINSTANCE TI ON TI.PROGRAMMEID = MP.PROGRAMMEID AND TI.TENANTID = MP.TENANTID LEFT JOIN DBO.TTRAININGENROLLMENT TE ON TE.INSTANCEID = TI.INSTANCEID AND TE.TENANTID = MP.TENANTID LEFT JOIN DBO.TTRAININGCOMPLETION TC ON TC.ENROLLMENTID = TE.ENROLLMENTID AND TC.TENANTID = MP.TENANTID LEFT JOIN DBO.TTRAININGCERTIFICATE CERT ON CERT.PROGRAMMEID = MP.PROGRAMMEID AND CERT.TENANTID = MP.TENANTID WHERE MP.TENANTID = @TenantId GROUP BY MP.PROGRAMMEID, MP.PROGRAMMECODE, MP.PROGRAMMETITLE, MD.DOMAINNAME, MP.PROGRAMMETYPE, MP.DELIVERYMODE, MP.DURATIONHOURS, MP.PASSMARK ORDER BY TotalEnrollments DESC;"; } }