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
CodePudding user response：
- Basically, I have used the function ROW_NUMBER, as shown in the line before your "id" column.
- 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 = 1to every column name you have previously mentioned except "rn". - 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