Search code examples
sql-server-2008ssms

Stop to bring duplicate results based in a condition

Even though my query is behaving the way it should be, I have an issue in the records that I can't fix in the database. So, I want to fix it in the query.

The issue is: as the image attached, for one specific customer, there are duplicate IDs due to the Sales Person being different.

I need to bring just one result for these IDs, doesn't matter the salesperson.

I've tried:

SELECT
    CONCAT(al.agr_header_recid, '-', ap.month, '-', ap.year) AS id,
    al.Company_Name,
    ah.AGR_Name,
    al.agr_type_desc,
    al.Valid_flag,
    CAST(al.DateStart AS DATE) AS date_start,
    CAST(al.DateEND AS DATE) AS date_end,
    al.Billing_Cycle_Desc AS billing_cycle,
    al.Billing_Amount,
    al.Agreement_Status,
    o.Owner_Level_Name AS 'Sales Person',

    
    CASE
    WHEN ah.PP_Time_Flag = 'True'
        AND ah.PP_Expenses_Flag = 'False'
        AND ah.PP_Products_Flag = 'False' THEN 'Time'
    WHEN ah.PP_Time_Flag = 'True'
        AND ah.PP_Expenses_Flag = 'True'
        AND ah.PP_Products_Flag = 'False' THEN 'Time & Expenses'
    WHEN ah.PP_Time_Flag = 'True'
        AND ah.PP_Expenses_Flag = 'True'
        AND ah.PP_Products_Flag = 'True' THEN 'Time, Products, & Expenses'
    WHEN ah.PP_Time_Flag = 'True'
        AND ah.PP_Expenses_Flag = 'False'
        AND ah.PP_Products_Flag = 'True' THEN 'Time & Products'
    WHEN ah.PP_Time_Flag = 'False'
        AND ah.PP_Expenses_Flag = 'True'
        AND ah.PP_Products_Flag = 'False' THEN 'Expenses'
    WHEN ah.PP_Time_Flag = 'False'
        AND ah.PP_Expenses_Flag = 'True'
        AND ah.PP_Products_Flag = 'True' THEN 'Products & Expenses'
    WHEN ah.PP_Time_Flag = 'False'
        AND ah.PP_Expenses_Flag = 'False'
        AND ah.PP_Products_Flag = 'True' THEN 'Products'
    ELSE NULL
END AS agreement_covers_list
,CASE ah.AGR_NoEnd_Flag
    WHEN 1 THEN 'Yes'
    ELSE 'No'
END AS no_end_flag


,COALESCE(ap.Rev, 0) + COALESCE(child.rev, 0) AS total_revenue
,CAST(ap.Agr_Date_inv AS DATE) AS date_agreement_invoiced
,COALESCE(ap.Hours, 0) + COALESCE(child.hours, 0) AS total_agreement_hours
,COALESCE(ap.labor_Cost, 0) + COALESCE(child.labor_Cost, 0) AS total_labor_cost
,COALESCE(ap.prod_cost, 0) + COALESCE(child.prod_cost, 0) AS total_addition_cost
,CAST(CASE WHEN child.parent_recid IS NULL THEN 0 ELSE 1 END AS bit) AS has_child_agreement
,child.child_agreeements
,child.child_agreeement_types
,child.count AS number_of_child_agreements
,child.rev AS child_revenue
,ap.Rev AS parent_revenue
,ap.Hours AS parent_hours
,child.hours AS child_Hours
,ap.labor_Cost AS parent_labor_cost
,child.labor_Cost AS child_labor_cost
,ap.prod_cost AS parent_addition_cost
,child.prod_cost AS child_addition_cost
,COALESCE(ap.labor_Cost, 0) + COALESCE(child.labor_Cost, 0) + COALESCE(ap.prod_cost, 0)
    + COALESCE(child.prod_cost, 0) AS total_cost
,CASE
    WHEN (COALESCE(ap.hours, 0) + COALESCE(child.hours, 0)) = 0 THEN NULL
    ELSE (COALESCE(ap.rev, 0) + COALESCE(child.rev, 0))/(COALESCE(ap.hours, 0) + COALESCE(child.hours, 0))
