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;