CODEWITHSUNDEEP
sqlsql-serverjoin
Yara1994
Yara1994

Reputation: 391

Need assistance in Joining 2 tables

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:

AuthId ClientId AuthStartDate AuthEndDate Month Year AllowedHoursPerMonth HoursBilled
123 55 2021-12-19 2022-03-17 January 2022 43 26.33
123 55 2021-12-19 2022-03-17 February 2022 43 32.5
109 55 2021-12-19 2022-03-17 January 2022 9 4.25
109 55 2021-12-19 2022-03-17 February 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 43 NULL
777 55 2021-12-19 2022-03-17 January 2022 43 2.5
777 55 2021-12-19 2022-03-17 February 2022 43 1.5
345 55 2022-03-18 2022-07-28 NULL NULL 40 NULL
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

Output I need:

AuthId ClientId AuthStartDate AuthEndDate Month Year AllowedHoursPerMonth HoursBilled
123 55 2021-12-19 2022-03-17 January 2022 43 26.33
123 55 2021-12-19 2022-03-17 February 2022 43 32.5
109 55 2021-12-19 2022-03-17 January 2022 9 4.25
109 55 2021-12-19 2022-03-17 February 2022 9 4.25
218 55 2021-12-19 2022-03-17 January 2022 6 0
218 55 2021-12-19 2022-03-17 February 2022 6 0
619 55 2021-12-19 2022-03-17 January 2022 43 0
619 55 2021-12-19 2022-03-17 February 2022 43 0
777 55 2021-12-19 2022-03-17 January 2022 43 2.5
777 55 2021-12-19 2022-03-17 February 2022 43 1.5
345 55 2022-03-18 2022-07-28 NULL NULL 40 NULL
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

Upvotes: 0

Views: 66

Answers (1)

ArchAngelPwn
ArchAngelPwn

Reputation: 3046

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

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

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.

Upvotes: 1

Related Questions