sql - 多年的每日总和(Daily sums for a range of years)

Question

The following code is functional but I would like to add a constraint to the [COGS] sum.

(以下代码可以正常工作，但是我想对[COGS]总和添加一个约束。)

The constraint is: 'Nz(i.VENDOR_ACTUAL_PRICE, 0)*Nz(s.quantity, 0) WHERE INVENTORY.CONSIGNMENT = "No" AS COGS,` How can the constraint be added?

(约束是：'Nz（i.VENDOR_ACTUAL_PRICE，0）* Nz（s.quantity，0）WHERE INVENTORY.CONSIGNMENT =“ No”作为销，如何添加约束？)

SELECT a.[Month / Day / Year],
           Round(Sum(a.[Gross Sales]), 2) AS [Gross Sales],
           Round(Sum(a.[COGS]), 2) AS COGS,
           Round(Sum(a.[Sales Margin]), 2) AS [Sales Margin],
           Round([Sales Margin]/[Gross Sales], 2) AS [Profit Margin]
    FROM
      (SELECT Format(DatePart("m", sale_date), "00") & "/" & Format(DatePart("d", sale_date), "00") & "/" & DatePart("yyyy", sale_date) AS [Month / Day / Year],
              Format(DatePart("d", s.sale_date), "00") AS [Sale Day],
              Format(DatePart("m", s.sale_date), "00") AS [Sale Month],
              DatePart("yyyy", s.sale_date) AS [Sale Year],

              Nz(s.SELLING_PRICE * s.quantity, 0) AS [Gross Sales],
              Nz(i.VENDOR_ACTUAL_PRICE, 0)*Nz(s.quantity, 0) AS COGS,
              Nz(s.SELLING_PRICE * s.quantity, 0) - Nz(i.VENDOR_ACTUAL_PRICE * s.quantity, 0) AS [Sales Margin]

       FROM INVENTORY AS i
       INNER JOIN SALES_RECEIPT AS s ON i.INVENTORY_ID = s.INVENTORY_ID
       WHERE DatePart("m", sale_date) BETWEEN 1 AND 12
         AND DatePart("d", sale_date) BETWEEN 1 AND 31
         AND DatePart("yyyy", sale_date) BETWEEN 2000 AND 2100) AS a
    GROUP BY a.[Month / Day / Year],
             a.[Sale Month],
             a.[Sale Day],
             a.[Sale Year]
    ORDER BY a.[Sale Year],
             a.[Sale Month],
             a.[Sale Day];

  ask by Code Mechanik translate from so

Answer 1

等待大神答复