END AS total_all_ehr
,CASE
    WHEN (COALESCE(ap.hours, 0) + COALESCE(child.hours, 0)) = 0 THEN NULL
    ELSE ((COALESCE(ap.rev, 0) + COALESCE(child.rev, 0)) - (COALESCE(ap.prod_cost, 0) + COALESCE(child.prod_cost, 0)))
           /(COALESCE(ap.hours, 0) + COALESCE(child.hours, 0))
END AS total_no_addition_cost_ehr
,CASE
    WHEN (COALESCE(ap.hours, 0)) = 0 THEN NULL
    ELSE (COALESCE(ap.rev, 0))/(COALESCE(ap.hours, 0))
END AS parent_all_ehr
,CASE
    WHEN (COALESCE(ap.hours, 0)) = 0 THEN NULL
    ELSE (COALESCE(ap.rev, 0) - COALESCE(ap.prod_cost, 0))/(COALESCE(ap.hours, 0))
END AS parent_no_addition_cost_ehr
,CASE
    WHEN (COALESCE(child.hours, 0)) = 0 THEN NULL
    ELSE (COALESCE(child.rev, 0) - COALESCE(child.prod_cost, 0))/(COALESCE(child.hours, 0))
END AS child_no_addition_cost_ehr
,CASE
    WHEN (COALESCE(child.hours, 0)) = 0 THEN NULL
    ELSE (COALESCE(child.rev, 0))/(COALESCE(child.hours, 0))
END AS child_all_ehr
,(COALESCE(ap.rev, 0) + COALESCE(child.rev, 0))
    - (COALESCE(ap.labor_Cost, 0) + COALESCE(child.labor_Cost, 0) + COALESCE(ap.prod_cost, 0) + COALESCE(child.prod_cost, 0))
AS total_agreement_margin
,CASE
    WHEN (COALESCE(ap.rev, 0) + COALESCE(child.rev, 0)) = 0 THEN 0
    ELSE ((COALESCE(ap.rev, 0) + COALESCE(child.rev, 0))
            - (COALESCE(ap.labor_Cost, 0) + COALESCE(child.labor_Cost, 0) + COALESCE(ap.prod_cost, 0)
                + COALESCE(child.prod_cost, 0)))
        / ((COALESCE(ap.rev, 0) + COALESCE(child.rev, 0)))
END AS total_agreement_margin_percentage


FROM AGR_Header AS ah

INNER JOIN v_rpt_AgreementList AS al ON ah.AGR_Header_RecID = al.AGR_Header_RecID
INNER JOIN (SELECT 
                    Company_RecID,
                    territory_recid

            FROM Billing_Log

            GROUP BY Company_RecID, territory_recid) AS bl ON ah.Company_RecID = bl.Company_RecID

