**count(case when IsNull(G.EmpCode,'X')='X' then null else 1 end) as Fullfilled**

**count(case when A.Selected = 'Y' then 1 else null end) as NoOfGaps**

Eg:

SELECT A.EmpCode

,F.EMPLOYEENAME as EmployeeName

,E.DESCSHORT

,count(case when IsNull(G.EmpCode,'X')='X' then null else 1 end) as Fullfilled

,count(case when IsNull(G.EmpCode,'X')='X' then 1 else null end) as NotFullfilled

,count(case when A.Selected = 'Y' then 1 else null end) as NoOfGaps

,sum(case when A.Status = 'T' then G.TrainingHrs else 0 end) as NoOfHrs

,sum(case when A.Status = 'T' then G.TrainingFees else 0 end) as Costs

,count(case when A.Status = 'A' then 1 else null end) as Attending

from tb_Employee as A

left outer join HR_Competency D on A.CompetencyGroup = D.CompetencyGroup and A.Competency = D.Competency and D.CompanyCode = A.CompanyCode

left outer join HR_EmployeeMaster F on A.CompanyCode = F.CompanyCode and A.EmpCode = F.EmpCode

left outer join HR01_CODEREF E on F.JobGrade = E.Code and E.CodeType = 'JOB' and E.CompanyCode = A.CompanyCode

left outer join TR_EmployeeTraining G on A.CompanyCode = G.CompanyCode and A.EmpCode = G.EmpCode and A.TrainingYear = G.TrainingYear

and A.CompetencyGroup = G.CompetencyGroup and A.Competency = G.Competency and G.Status not in ('S', 'P', 'R', 'C', 'W','Z')

where A.CompanyCode = 'LSH' and rtrim(A.TrainingYear) = year(getdate())

and A.EmpCode IN (SELECT X.EMPCODE FROM HR_EMPLOYEEMASTER X, HR_PAYROLL Y WHERE X.COMPANYCODE = 'LSH'

AND X.COMPANYCODE = Y.COMPANYCODE AND X.EMPCODE = Y.EMPCODE AND Y.CPFSTATUS <> '4' AND X.EMPCODE='IQ001' )

group by A.CompanyCode,A.EmpCode,F.EMPLOYEENAME,E.DESCSHORT

## 0 comments:

## Post a Comment