文章/答案/技术大牛

发布

社区首页 >问答首页 >停止在条件下带来重复的结果

问停止在条件下带来重复的结果
EN

Stack Overflow用户

提问于 2022-05-11 01:28:06

回答 1查看 36关注 0票数 0

即使我的查询行为正常，但我在数据库中无法修复的记录中有一个问题。所以，我想在查询中修复它。

问题是:作为图像附件，对于一个特定的客户，由于销售人员的不同，存在重复的is。

我只需要为这些ID带来一个结果，而不是销售人员。

我试过：

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'

但这不管用。

这就是结果(这里没有粘贴所有的列)，并且一幅图片可能是有用的，因为它的形式，它附在最后：

id Company_Name AGR_Name agr_type_desc Valid_flag date_start date_end billing_cycle Billing_Amount Agreement_Status销售人员464-2 2022客户端A客户端A- BaaS服务-用户主服务协议1 1/06/2020空月150 Active Adam 464-2-2022客户端A客户端A- BaaS服务-用户主服务协议1/06/2020空每月活动的Alex 464-6-2021客户端A- BaaS服务-用户主服务协议1 1/06/2020空月150 Active Adam 464-6-2021 Client A客户端A- BaaS服务-用户主服务协议1 1/06/2020空月150 Active Alex 464-10-2020 Client A客户端A- BaaS服务-用户主服务协议1 1/06/2020无效每月活动Adam 464-10-2020客户端A客户端A- BaaS服务-用户主服务协议1/06/2020空月150个活动亚历克斯464-4-2021客户端A客户端A- BaaS服务-用户主服务协议1 1/06/2020空月活动Adam 464-4-2021客户端A客户端A- BaaS服务-用户主服务协议1 1/06/2020空每月150 Active Alex 464-8-2020客户端A客户端- BaaS服务-用户主服务协议1 1/06/2020空每月150Active Adam 464-8-2020客户端A客户端A- BaaS服务-用户主服务协议1 1/06/2020空月150 Active Alex 464-11-2021 Client A客户机A- BaaS服务-用户主服务协议1 1/06/2020空月150 Active Adam 464-11-2021客户机A客户端A- BaaS服务-用户主服务协议1/06/2020空月150 Active Alex 464-3-2021客户机A客户端A- BaaS服务-用户主服务协议1 1/06/2020空月150 Active Adam 464-3-2021 Client A客户端A- BaaS服务-用户主服务协议1 1/06/2020空每月150 Active Alex 464-7-2020客户端A客户端A- BaaS服务-用户主服务协议1 1/06/2020无效每月活动Adam 464-7-2020客户端A客户端A- BaaS服务-用户主服务协议1/06/2020空月150个活动亚历克斯464-4-2022客户端A客户端A- BaaS服务-用户主服务协议1 1/06/2020空月活动Adam 464-4-2022客户端A客户端A- BaaS服务-用户主服务协议1 1/06/2020空每月活动亚历克斯464-1-2021客户端A客户端A- BaaS服务-用户主服务协议1 1/06/2020年空每月150Active Adam 464-1-2021 Client A客户端A- BaaS服务-用户主服务协议1 1/06/2020空月150 Active Alex 464-8-2021 Client A客户端A- BaaS服务-用户主服务协议1 1/06/2020空月150 Active Adam 464-8-2021客户端A客户端A- BaaS服务-用户主服务协议1/06/2020空每月活动亚历克斯150

查询结果

sql-server-2008

ssms

回答 1

Stack Overflow用户

回答已采纳

发布于 2022-05-11 04:00:34

基本上，我使用了函数ROW_NUMBER，如"id“列前面的行所示。
如果您不想在结果中显示“行号列”或"rn“，则可以将以下查询中的*替换为除"rn”之外的所有前面提到的列名。
如果这对你有用的话请告诉我。

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

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/72194464

复制

相似问题

问停止在条件下带来重复的结果
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问停止在条件下带来重复的结果EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问停止在条件下带来重复的结果
EN