主頁 > 軟體工程 > 停止以根據條件帶來重復的結果

停止以根據條件帶來重復的結果

2022-05-12 18:37:54 軟體工程

即使我的查詢表現正常，但我在記錄中存在無法在資料庫中修復的問題。所以，我想在查詢中修復它。

問題是：如附圖所示，對于一個特定的客戶，由于銷售人員不同，存在重復的 ID。

我只需要為這些 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 NULL 每月 150 活動 Adam 464-2-2022 客戶 A 客戶 A - BaaS 服務 - 用戶主服務協議 1 1/06/2020 NULL 每月 150 活躍 Alex 464-6-2021 客戶 A 客戶 A - BaaS 服務 - 用戶主服務協議 1 1/06/2020 NULL 每月 150 活躍 Adam 464-6- 2021 客戶 A 客戶 A - BaaS 服務 - 用戶主服務協議 1 2020 年 1 月 6 日 NULL 每月 150 活動 Alex 464-10-2020 客戶 A 客戶 A - BaaS 服務 - 用戶主服務協議 1 1/06/2020 NULL 每月 150活躍亞當 464-10-2020 客戶 A客戶端 A - BaaS 服務 - 用戶主服務協議 1 1/06/2020 NULL 每月 150 活動 Alex 464-4-2021 客戶端 A 客戶端 A - BaaS 服務 - 用戶主服務協議 1 1/06/2020 NULL 每月 150 活動 Adam 464 -4-2021 客戶 A 客戶 A - BaaS 服務 - 用戶主服務協議 1 1/06/2020 NULL 每月 150 活動 Alex 464-8-2020 客戶 A 客戶 A - BaaS 服務 - 用戶主服務協議 1 1/06/2020 NULL 每月 150 活躍 Adam 464-8-2020 客戶 A 客戶 A - BaaS 服務 - 用戶主服務協議 1 1/06/2020 NULL 每月 150 活躍 Alex 464-11-2021 客戶 A 客戶 A - BaaS 服務 - 用戶主服務協議1 1/06/2020 NULL 每月 150 活動 Adam 464-11-2021 客戶端 A 客戶端 A - BaaS 服務 - 用戶主服務協議 1 1/06/2020 NULL 每月 150 活躍 Alex 464-3-2021 客戶 A 客戶 A - BaaS 服務 - 用戶 主服務協議 1 1/06/2020 NULL 每月 150 活躍 Adam 464-3-2021 客戶 A 客戶A - BaaS 服務 - 用戶主服務協議 1 1/06/2020 NULL 每月 150 活動 Alex 464-7-2020 客戶端 A 客戶端 A - BaaS 服務 - 用戶主服務協議 1 1/06/2020 NULL 每月 150 活動 Adam 464- 7-2020 客戶 A 客戶 A - BaaS 服務 - 用戶主服務協議 1 1/06/2020 NULL 每月 150 活躍 Alex 464-4-2022 客戶 A 客戶 A - BaaS 服務 - 用戶主服務協議 1 1/06/2020 NULL每月 150 活躍 Adam 464-4-2022 客戶 A 客戶 A - BaaS 服務 - 用戶主服務協議 12020 年 1 月 6 日 NULL 每月 150 活躍 Alex 464-1-2021 客戶 A 客戶 A - BaaS 服務 - 用戶主服務協議 1 2020 年 1 月 6 日 NULL 每月 150 活躍 Adam 464-1-2021 客戶 A 客戶 A - BaaS 服務- 用戶主服務協議 1 1/06/2020 NULL 每月 150 活躍 Alex 464-8-2021 客戶 A 客戶 A - BaaS 服務 - 用戶主服務協議 1 1/06/2020 NULL 每月 150 活躍 Adam 464-8-2021 客戶客戶 A - BaaS 服務 - 用戶主服務協議 1 1/06/2020 NULL Monthly 150 Active Alex2020 年 1 月 6 日 NULL 每月 150 活躍 Alex 464-8-2021 客戶 A 客戶 A - BaaS 服務 - 用戶主服務協議 1 2020 年 1 月 6 日 NULL 每月 150 活躍 Adam 464-8-2021 客戶 A 客戶 A - BaaS 服務- 用戶主服務協議 1 1/06/2020 NULL Monthly 150 Active Alex2020 年 1 月 6 日 NULL 每月 150 活躍 Alex 464-8-2021 客戶 A 客戶 A - BaaS 服務 - 用戶主服務協議 1 2020 年 1 月 6 日 NULL 每月 150 活躍 Adam 464-8-2021 客戶 A 客戶 A - BaaS 服務- 用戶主服務協議 1 1/06/2020 NULL Monthly 150 Active Alex

