带有SUM（CASE）的SQL聚合函数

甜琼斯

我了解我无法在聚合函数中执行子查询。我该如何解决这个问题。我需要能够从另一个表中找出当前会计期间，并将其传递给我的查询。我正在尝试计算月度至今的数字。任何想法表示赞赏。

SELECT SUM(CASE
             WHEN CRMBNB_DATE_1.AccountingFiscalPeriod = 
                            /*Problem subquery*/
                            (SELECT AccountingFiscalPeriod
                            FROM   crmbnb_date
                            WHERE  DAY = cast(CONVERT(VARCHAR(10), getdate(), 110) AS DATE))
                  AND ( CRMBNB_DATE_1.FiscalYear = YEAR(DATEADD(mm, -3, GETDATE())) ) THEN BL_Amount
             ELSE 0
           END) AS [MTD Billing]
FROM   CRMBNB_BILLINGS
       INNER JOIN CRMBNB_DATE AS CRMBNB_DATE_1
         ON CRMBNB_BILLINGS.InvoiceDate = CRMBNB_DATE_1.Day
       INNER JOIN CRMBNB_EMPLOYEE AS CRMBNB_EMPLOYEE_1
         ON CRMBNB_BILLINGS.BL_EmployeeNo = CRMBNB_EMPLOYEE_1.EmployeeNo
       INNER JOIN CRMBNB_SALESREGION AS CRMBNB_SALESREGION_1
         ON CRMBNB_EMPLOYEE_1.Employee_Region = CRMBNB_SALESREGION_1.Region
WHERE  ( CRMBNB_SALESREGION_1.Area IN ( 'OGS' ) )
--AND (CRMBNB_DATE_1.AccountingFiscalPeriod IN (10)) AND (CRMBNB_DATE_1.FiscalYear IN (2013)) 
--AND (CRMBNB_BILLINGS.BL_ProductLine IN (@PRODUCTLINE))
GROUP  BY CRMBNB_SALESREGION_1.Area,
          CRMBNB_SALESREGION_1.Region,
          CRMBNB_DATE_1.AccountingFiscalPeriod,
          CRMBNB_DATE_1.FiscalYear,
          CRMBNB_SALESREGION_1.RegionDesc,
          CRMBNB_BILLINGS.BL_ProductLine

这是错误

消息130，级别15，状态1，第4行无法对包含聚集或子查询的表达式执行聚集功能。

费尔南多·卡努尔

您可以使用函数代替“子查询”，例如：

    SELECT SUM(CASE
         WHEN CRMBNB_DATE_1.AccountingFiscalPeriod = 
                      **Fn_XXX()** THEN BL_Amount
         ELSE 0
       END) AS [MTD Billing]
 FROM   CRMBNB_BILLINGS
   INNER JOIN CRMBNB_DATE AS CRMBNB_DATE_1
     ON CRMBNB_BILLINGS.InvoiceDate = CRMBNB_DATE_1.Day
   INNER JOIN CRMBNB_EMPLOYEE AS CRMBNB_EMPLOYEE_1
     ON CRMBNB_BILLINGS.BL_EmployeeNo = CRMBNB_EMPLOYEE_1.EmployeeNo
   INNER JOIN CRMBNB_SALESREGION AS CRMBNB_SALESREGION_1
     ON CRMBNB_EMPLOYEE_1.Employee_Region = CRMBNB_SALESREGION_1.Region
    WHERE  ( CRMBNB_SALESREGION_1.Area IN ( 'OGS' ) )
  --AND (CRMBNB_DATE_1.AccountingFiscalPeriod IN (10)) AND (CRMBNB_DATE_1.FiscalYear IN (2013)) 
     --AND (CRMBNB_BILLINGS.BL_ProductLine IN (@PRODUCTLINE))
         GROUP  BY CRMBNB_SALESREGION_1.Area,
      CRMBNB_SALESREGION_1.Region,
      CRMBNB_DATE_1.AccountingFiscalPeriod,
      CRMBNB_DATE_1.FiscalYear,
      CRMBNB_SALESREGION_1.RegionDesc,
      CRMBNB_BILLINGS.BL_ProductLine

在您的功能中，您可以选择，更新，执行所需的所有操作...

希望这行得通...

问候...

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。