Select all_data.*,gross_salary from
(SELECT
ISNULL(A.[staffpin], '') AS [staffpin],
ISNULL(A.[staffname], '') AS [staffname],
ISNULL(A.[sex], '') AS [sex],
CONVERT(date, A.[dateofbirth]) AS [dateofbirth],
DATEDIFF(year,A.[dateofbirth],GETDATE()) AS [age],
CONVERT(date, A.[joining_date]) AS [joining_date],
DATEDIFF(year,A.[joining_date],GETDATE()) AS [service_length],
Case when ISNULL(A.[status], '') = 'C' then 'CONFIRM' else 'NON CONFIRM' end AS [status],
ISNULL(A.[jobstatus], '') AS [jobstatus],
ISNULL(A.[jobbase], '') AS [jobbase],
ISNULL(A.[designation], '') AS [designation],
ISNULL(A.[functionaldesignation], '') AS [Role],
ISNULL(A.[program_name], '') AS [program_name],
ISNULL(A.[project_name], '') AS [project_name],
ISNULL(A.[branchname], '') AS [branchname],
ISNULL(A.[district_name], '') AS [district_name],
ISNULL(A.[thana_name], '') AS [thana_name],
ISNULL(A.[division_name], '') AS [division_name],
CASE WHEN ISNULL(A.[level], 0) = 99 THEN 0 ELSE ISNULL(A.[level], 0) END AS [Grade],
ISNULL(A.[email_address], '') AS [email_address],
ISNULL(A.[mobile], '') AS [mobile],
ISNULL(slb.slab, 0) AS [slab],
ISNULL(A.[BirthDistrict], '') AS [HomeDistrict],
ISNULL(A.[LastEducation], '') AS [LastEducation],
ISNULL(A.[Last_Pms], '') AS [PMS2023],
ISNULL(A.[Second_Last_Pms], '') AS [PMS2022],
ISNULL(A.[Third_Last_Pms], '') AS [PMS2021],
ISNULL(A.[Last_Promotion_Date], '') AS [Last_Promotion_Date],
ISNULL(A.[Action_Taken], '') AS [Action_Taken],
ISNULL(A.[LastTransferDate], '') AS [LastTransferDate],
[Last_Salary_Enhance_date] [LastSlab]
FROM [HRReportDB].[HRReportDB].[dbo].[tblERP_Data] A
LEFT JOIN (
SELECT RIGHT('00000000' + pin, 8) AS PIN, ISNULL(slab, 0) AS slab FROM OPENQUERY(payroll_tool, 'SELECT pin, slab FROM payroll_tools.staffs')
) slb ON a.staffPIN = slb.PIN
WHERE a.jobstatus = 'Active' AND HRProgramId = '11'
AND a.HRProgramId NOT IN ('00', '08', '41', '33', '05', '78', '66', '65', '50', '31')
AND a.jobbase IN ('REGULAR', 'SERVICE', 'CONTRACT')
UNION
SELECT
RIGHT(REPLICATE('0', 8) + k.pin,8) AS [staffpin],
ISNULL(k.name, '') AS StaffName,
ISNULL(A.[sex], '') AS [sex],
CONVERT(date, A.[dateofbirth]) AS [dateofbirth],
DATEDIFF(
year,
A.[dateofbirth],
GETDATE()
) AS [age],
CONVERT(date, A.[joining_date]) AS [joining_date],
DATEDIFF(year,A.[joining_date],GETDATE()) AS [service_length],
ISNULL(k.job_status, '') AS [status],
'Active' AS [jobstatus],
ISNULL(k.employee_type, '') AS [jobbase],
ISNULL(k.designation, '') AS [designation],
ISNULL(k.designation, '') AS [Role],
ISNULL(k.program_name, '') AS [program_name],
ISNULL(k.project_name, '') AS [project_name],
ISNULL(k.branch_name, '') AS [branchname],
ISNULL(Branch.DistrictName, '') AS [district_name],
ISNULL(Branch.UpazilaName, '') AS [thana_name],
ISNULL(Branch.DivisionName, '') AS [division_name],
RIGHT(
REPLICATE('0', 3) + CAST(
ISNULL(k.grade, 0) AS VARCHAR
),
3
) AS [Grade],
ISNULL(A.[email_address], '') AS [email_address],
ISNULL(A.[mobile], '') AS [mobile],
RIGHT(
REPLICATE('0', 3) + CAST(
CASE WHEN ISNULL(k.slab, 0) IN (0, 99, 16) THEN 0 ELSE k.slab END AS VARCHAR
),
3
) AS [slab],
ISNULL(A.[BirthDistrict], '') AS [HomeDistrict],
ISNULL(A.[LastEducation], '') AS [LastEducation],
ISNULL(A.[Last_Pms], '') AS [PMS2023],
ISNULL(A.[Second_Last_Pms], '') AS [PMS2022],
ISNULL(A.[Third_Last_Pms], '') AS [PMS2021],
ISNULL(A.[Last_Promotion_Date], '') AS [Last_Promotion_Date],
ISNULL(A.[Action_Taken], '') AS [Action_Taken],
ISNULL(A.[LastTransferDate], '') AS [LastTransferDate],
[Last_Salary_Enhance_date] [LastSlab]
FROM
OPENQUERY(
PAYROLL_TOOL, 'SELECT DISTINCT month, pin, name, designation, grade, slab, employee_type, job_status, project_name, project_code, branch_name, branch_code, program_name, program_code, salary_to_be_paid
FROM (
SELECT MAX(sa.id) AS id, b.month, pin, sa.name, designation, grade, slab, employee_type, job_status, project_name, project_code, branch_name, branch_code, program_name, program_code, salary_to_be_paid
FROM payroll_tools.salary_info_history sa
INNER JOIN payroll_tools.batches ba ON ba.id = sa.batch_id
INNER JOIN payroll_tools.salary_months b ON b.id = ba.salary_month_id
WHERE sa.salary_to_be_paid != ''separation''
AND ba.`type` = ''salary'' and sa.program_code = ''11''
AND b.month IN (
SELECT MAX(month)
FROM payroll_tools.batches bb
INNER JOIN payroll_tools.salary_months bm ON bm.id = bb.salary_month_id
WHERE salary_month_id IS NOT NULL
AND status IN (''finalized'',''archived'')
)
GROUP BY pin, b.month, sa.name, designation, grade, slab, employee_type, job_status, project_name, project_code, branch_name, branch_code, program_name, program_code, salary_to_be_paid
) s'
) k
LEFT JOIN HRReportDb.HRReportDb.dbo.tblERP_Data A ON staffpin = RIGHT(REPLICATE('0', 8) + k.pin,8)
LEFT JOIN DataProject.bpm.Branch ON HR_BranchId = branch_code
WHERE JobBase NOT IN ('REGULAR', 'SERVICE', 'CONTRACT')
) all_data
left join (
select right('00000000'+pin,8) pin,gross_salary from openquery(payroll_tool,'select pin,gross_salary from payroll_tools.salary_structure')
) sal on sal.pin = all_data.staffpin;