查詢結果

uj5u.com熱心網友回復：

  1. 基本上，我使用了函式 ROW_NUMBER，如“id”列之前的行所示。
  2. 如果您不想在結果中顯示“行號列”或“rn”，您可以將*以下查詢中的替換SELECT * FROM TMP WHERE rnk = 1為您之前提到的除“rn”之外的每個列名。
  3. 讓我知道這是否適合您。
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

轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/472568.html

標籤：sql-server-2008 短信

上一篇：使用訂閱初始化物件

下一篇：如何將reactjs狀態用于組件類？

標籤雲
其他(157675) Python(38076) JavaScript(25376) Java(17977) C(15215) 區塊鏈(8255) C＃(7972) AI(7469) 爪哇(7425) MySQL(7132) html(6777) 基礎類(6313) sql(6102) 熊猫(6058) PHP(5869) 数组(5741) R(5409) Linux(5327) 反应(5209) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4554) 数据框(4311) css(4259) 节点.js(4032) C語言(3288) json(3245) 列表(3129) 扑(3119) C++語言(3117) 安卓(2998) 打字稿(2995) VBA(2789) Java相關(2746) 疑難問題(2699) 细绳(2522) 單片機工控(2479) iOS(2429) ASP.NET(2402) MongoDB(2323) 麻木的(2285) 正则表达式(2254) 字典(2211) 循环(2198) 迅速(2185) 擅长(2169) 镖(2155) 功能(1967) .NET技术(1958) Web開發(1951) python-3.x(1918) HtmlCss(1915) 弹簧靴(1913) C++(1909) xml(1889) PostgreSQL(1872) .NETCore(1853) 谷歌表格(1846) Unity3D(1843) for循环(1842)