INNER JOIN Owner_Level AS o ON bl.territory_recid = o.Owner_Level_RecID
INNER JOIN (SELECT ar.AGR_Header_RecID
            ,ar.Month
            ,ar.Year
            ,ar.Agr_Date_inv
            ,ar.Rev
            ,ac.Hours AS hours
            ,ac.Cost AS labor_cost
            ,addi.prod_cost
            FROM
                (SELECT ah.AGR_Header_RecID
                ,ai.Month_Nbr AS month
                ,ai.Year_Nbr AS year
                ,CONVERT (VARCHAR(8), ai.Year_Nbr, 120) + '-' + RIGHT ('0' + CONVERT (VARCHAR(8), ai.Month_nbr, 120), 2)
                    + '-' + '01' AS agr_date_inv
                ,CAST(SUM(ai.Monthly_Inv_Amt - ai.Monthly_SalesTax_Amt) AS NUMERIC (18, 2)) AS rev
                FROM agr_header AS ah
                INNER JOIN agr_invoice_amt AS ai ON ah.AGR_Header_RecID = ai.AGR_Header_RecID
                GROUP BY ah.AGR_Header_RecID
                ,ai.Month_Nbr
                ,ai.Year_Nbr) AS ar
            LEFT JOIN
                (SELECT ah.AGR_Header_RecID
                ,DATEPART(MONTH, te.Date_Start) AS month
                ,DATEPART(YEAR, te.Date_Start) AS year
                ,SUM(te.AgrHrsCovered) AS hours
                ,CAST(SUM(te.AgrHrsCovered * te.Hourly_Cost_Decimal) AS NUMERIC (18, 2)) AS cost
                FROM v_rpt_time AS te
                INNER JOIN agr_header AS ah ON te.Agr_Header_RecID = ah.AGR_Header_RecID
                WHERE te.date_start >= DATEADD(mm, -3, CURRENT_TIMESTAMP)
                AND te.Agr_Header_RecID IS NOT NULL
                AND te.AgrHrsCovered IS NOT NULL
                GROUP BY ah.AGR_Header_RecID
                ,DATEPART(MONTH, te.Date_Start)
                ,DATEPART(YEAR, te.Date_Start)) AS ac ON ar.AGR_Header_RecID = ac.AGR_Header_RecID
                     AND ar.Month = ac.Month
                     AND ar.Year = ac.Year
                LEFT JOIN
                    (SELECT SUM(vadi.Extended_Cost_Amount) AS prod_cost
                    ,vadi.AGR_Header_RecID
                    ,vadi.agr_month AS month
                    ,vadi.agr_year AS year
                    FROM iv_product vadi
                    GROUP BY vadi.AGR_Header_RecID
                    ,vadi.agr_month
                    ,vadi.agr_year) AS addi ON addi.AGR_Header_RecID = ar.AGR_Header_RecID AND ar.year = addi.year
                                                AND ar.month = addi.month
    ) AS ap ON ap.AGR_Header_RecID = al.AGR_Header_RecID
LEFT JOIN
    (SELECT ar.parent_recid
    ,ar.Month
    ,ar.Year
    ,ar.Rev
    ,ac.Hours AS hours
    ,ac.Cost AS labor_cost
    ,ar.child_agreeements
    ,ar.count
    ,ar.child_agreeement_types
    ,addi.prod_cost
    FROM
        (SELECT ah.parent_recid
        ,ai.Month_Nbr AS month
        ,ai.Year_Nbr AS year
        ,CAST(SUM(ai.Monthly_Inv_Amt - ai.Monthly_SalesTax_Amt) AS NUMERIC (18, 2)) AS rev
        ,SUBSTRING(
              (SELECT ', '+ahc.AGR_Name  AS [text()]
              FROM agr_header ahc
              WHERE ahc.parent_recid = ah.parent_Recid
              ORDER BY ahc.AGR_Name
              For XML PATH (''))
            , 2, 1000) [child_agreeements]
        ,SUBSTRING(
              (SELECT ', '+atc.AGR_Type_Desc  AS [text()]
              FROM agr_header ahc2
              INNER JOIN AGR_Type atc ON atc.AGR_Type_RecID = ahc2.AGR_Type_RecID
              WHERE ahc2.parent_recid = ah.parent_Recid
              ORDER BY atc.AGR_Type_Desc
              For XML PATH (''))
            , 2, 1000) [child_agreeement_types]
        ,COUNT(1) AS count
        FROM agr_header AS ah
        INNER JOIN agr_invoice_amt AS ai ON ah.AGR_Header_RecID = ai.AGR_Header_RecID
        GROUP BY ah.parent_Recid
        ,ai.Month_Nbr
        ,ai.Year_Nbr) AS ar
        LEFT JOIN
            (SELECT ah.parent_Recid
            ,DATEPART(MONTH, te.Date_Start) AS month
            ,DATEPART(YEAR, te.Date_Start) AS year
            ,SUM(te.AgrHrsCovered) AS hours
            ,CAST(SUM(te.AgrHrsCovered * te.Hourly_Cost_Decimal) AS NUMERIC (18, 2)) AS cost
            FROM v_rpt_time AS te
            INNER JOIN agr_header AS ah ON te.Agr_Header_RecID = ah.AGR_Header_RecID
            WHERE te.date_start >= DATEADD(mm, -6, CURRENT_TIMESTAMP)
            AND te.Agr_Header_RecID IS NOT NULL
            AND te.AgrHrsCovered IS NOT NULL
            GROUP BY ah.parent_Recid
            ,DATEPART(MONTH, te.Date_Start)
            ,DATEPART(YEAR, te.Date_Start)) AS ac ON ar.parent_recid = ac.parent_recid AND ar.Month = ac.Month
                                                        AND ar.Year = ac.Year
        LEFT JOIN
            (SELECT SUM(vadi.Extended_Cost_Amount) AS prod_cost
            ,ahp.parent_Recid
            ,vadi.agr_month AS month
            ,vadi.agr_year AS year
            FROM agr_header ahp
            INNER JOIN iv_product vadi ON vadi.AGR_Header_RecID = ahp.AGR_Header_RecID
            GROUP BY ahp.parent_Recid
            ,vadi.agr_month
            ,vadi.agr_year) AS addi ON addi.parent_Recid = ar.parent_Recid AND ar.year = addi.year AND ar.month = addi.month
    ) AS child ON child.parent_recid = al.AGR_Header_RecID AND child.month = ap.month AND child.year = ap.year





