I need to join 2 tables and assign 0 (HoursBilled column) and all Months appeared in the BilledHours table for each unique AuthId.

First table (AuthHours) has data of each ClientId and their allowed hours (per month) for different date ranges per unique 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

Second table (BilledHours) (already grouped by ClientId, AuthId, Month and Year) has data of each ClientId and their already billed hours per AuthId and 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

I need to assign 0 HoursBilled for each AuthId that not in the BilledHours table, but if TODAY Date not between AuthStartDate and AuthEndDate date ranges, keep it NULL. Also, Month and Year appeared in the BilledHours table need to be added for each AuthId that not in the BilledHours table.

My Join, but it's wrong (obviously).

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)

Incorrect output:

Output I need:

CodePudding user response：

I changed the query to reflect more what you are looking for with the month/year request

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

This is an option without a CAL_DM table, but I would recommend investing in a CAL_DM table if your company does not have one already.