如何将两个SQL查询合并为含工作日计算的最终结果表

原Tableau FIXED计算字段 #

{ FIXED [Client ID]: MAX(IF [Appt Status] = 'Complete' AND [Appointment Type Roll-Up] = '1' THEN [Appt Date]END)}

{ FIXED [Client ID]: MIN(IF[Appointment Status Roll-UP]='Active' and [Appt Date] > [Last Direct Trmt Appt] then [Appt Date]END)}

已完成的SQL代码 #

1. 创建临时表#Last_Direct_treatment的主查询 #

SELECT 
s.[appointment_id]
,c.[full_name]
,s.[client_id]
,s.[case_number]
,s.[appointment_date]
,s.[appointment_type]
,s.[appointment_status]
,ca.[last_direct_only_date]
,t.[authorization_status]
,t.[service_type]
,case 
when s.appointment_status = '*_Cancelled' then 'Cancelled'
when s.appointment_status = '*_Complete' then 'Complete'
when s.appointment_status = 'Complete' then 'Complete'
else 'Active'
end as Appointment_Status_Roll_up
,case when
s.[appointment_type] like '%IND%' or s.[appointment_type] like '%indirect%' then '0' else '1'
end as Appointment_type_roll_up

Into #Last_Direct_treatment 
     
FROM [appointment] s
INNER JOIN [client] c
ON s.[client_id] = c.[client_id]
INNER JOIN [client_case] ca
ON c.[client_id] = ca.[client_id]
INNER JOIN [authorization] t
ON ca.[case_number] = t.[case_number]

ORDER BY appointment_date DESC

2. 独立获取最大/最小日期的查询 #

获取最大日期

select 
a.client_id,
Max(a.appointment_date) as max_date
From #Last_Direct_treatment A
where  a.[appointment_status] = 'Complete' 
and  a.[Appointment_type_roll_up] = '1'
Group by client_id

获取最小日期

select 
a.client_id,
min(a.appointment_date) as min_date
From #Last_Direct_treatment A
where  a.[Appointment_Status_Roll_up] = 'active' 
and a.appointment_date > a.last_direct_only_date
Group by client_id

当前需求 #

将上述max_date和min_date字段整合到包含临时表所有字段的最终结果中，并新增基于这两个日期的工作日计算列。

解决方案 #

通过左连接结合CTE（公共表表达式）整合数据，同时计算工作日（示例基于SQL Server，不同数据库的工作日计算逻辑需调整）：

-- 用CTE存储两个聚合结果
WITH ClientMaxDate AS (
    select 
    a.client_id,
    Max(a.appointment_date) as max_date
    From #Last_Direct_treatment A
    where  a.[appointment_status] = 'Complete' 
    and  a.[Appointment_type_roll_up] = '1'
    Group by client_id
),
ClientMinDate AS (
    select 
    a.client_id,
    min(a.appointment_date) as min_date
    From #Last_Direct_treatment A
    where  a.[Appointment_Status_Roll_up] = 'active' 
    and a.appointment_date > a.last_direct_only_date
    Group by client_id
)
-- 整合所有字段并计算工作日
SELECT 
    ldt.*,
    cmd.max_date,
    cmin.min_date,
    -- 计算两个日期间的工作日（排除周末）
    CASE 
        WHEN cmd.max_date IS NULL OR cmin.min_date IS NULL THEN NULL
        ELSE DATEDIFF(day, cmd.max_date, cmin.min_date) 
             - (DATEDIFF(week, cmd.max_date, cmin.min_date) * 2)
             -- 如需排除节假日，需额外关联节假日表调整逻辑
    END AS work_days_between
FROM #Last_Direct_treatment ldt
LEFT JOIN ClientMaxDate cmd ON ldt.client_id = cmd.client_id
LEFT JOIN ClientMinDate cmin ON ldt.client_id = cmin.client_id
ORDER BY ldt.appointment_date DESC;

说明 #

• 左连接确保即使部分客户无符合条件的日期，仍保留主表全部数据；
• 工作日计算默认排除周末，若需排除节假日，需单独创建节假日表并关联计算；
• 若使用MySQL/PostgreSQL等其他数据库，需替换为对应工作日计算函数（例如PostgreSQL的自定义工作日函数、MySQL结合WEEKDAY()判断）。

内容的提问来源于stack exchange，提问作者agm