WHERE ah.parent_Recid IS NULL 
  AND ap.agr_date_inv > DATEADD(YEAR, -2, CURRENT_TIMESTAMP)
  AND ap.agr_date_inv <= CURRENT_TIMESTAMP
  AND o.Owner_Level_Name IN ('Australia', 'Alex', 'Adam')
  AND al.Agreement_Status = 'active'
  AND al.Company_Name = 'Client A'

But it's not working.

This is the outcome (not pasting all the columns here) and a picture could be useful due the formating, its attached in the end:

id Company_Name AGR_Name agr_type_desc Valid_flag date_start date_end billing_cycle Billing_Amount Agreement_Status Sales Person 464-2-2022 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Adam 464-2-2022 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Alex 464-6-2021 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Adam 464-6-2021 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Alex 464-10-2020 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Adam 464-10-2020 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Alex 464-4-2021 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Adam 464-4-2021 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Alex 464-8-2020 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Adam 464-8-2020 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Alex 464-11-2021 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Adam 464-11-2021 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Alex 464-3-2021 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Adam 464-3-2021 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Alex 464-7-2020 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Adam 464-7-2020 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Alex 464-4-2022 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Adam 464-4-2022 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Alex 464-1-2021 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Adam 464-1-2021 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Alex 464-8-2021 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Adam 464-8-2021 Client A Client A - BaaS Service - User Master Services Agreement 1 1/06/2020 NULL Monthly 150 Active Alex

query outcome

