在连接 2 个表时需要帮助

Question

我需要连接 2 个表并分配 0（HoursBilled 列），并且每个唯一 AuthId 的所有月份都出现在 BilledHours 表中。

第一个表 (AuthHours) 包含每个 ClientId 的数据以及每个唯一 AuthId 在不同日期范围内允许的时间（每月）。

create table AuthHours
(AuthId INT, ClientId INT, AuthStartDate DATE, AuthEndDate DATE, AllowedHoursPerMonth Float);

INSERT INTO AuthHours
VALUES
(123, 55, '2021-12-19', '2022-03-17', 43.0),
(109, 55, '2021-12-19', '2022-03-17', 9.0),
(218, 55, '2021-12-19', '2022-03-17', 6.0),
(619, 55, '2021-12-19', '2022-03-17', 43.0),
(777, 55, '2021-12-19', '2022-03-17', 43.0),
(345, 55, '2022-03-18', '2022-07-28', 40.0),
(346, 55, '2022-03-18', '2022-07-28', 12.0),
(395, 55, '2022-03-18', '2022-07-28', 10.0),
(487, 55, '2022-03-18', '2022-07-28', 45.0),
(198, 55, '2022-03-18', '2022-07-28', 37.0)

SELECT * FROM AuthHours

第二个表 (BilledHours)（已按 ClientId、AuthId、月份和年份分组）包含每个 ClientId 的数据以及每个 AuthId已 的计费小时数和月份。

create table BilledHours
(ClientId INT, Month VARCHAR(10), Year INT, AuthId INT, HoursBilled Float);

INSERT INTO BilledHours
VALUES
(55, 'January', 2022, 123, 26.33),
(55, 'January', 2022, 109, 4.25),
(55, 'January', 2022, 777, 2.5),
(55, 'February', 2022, 123, 32.5),
(55, 'February', 2022, 109, 4.25),
(55, 'February', 2022, 777, 1.5)

SELECT * FROM BilledHours

我需要为不在 BilledHours 表中的每个 AuthId 分配 0 HoursBilled，但如果 TODAY Date 不在 AuthStartDate 和 AuthEndDate 日期范围之间，请将其保留为 NULL。另外，需要为 BilledHours 表中没有的每个 AuthId 添加出现在 BilledHours 表中的月份和年份。

我的加入，但这是错误的（显然）。

SELECT  AuthHours.AuthId,
        AuthHours.ClientId,
        AuthHours.AuthStartDate,
        AuthHours.AuthEndDate,
        BilledHours.Month,
        BilledHours.Year,
        AuthHours.AllowedHoursPerMonth,
        BilledHours.HoursBilled
        
FROM AuthHours
LEFT JOIN BilledHours
  ON (AuthHours.AuthId = BilledHours.AuthId) AND (AuthHours.ClientId = BilledHours.ClientId)

不正确的输出：

AuthId	ClientId	AuthStartDate	AuthEndDate	月	年	AllowedHoursPerMonth	HoursBilled
123	55	2021-12-19	2022-03-17	一月	2022	43	26.33
123	55	2021-12-19	2022-03-17	二月	2022	43	32.5
109	55	2021-12-19	2022-03-17	年1月	2022	9	4.25
109	55	2021-12-19	2022-03-17	2月	2022年	9	4.25
218	55	2021-12-19	2022-03-17	NULL	NULL	6	NULL
619	55	2021-12-19	2022-03-17	NULL	NULL< /strong>	43	NULL
777	55	2021-12-19	2022-03-17	1 月	2022 年	43	2.5
777	55	2021-12-19	2022-03-17	2月	2022年	43	1.5
345	55	2022-03-18	2022-07-28	空	空	40	空
346	55	2022-03-18	2022-07-28	空	空	12	空
395	55	2022-03-18	2022-07-28	NULL	NULL	10	NULL
487	55	2022-03-18	2022-07-28	NULL	NULL	45	NULL
198	55	2022-03-18	2022-07-28	NULL	NULL	37	NULL

输出 I需要：

AuthId	ClientId	AuthStartDate	AuthEndDate	月	年	允许	小时数每月 小时数
123	55	2021-12-19	2022-03-17	1 月	2022 年	43	26.33
123	55	2021-12-19	2022-03-17	2 月	2022 年	43	32.5
109	55	2021-12-19	2022-03-17	1月	2022年	9	4.25
109	55	2021-12-19	2022-03-17	2月	2022年	9	4.25
218	55	2021-12-19	2022-03-17	1月	2022年	6	0
218	55	2021-12-19	2022-03-17	2月	2022年< /strong>	6	0
619	55	2021-12-19	2022-03-17	1月	2022年	43	0
619	55	2021-12-19	2022-03-17	2月	2022年< /strong>	43	0
777	55	2021-12-19	2022-03-17	一月	2022	43	2.5
777	55	2021-12-19	2022-03-17	2 月	2022 年	43	1.5
345	55	2022-03-18	2022-07-28	NULL	NULL	40	空
346	55	2022-03-18	2022-07-28	NULL	NULL	12	NULL
395	55	2022-03-18	2022-07-28	NULL	NULL	10	NULL
487	55	2022-03-18	2022-07-28	NULL	NULL	45	NULL
198	55	2022-03-18	2022-07-28	NULL	NULL	37	NULL

Answer 1

我更改了查询以反映您在月/年请求中寻找的更多内容

select
    distinct
    a.authid,
    a.clientid,
    a.authstartdate,
    a.authenddate,
    case when a.hoursbilled is null then null else a.month end as month,
    case when a.hoursbilled is null then null else a.year end as year,
    a.allowedhourspermonth,
    a.hoursbilled
from 
    (
        select
        a.authid,
        a.clientid,
        a.authstartdate,
        a.authenddate,
        a.allowedhourspermonth,
        a.month,
        a.year,
        case 
            when a.hoursbilled is null and getdate() between cast(a.authstartdate as date) and cast(a.authenddate as date) then 0
            when a.hoursbilled is null and getdate() not between cast(a.authstartdate as date) and cast(a.authenddate as date) then null
            else a.hoursbilled 
        end as hoursbilled
    from 
        (
        SELECT 
            a.authid,
            a.clientid,
            a.authstartdate,
            a.authenddate,
            a.allowedhourspermonth,
            b.month,
            b.year,
            b.hoursbilled
        FROM 
            (
            select
                d.clientid,
                d.month,
                d.year,
                d.authid,
                b.hoursbilled
            from 
                (
                    select
                        distinct
                        a.clientid,
                        a.authid,
                        month,
                        year
                    from BilledHours b 
                        join AuthHours a on 1=1
                ) d 
                left join BilledHours b on b.authid = d.authid and d.clientid = b.clientid and d.month = b.month and b.year = d.year
            ) b 
                left join AuthHours a on a.clientid = b.clientid and a.authid = b.authid
        ) a
    ) a

。这是一个没有 CAL_DM 表的选项，但如果您的公司还没有 CAL_DM 表，我建议您投资 CAL_DM 表。