熱門瀏覽
  • Git本地庫既關聯GitHub又關聯Gitee

    創建代碼倉庫 使用gitee舉例（github和gitee差不多） 1.在gitee右上角點擊+，選擇新建倉庫 ? 2.選擇填寫倉庫資訊，然后進行創建 ? 3.服務端已經準備好了，本地開始作準備 （1）Git 全域設定 git config --global user.name "成鈺" git c ......

    uj5u.com 2020-09-10 05:04:14 more
  • CODING DevOps 代碼質量實戰系列第二課，相約周三

    隨著 ToB（企業服務）的興起和 ToC（消費互聯網）產品進入成熟期，線上故障帶來的損失越來越大，代碼質量越來越重要，而「質量內建」正是 DevOps 核心理念之一。**《DevOps 代碼質量實戰（PHP 版）》**為 CODING DevOps 代碼質量實戰系列的第二課，同時也是本系列的 PHP ......

    uj5u.com 2020-09-10 05:07:43 more
  • 推薦Scrum書籍

    推薦Scrum書籍 直接上干貨，推薦書籍清單如下（推薦有順序的哦） Scrum指南 Scrum精髓 Scrum敏捷軟體開發 Scrum捷徑 硝煙中的Scrum和XP : 我們如何實施Scrum 敏捷軟體開發:Scrum實戰指南 Scrum要素 大規模Scrum:大規模敏捷組織的設計 用戶故事地圖 用 ......

    uj5u.com 2020-09-10 05:07:45 more
  • CODING DevOps 代碼質量實戰系列最后一課，周四發車

    隨著 ToB（企業服務）的興起和 ToC（消費互聯網）產品進入成熟期，線上故障帶來的損失越來越大，代碼質量越來越重要，而「質量內建」正是 DevOps 核心理念之一。 **《DevOps 代碼質量實戰（Java 版）》**為 CODING DevOps 代碼質量實戰系列的最后一課，同時也是本系列的 ......

    uj5u.com 2020-09-10 05:07:52 more
  • 敏捷軟體工程實踐書籍

    Scrum轉型想要做好，第一步先了解并真正落實Scrum，那么我推薦的Scrum書籍是要看懂并實踐的。第二步是團隊的工程實踐要做扎實。 下面推薦工程實踐書單： 重構：改善既有代碼的設計 決議極限編程 : 擁抱變化 代碼整潔代碼 程式員的職業素養 修改代碼的藝術 撰寫可讀代碼的藝術 測驗驅動開發 : ......

    uj5u.com 2020-09-10 05:07:55 more
  • Jenkins+svn+nginx實作windows環境自動部署vue前端專案

    前面文章介紹了Jenkins+svn+tomcat實作自動化部署，現在終于有空抽時間出來寫下Jenkins+svn+nginx實作自動部署vue前端專案。 jenkins的安裝和配置已經在前面文章進行介紹，下面介紹實作vue前端專案需要進行的哪些額外的步驟。 注意：在安裝jenkins和nginx的 ......

    uj5u.com 2020-09-10 05:08:49 more
  • CODING DevOps 微服務專案實戰系列第一課，明天等你

    CODING DevOps 微服務專案實戰系列第一課**《DevOps 微服務專案實戰：DevOps 初體驗》**將由 CODING DevOps 開發工程師 王寬老師 向大家介紹 DevOps 的基本理念，并探討為什么現代開發活動需要 DevOps，同時將以 eShopOnContainers 項 ......

    uj5u.com 2020-09-10 05:09:14 more
  • CODING DevOps 微服務專案實戰系列第二課來啦！

    近年來，工程專案的結構越來越復雜，需要接入合適的持續集成流水線形式，才能滿足更多變的需求，那么如何優雅地使用 CI 能力提升生產效率呢？CODING DevOps 微服務專案實戰系列第二課 《DevOps 微服務專案實戰：CI 進階用法》 將由 CODING DevOps 全堆疊工程師 何晨哲老師 向 ......

    uj5u.com 2020-09-10 05:09:33 more
  • CODING DevOps 微服務專案實戰系列最后一課，周四開講！

    隨著軟體工程越來越復雜化，如何在 Kubernetes 集群進行灰度發布成為了生產部署的”必修課“，而如何實作安全可控、自動化的灰度發布也成為了持續部署重點關注的問題。CODING DevOps 微服務專案實戰系列最后一課：**《DevOps 微服務專案實戰：基于 Nginx-ingress 的自動 ......

    uj5u.com 2020-09-10 05:10:00 more
  • CODING 儀表盤功能正式推出，實作作業資料可視化！

    CODING 儀表盤功能現已正式推出！該功能旨在用一張張統計卡片的形式，統計并展示使用 CODING 中所產生的資料。這意味著無需額外的設定，就可以收集歸納寶貴的作業資料并予之量化分析。這些海量的資料皆會以圖表或串列的方式躍然紙上，方便團隊成員隨時查看各專案的進度、狀態和指標，云端協作迎來真正意義上 ......

    uj5u.com 2020-09-10 05:11:01 more
