Code:
SELECT AX.EmployeeNumber, AX.EmployeeName, Active.ResidentName, AX.BasicSalary, AX.NetSalary, AX.Region, AX.LaborGroup, AX.Gender,
AX.StartDate, AX.ContractEndDate, AX.Passport, AX.Iqama, AX.StatusEng, AX.SubStatusEng, AX.LocationDescription, AX.ProfessionEnglish,
AX.Nationality, AX.ClientName, AX.CustomerContractNumber, AX.Religion, Active.PassportExpiryDateHijri,
CASE WHEN PassportExpiryDateHijri<> ''
THEN RIGHT(PassportExpiryDateHijri,2)+'/'+ substring(PassportExpiryDateHijri,5,2)+'/' +LEFT(PassportExpiryDateHijri,4) else PassportExpiryDateHijri end AS [Passport Expiry],
Active.IqamaExpiryDateHijri,
CASE WHEN IqamaExpiryDateHijri<>''
then RIGHT(IqamaExpiryDateHijri,2)+'/'+ substring(IqamaExpiryDateHijri,5,2)+'/' +LEFT(IqamaExpiryDateHijri,4) else IqamaExpiryDateHijri end AS [Iqama Expiry],
Active.ActiveStatus,
ERVisa.ERVisaIssuanceHijri,
ERVisa.ERVisaExpiryHijri,
ERVisa.VisaType,
ERVisa.InOutKingdom,
Escaped.Status,
FEVisa.FEVisaIssuanceDate,
FEVisa.FEVisaTravelBeforeDate,
FEVisa.FEVisaStatus,
DL.DrivinglicenseType,
DL.DLIssuanceDateHijri,
DL.DLExpiryDateHijri,
TermOKSA.Status,
case when [ContractEndDate]-[StartDate]<=0 then 'Terminated'
when [ContractEndDate]-[StartDate]>=0 then convert(int,getdate()-[StartDate]) else '' end AS [Working Days],
case when datediff(DAY,ContractEndDate,getdate())<=0 then 'Terminated From Company'
when datediff(DAY,ContractEndDate,getDate())>=0 then convert(varchar(10),datediff(DAY,ContractEndDate,getDate()))
else '' end AS [Contract Duration]
FROM AXNew AX
LEFT JOIN ActiveNew Active ON AX.Iqama = Active.IqamaNumber
LEFT JOIN DLNew DL ON AX.Iqama = DL.IqamaNumber
LEFT JOIN ERVisaNew ERVisa ON AX.Iqama = ERVisa.IqamaNumber
LEFT JOIN EscapedNew Escaped ON AX.Iqama = Escaped.IqamaNumber
LEFT JOIN FEVisaNew FEVisa ON AX.Iqama = FEVisa.IqamaNumber
LEFT JOIN TermOKSANew TermOKSA ON AX.Iqama = TermOKSA.IqamaNumber
GROUP BY AX.EmployeeNumber, AX.EmployeeName, Active.ResidentName, AX.BasicSalary, AX.NetSalary, AX.Region, AX.LaborGroup, AX.Gender, AX.StartDate, AX.ContractEndDate, AX.Passport, AX.Iqama, AX.StatusEng, AX.SubStatusEng, AX.LocationDescription, AX.ProfessionEnglish, AX.Nationality, AX.ClientName, AX.CustomerContractNumber, AX.Religion, Active.PassportExpiryDateHijri, Active.IqamaExpiryDateHijri, Active.ActiveStatus, ERVisa.ERVisaIssuanceHijri, ERVisa.ERVisaExpiryHijri, ERVisa.VisaType, ERVisa.InOutKingdom, Escaped.Status, FEVisa.FEVisaIssuanceDate, FEVisa.FEVisaTravelBeforeDate, FEVisa.FEVisaStatus, DL.DrivinglicenseType, DL.DLIssuanceDateHijri, DL.DLExpiryDateHijri, TermOKSA.Status