首页 > 解决方案 > 来自两个不同表的 SQL 报告

问题描述

我正在尝试运行一份详细说明所有 Salary&Job 更改的报告,但我的数据分为两个表 -job_changessalary_changes. 主要问题是工作和薪水变化之间没有关系。例如,某人可能换了工作而薪水没有受到影响,反之亦然。关于如何制作单个数据集的任何想法?谢谢

SELECT Employee.EMPLOY_REF AS EmployeeID,
         (RTRIM(Employee.FORENAME)+" "+Employee.SURNAME) AS EmployeeName,
         Salhisty.SAL_DATE AS RecordStartDate,
         V_salres.DESCRIPTION AS ReasonForChange,
         Jobhisty.JOBHISTY_REF AS Jobhisty_ref,
         Jobhisty.JOB_REF AS Job_ref,
         Jobhisty.JOB_NAME AS Job_name,
         CASE
WHEN  Salhisty.NEWEST_SAL = 'TRUE' THEN  Employee.MGR_REF 
ELSE NULL
END AS LineManagerID,
         Salhisty.SALARY AS AnnualPay,
         'EUR' AS AnnualPayCurrency,
         Employee.LEAVER AS Leaver
    FROM ((EMPLOYEE
         INNER JOIN SALHISTY
                 ON Employee.EMPLOY_REF = Salhisty.EMPLOY_REF)
         INNER JOIN V_SALRES
                 ON Salhisty.SAL_REASON = V_salres.CODE)
         LEFT OUTER JOIN jobhisty
                      ON Salhisty.EMPLOY_REF = Jobhisty.EMPLOY_REF
                     AND Salhisty.SAL_DATE = Jobhisty.FROMDATE
   WHERE (((Employee.EMPLOY_REF LIKE '%45261%')))
ORDER BY Employee.EMPLOY_REF ASC,
         Salhisty.SAL_DATE DESC

标签: report

解决方案


推荐阅读