在加入2個表時需要幫助

我需要加入 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、Month 和 Year 分組）包含每個 ClientId 的資料以及每個 AuthId 和 Month 的已計費小時數。

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)

輸出錯誤：

我需要的輸出：

uj5u.com熱心網友回復：

我更改了查詢以反映您在月/年請求中尋找的更多內容

with cte_date
as
(
select
    distinct
    a.clientid,
    a.authid,
    month,
    year
from BilledHours b 
    join AuthHours a on 1=1
), cte_1
as
(
select
    d.clientid,
    d.month,
    d.year,
    d.authid,
    b.hoursbilled
from cte_date 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
), cte_2
as
(
SELECT 
    a.authid,
    a.clientid,
    a.authstartdate,
    a.authenddate,
    a.allowedhourspermonth,
    b.month,
    b.year,
    b.hoursbilled
FROM cte_1 b 
    left join AuthHours a on a.clientid = b.clientid and a.authid = b.authid
), cte_3
as
(
select
    authid,
    clientid,
    authstartdate,
    authenddate,
    allowedhourspermonth,
    month,
    year,
    case 
        when hoursbilled is null and getdate() between cast(authstartdate as date) and cast(authenddate as date) then 0
        when hoursbilled is null and getdate() not between cast(authstartdate as date) and cast(authenddate as date) then null
        else hoursbilled 
    end as hoursbilled
from cte_2
)
select
    distinct
    authid,
    clientid,
    authstartdate,
    authenddate,
    case when hoursbilled is null then null else month end as month,
    case when hoursbilled is null then null else year end as year,
    allowedhourspermonth,
    hoursbilled
from cte_3
order by authid, month desc

這是一個沒有 CAL_DM 表的選項，但如果您的公司還沒有 CAL_DM 表，我建議您投資購買一個 CAL_DM 表。

標籤：sql sql服务器 加入

上一篇：我的css更改未顯示H1和h2問題

下一篇：通過時區處理SQL連接