如何在跨服务器交叉连接查询中生成Employee_Utilization计算字段

修改后的完整查询代码 #

EXEC sp_addlinkedsrvlogin 'PAYROLLSERVER', 'false', NULL, '**', '**********'
SELECT
    A.[Employee ID],
    A.FIRSTNAME,
    A.LASTNAME,
    A.[PEREND],
    A.[CATEGORY],
    A.[AUDTORG],
    A.[EARDEDTYPE],
    A.[EARDEDDATE],
    A.[Payroll_Hours],
    B.[WO_Hours],
    -- 计算员工利用率，处理薪资时长为0的情况避免报错
    CASE 
        WHEN A.[Payroll_Hours] = 0 THEN NULL 
        ELSE ROUND((B.[WO_Hours] / A.[Payroll_Hours]) * 100, 2) 
    END AS [Employee_Utilization]
FROM
(
    SELECT
        C.EMPLOYEE AS [Employee ID],
        E.FIRSTNAME,
        E.LASTNAME,
        C.[PEREND],
        C.[CATEGORY],
        C.[AUDTORG],
        C.[EARDEDTYPE],
        C.[EARDEDDATE],
        SUM(DISTINCT C.[HOURS]) AS [Payroll_Hours]
    FROM [PAYROLLSERVER].[SagePayroll].[dbo].[UPCHKD] C 
    JOIN [PAYROLLSERVER01].[SagePayroll_SUD].[dbo].[UPHHAU] E ON C.EMPLOYEE = E.EMPLOYEE 
    WHERE C.CATEGORY = '2' AND C.PEREND = '20221210'
    GROUP BY
        C.EMPLOYEE,
        E.FIRSTNAME,
        E.LASTNAME,
        C.[PEREND],
        C.[CATEGORY],
        C.[AUDTORG],
        C.[EARDEDTYPE],
        C.[EARDEDDATE] 
) A
INNER JOIN -- 用INNER JOIN替代CROSS JOIN+WHERE，逻辑更直观
(
     SELECT [Employee ID],SUM([Labor Run Hours]) AS [WO_Hours]
     FROM [********].[*********].[dbo].[WorkOrders_Labor]
     WHERE [Date Worked] > '11/26/22' AND [Date Worked] < '12/11/22'
     GROUP BY [Employee ID] 
) B ON A.[Employee ID] = B.[Employee ID]

关键修改说明 #

1. *替换SELECT 为明确字段列表：避免出现重复的[Employee ID]列，同时精准控制输出字段顺序
2. 新增利用率计算列：
   • 用CASE语句处理Payroll_Hours为0的场景，防止除以0报错
   • 通过ROUND函数将结果保留两位小数，乘以100转换为百分比格式（不需要百分比可去掉*100）
3. 优化连接逻辑：将CROSS JOIN加WHERE条件替换为INNER JOIN，语义更清晰，与原查询逻辑完全一致

内容的提问来源于stack exchange，提问作者Sudenga-Steve