Solution
    1. Basically, I have used the function ROW_NUMBER, as shown in the line before your "id" column.
    2. In case you do not want to show the "row number column" or "rn" in the results, you can replace the * in the following query, SELECT * FROM TMP WHERE rnk = 1 to every column name you have previously mentioned except "rn".
    3. Let me know if this works for you.
    WITH TMP AS (
SELECT
    ROW_NUMBER() OVER(PARTITION BY CONCAT(al.agr_header_recid, '-', ap.month, '-', ap.year) ORDER BY o.Owner_Level_Name) as rn,
    CONCAT(al.agr_header_recid, '-', ap.month, '-', ap.year) AS id,
    al.Company_Name,
    ah.AGR_Name,
    al.agr_type_desc,
    al.Valid_flag,
    CAST(al.DateStart AS DATE) AS date_start,
    CAST(al.DateEND AS DATE) AS date_end,
    al.Billing_Cycle_Desc AS billing_cycle,
    al.Billing_Amount,
    al.Agreement_Status,
    o.Owner_Level_Name AS 'Sales Person',

    
    CASE
    WHEN ah.PP_Time_Flag = 'True'
        AND ah.PP_Expenses_Flag = 'False'
        AND ah.PP_Products_Flag = 'False' THEN 'Time'
    WHEN ah.PP_Time_Flag = 'True'
        AND ah.PP_Expenses_Flag = 'True'
        AND ah.PP_Products_Flag = 'False' THEN 'Time & Expenses'
    WHEN ah.PP_Time_Flag = 'True'
        AND ah.PP_Expenses_Flag = 'True'
        AND ah.PP_Products_Flag = 'True' THEN 'Time, Products, & Expenses'
    WHEN ah.PP_Time_Flag = 'True'
        AND ah.PP_Expenses_Flag = 'False'
        AND ah.PP_Products_Flag = 'True' THEN 'Time & Products'
    WHEN ah.PP_Time_Flag = 'False'
        AND ah.PP_Expenses_Flag = 'True'
        AND ah.PP_Products_Flag = 'False' THEN 'Expenses'
    WHEN ah.PP_Time_Flag = 'False'
        AND ah.PP_Expenses_Flag = 'True'
        AND ah.PP_Products_Flag = 'True' THEN 'Products & Expenses'
    WHEN ah.PP_Time_Flag = 'False'
        AND ah.PP_Expenses_Flag = 'False'
        AND ah.PP_Products_Flag = 'True' THEN 'Products'
    ELSE NULL
END AS agreement_covers_list
,CASE ah.AGR_NoEnd_Flag
    WHEN 1 THEN 'Yes'
    ELSE 'No'
END AS no_end_flag


,COALESCE(ap.Rev, 0) + COALESCE(child.rev, 0) AS total_revenue
,CAST(ap.Agr_Date_inv AS DATE) AS date_agreement_invoiced
,COALESCE(ap.Hours, 0) + COALESCE(child.hours, 0) AS total_agreement_hours
,COALESCE(ap.labor_Cost, 0) + COALESCE(child.labor_Cost, 0) AS total_labor_cost
,COALESCE(ap.prod_cost, 0) + COALESCE(child.prod_cost, 0) AS total_addition_cost
,CAST(CASE WHEN child.parent_recid IS NULL THEN 0 ELSE 1 END AS bit) AS has_child_agreement
,child.child_agreeements
,child.child_agreeement_types
,child.count AS number_of_child_agreements
,child.rev AS child_revenue
,ap.Rev AS parent_revenue
,ap.Hours AS parent_hours
,child.hours AS child_Hours
,ap.labor_Cost AS parent_labor_cost
,child.labor_Cost AS child_labor_cost
,ap.prod_cost AS parent_addition_cost
,child.prod_cost AS child_addition_cost
,COALESCE(ap.labor_Cost, 0) + COALESCE(child.labor_Cost, 0) + COALESCE(ap.prod_cost, 0)
    + COALESCE(child.prod_cost, 0) AS total_cost
,CASE
    WHEN (COALESCE(ap.hours, 0) + COALESCE(child.hours, 0)) = 0 THEN NULL
    ELSE (COALESCE(ap.rev, 0) + COALESCE(child.rev, 0))/(COALESCE(ap.hours, 0) + COALESCE(child.hours, 0))
END AS total_all_ehr
,CASE
    WHEN (COALESCE(ap.hours, 0) + COALESCE(child.hours, 0)) = 0 THEN NULL
    ELSE ((COALESCE(ap.rev, 0) + COALESCE(child.rev, 0)) - (COALESCE(ap.prod_cost, 0) + COALESCE(child.prod_cost, 0)))
           /(COALESCE(ap.hours, 0) + COALESCE(child.hours, 0))
END AS total_no_addition_cost_ehr
,CASE
    WHEN (COALESCE(ap.hours, 0)) = 0 THEN NULL
    ELSE (COALESCE(ap.rev, 0))/(COALESCE(ap.hours, 0))
END AS parent_all_ehr
,CASE
    WHEN (COALESCE(ap.hours, 0)) = 0 THEN NULL
    ELSE (COALESCE(ap.rev, 0) - COALESCE(ap.prod_cost, 0))/(COALESCE(ap.hours, 0))
END AS parent_no_addition_cost_ehr
,CASE
    WHEN (COALESCE(child.hours, 0)) = 0 THEN NULL
    ELSE (COALESCE(child.rev, 0) - COALESCE(child.prod_cost, 0))/(COALESCE(child.hours, 0))
END AS child_no_addition_cost_ehr
,CASE
    WHEN (COALESCE(child.hours, 0)) = 0 THEN NULL
    ELSE (COALESCE(child.rev, 0))/(COALESCE(child.hours, 0))
END AS child_all_ehr
,(COALESCE(ap.rev, 0) + COALESCE(child.rev, 0))
    - (COALESCE(ap.labor_Cost, 0) + COALESCE(child.labor_Cost, 0) + COALESCE(ap.prod_cost, 0) + COALESCE(child.prod_cost, 0))
AS total_agreement_margin
,CASE
    WHEN (COALESCE(ap.rev, 0) + COALESCE(child.rev, 0)) = 0 THEN 0
    ELSE ((COALESCE(ap.rev, 0) + COALESCE(child.rev, 0))
            - (COALESCE(ap.labor_Cost, 0) + COALESCE(child.labor_Cost, 0) + COALESCE(ap.prod_cost, 0)
                + COALESCE(child.prod_cost, 0)))
        / ((COALESCE(ap.rev, 0) + COALESCE(child.rev, 0)))
END AS total_agreement_margin_percentage


FROM AGR_Header AS ah

INNER JOIN v_rpt_AgreementList AS al ON ah.AGR_Header_RecID = al.AGR_Header_RecID
INNER JOIN (SELECT 
                    Company_RecID,
                    territory_recid

            FROM Billing_Log

            GROUP BY Company_RecID, territory_recid) AS bl ON ah.Company_RecID = bl.Company_RecID

INNER JOIN Owner_Level AS o ON bl.territory_recid = o.Owner_Level_RecID
INNER JOIN (SELECT ar.AGR_Header_RecID
            ,ar.Month
            ,ar.Year
            ,ar.Agr_Date_inv
            ,ar.Rev
            ,ac.Hours AS hours
            ,ac.Cost AS labor_cost
            ,addi.prod_cost
            FROM
                (SELECT ah.AGR_Header_RecID
                ,ai.Month_Nbr AS month
                ,ai.Year_Nbr AS year
                ,CONVERT (VARCHAR(8), ai.Year_Nbr, 120) + '-' + RIGHT ('0' + CONVERT (VARCHAR(8), ai.Month_nbr, 120), 2)
                    + '-' + '01' AS agr_date_inv
                ,CAST(SUM(ai.Monthly_Inv_Amt - ai.Monthly_SalesTax_Amt) AS NUMERIC (18, 2)) AS rev
                FROM agr_header AS ah
                INNER JOIN agr_invoice_amt AS ai ON ah.AGR_Header_RecID = ai.AGR_Header_RecID
                GROUP BY ah.AGR_Header_RecID
                ,ai.Month_Nbr
                ,ai.Year_Nbr) AS ar
            LEFT JOIN
                (SELECT ah.AGR_Header_RecID
                ,DATEPART(MONTH, te.Date_Start) AS month
                ,DATEPART(YEAR, te.Date_Start) AS year
                ,SUM(te.AgrHrsCovered) AS hours
                ,CAST(SUM(te.AgrHrsCovered * te.Hourly_Cost_Decimal) AS NUMERIC (18, 2)) AS cost
                FROM v_rpt_time AS te
                INNER JOIN agr_header AS ah ON te.Agr_Header_RecID = ah.AGR_Header_RecID
                WHERE te.date_start >= DATEADD(mm, -3, CURRENT_TIMESTAMP)
                AND te.Agr_Header_RecID IS NOT NULL
                AND te.AgrHrsCovered IS NOT NULL
                GROUP BY ah.AGR_Header_RecID
                ,DATEPART(MONTH, te.Date_Start)
                ,DATEPART(YEAR, te.Date_Start)) AS ac ON ar.AGR_Header_RecID = ac.AGR_Header_RecID
                     AND ar.Month = ac.Month
                     AND ar.Year = ac.Year
                LEFT JOIN
                    (SELECT SUM(vadi.Extended_Cost_Amount) AS prod_cost
                    ,vadi.AGR_Header_RecID
                    ,vadi.agr_month AS month
                    ,vadi.agr_year AS year
                    FROM iv_product vadi
                    GROUP BY vadi.AGR_Header_RecID
                    ,vadi.agr_month
                    ,vadi.agr_year) AS addi ON addi.AGR_Header_RecID = ar.AGR_Header_RecID AND ar.year = addi.year
                                                AND ar.month = addi.month
    ) AS ap ON ap.AGR_Header_RecID = al.AGR_Header_RecID
LEFT JOIN
    (SELECT ar.parent_recid
    ,ar.Month
    ,ar.Year
    ,ar.Rev
    ,ac.Hours AS hours
    ,ac.Cost AS labor_cost
    ,ar.child_agreeements
    ,ar.count
    ,ar.child_agreeement_types
    ,addi.prod_cost
    FROM
        (SELECT ah.parent_recid
        ,ai.Month_Nbr AS month
        ,ai.Year_Nbr AS year
        ,CAST(SUM(ai.Monthly_Inv_Amt - ai.Monthly_SalesTax_Amt) AS NUMERIC (18, 2)) AS rev
        ,SUBSTRING(
              (SELECT ', '+ahc.AGR_Name  AS [text()]
              FROM agr_header ahc
              WHERE ahc.parent_recid = ah.parent_Recid
              ORDER BY ahc.AGR_Name
              For XML PATH (''))
            , 2, 1000) [child_agreeements]
        ,SUBSTRING(
              (SELECT ', '+atc.AGR_Type_Desc  AS [text()]
              FROM agr_header ahc2
              INNER JOIN AGR_Type atc ON atc.AGR_Type_RecID = ahc2.AGR_Type_RecID
              WHERE ahc2.parent_recid = ah.parent_Recid
              ORDER BY atc.AGR_Type_Desc
              For XML PATH (''))
            , 2, 1000) [child_agreeement_types]
        ,COUNT(1) AS count
        FROM agr_header AS ah
        INNER JOIN agr_invoice_amt AS ai ON ah.AGR_Header_RecID = ai.AGR_Header_RecID
        GROUP BY ah.parent_Recid
        ,ai.Month_Nbr
        ,ai.Year_Nbr) AS ar
        LEFT JOIN
            (SELECT ah.parent_Recid
            ,DATEPART(MONTH, te.Date_Start) AS month
            ,DATEPART(YEAR, te.Date_Start) AS year
            ,SUM(te.AgrHrsCovered) AS hours
            ,CAST(SUM(te.AgrHrsCovered * te.Hourly_Cost_Decimal) AS NUMERIC (18, 2)) AS cost
            FROM v_rpt_time AS te
            INNER JOIN agr_header AS ah ON te.Agr_Header_RecID = ah.AGR_Header_RecID
            WHERE te.date_start >= DATEADD(mm, -6, CURRENT_TIMESTAMP)
            AND te.Agr_Header_RecID IS NOT NULL
            AND te.AgrHrsCovered IS NOT NULL
            GROUP BY ah.parent_Recid
            ,DATEPART(MONTH, te.Date_Start)
            ,DATEPART(YEAR, te.Date_Start)) AS ac ON ar.parent_recid = ac.parent_recid AND ar.Month = ac.Month
                                                        AND ar.Year = ac.Year
        LEFT JOIN
            (SELECT SUM(vadi.Extended_Cost_Amount) AS prod_cost
            ,ahp.parent_Recid
            ,vadi.agr_month AS month
            ,vadi.agr_year AS year
            FROM agr_header ahp
            INNER JOIN iv_product vadi ON vadi.AGR_Header_RecID = ahp.AGR_Header_RecID
            GROUP BY ahp.parent_Recid
            ,vadi.agr_month
            ,vadi.agr_year) AS addi ON addi.parent_Recid = ar.parent_Recid AND ar.year = addi.year AND ar.month = addi.month
    ) AS child ON child.parent_recid = al.AGR_Header_RecID AND child.month = ap.month AND child.year = ap.year





WHERE ah.parent_Recid IS NULL 
  AND ap.agr_date_inv > DATEADD(YEAR, -2, CURRENT_TIMESTAMP)
  AND ap.agr_date_inv <= CURRENT_TIMESTAMP
  AND o.Owner_Level_Name IN ('Australia', 'Alex', 'Adam')
  AND al.Agreement_Status = 'active'
  AND al.Company_Name = 'Client A'
)


SELECT *
FROM TMP
WHERE rn = 1