最新发布
  • windows系統git使用ssh方式和gitee/github進行同步

    使用git來clone專案有兩種方式：HTTPS和SSH：
    HTTPS：不管是誰，拿到url隨便clone，但是在push的時候需要驗證用戶名和密碼；
    SSH：clone的專案你必須是擁有者或者管理員，而且需要在clone前添加SSH Key。SSH 在push的時候，是不需要輸入用戶名的，如果配置... ......

    uj5u.com 2023-04-19 08:41:12 more
  • windows系統git使用ssh方式和gitee/github進行同步

    使用git來clone專案有兩種方式：HTTPS和SSH：
    HTTPS：不管是誰，拿到url隨便clone，但是在push的時候需要驗證用戶名和密碼；
    SSH：clone的專案你必須是擁有者或者管理員，而且需要在clone前添加SSH Key。SSH 在push的時候，是不需要輸入用戶名的，如果配置... ......

    uj5u.com 2023-04-19 08:35:34 more
  • 2023年農牧行業6大CRM系統、5大場景盤點

    在物聯網、大資料、云計算、人工智能、自動化技術等現代資訊技術蓬勃發展與逐步成熟的背景下，數字化正成為農牧行業供給側結構性變革與高質量發展的核心驅動因素。因此，改造和提升傳統農牧業、開拓創新現代智慧農牧業，加快推進農牧業的現代化、資訊化、數字化建設已成為農牧業發展的重要方向。 當下，企業數字化轉型已經 ......

    uj5u.com 2023-04-18 08:05:44 more
  • 2023年農牧行業6大CRM系統、5大場景盤點

    在物聯網、大資料、云計算、人工智能、自動化技術等現代資訊技術蓬勃發展與逐步成熟的背景下，數字化正成為農牧行業供給側結構性變革與高質量發展的核心驅動因素。因此，改造和提升傳統農牧業、開拓創新現代智慧農牧業，加快推進農牧業的現代化、資訊化、數字化建設已成為農牧業發展的重要方向。 當下，企業數字化轉型已經 ......

    uj5u.com 2023-04-18 08:00:18 more
  • 計算機組成原理—存盤器

    計算機組成原理—硬體結構 二、存盤器 1.概述 存盤器是計算機系統中的記憶設備，用來存放程式和資料 1.1存盤器的層次結構 快取-主存層次主要解決CPU和主存速度不匹配的問題，速度接近快取 主存-輔存層次主要解決存盤系統的容量問題，容量接近與價位接近于主存 2.主存盤器 2.1概述 主存與CPU的聯 ......

    uj5u.com 2023-04-17 08:20:31 more
  • 談一談我對協同開發的一些認識

    如今各互聯網公司普通都使用敏捷開發，采用小步快跑的形式來進行專案開發。如果是小專案或者小需求，那一個開發可能就搞定了。但對于電商等復雜的系統，其功能多，結構復雜，一個人肯定是搞不定的，所以都是很多人來共同開發維護。以我曾經待過的商城團隊為例，光是后端開發就有七十多人。 為了更好地開發這類大型系統，往 ......

    uj5u.com 2023-04-17 08:18:55 more
  • 專案管理PRINCE2核心知識點整理

    PRINCE2，即 PRoject IN Controlled Environment（受控環境中的專案）是一種結構化的專案管理方法論，由英國政府內閣商務部（OGC）推出，是英國專案管理標準。
    PRINCE2 作為一種開放的方法論，是一套結構化的專案管理流程，描述了如何以一種邏輯性的、有組織的方法，... ......

    uj5u.com 2023-04-17 08:18:51 more
  • 談一談我對協同開發的一些認識

    如今各互聯網公司普通都使用敏捷開發，采用小步快跑的形式來進行專案開發。如果是小專案或者小需求，那一個開發可能就搞定了。但對于電商等復雜的系統，其功能多，結構復雜，一個人肯定是搞不定的，所以都是很多人來共同開發維護。以我曾經待過的商城團隊為例，光是后端開發就有七十多人。 為了更好地開發這類大型系統，往 ......

    uj5u.com 2023-04-17 08:18:00 more
  • 專案管理PRINCE2核心知識點整理

    PRINCE2，即 PRoject IN Controlled Environment（受控環境中的專案）是一種結構化的專案管理方法論，由英國政府內閣商務部（OGC）推出，是英國專案管理標準。
    PRINCE2 作為一種開放的方法論，是一套結構化的專案管理流程，描述了如何以一種邏輯性的、有組織的方法，... ......

    uj5u.com 2023-04-17 08:17:55 more
  • 計算機組成原理—存盤器

    計算機組成原理—硬體結構 二、存盤器 1.概述 存盤器是計算機系統中的記憶設備，用來存放程式和資料 1.1存盤器的層次結構 快取-主存層次主要解決CPU和主存速度不匹配的問題，速度接近快取 主存-輔存層次主要解決存盤系統的容量問題，容量接近與價位接近于主存 2.主存盤器 2.1概述 主存與CPU的聯 ......

    uj5u.com 2023-04-17 08:12:06 more

Copyright © 2010-2020 有解無憂