在Microsoft Access中创建查询差异(差异)分析

Question

我有一项特定的任务需要帮助。

从本质上讲，我有一个销售表：

Product_ID/No   Product_Name    Date          Revenue
       A              APPLE     01-01-20       50
       B              BANANA    01-01-20       100
       A              APPLE     15-12-19       50
       B              BANANA    15-12-19       150
       C              CHERRY    15-12-19       50
       A              APPLE     01-01-20       60

我需要每月从该表创建一个差异报告(差异/移动)，其中包含以下标题：

Product_ID/No   Product_Name    Current_Month   Previous_Month  Variance
    A             Apple             110         50               60
    B             Banana            100         150             -50
    C             Cherry            50          -50

问题：

我该怎么做呢？

这将是一系列SQL语句吗？

任何建议都将不胜感激。

谢谢

Answer 1

您可以按产品分组并使用条件聚合：

SELECT 
  [Product_ID/No], [Product_Name], 
  SUM(IIF(DATESERIAL(YEAR(DATE()), MONTH(DATE()), 1) = DATESERIAL(YEAR([Date]), MONTH([Date]), 1), Revenue, NULL)) AS Current_Month,
  SUM(IIF(DATESERIAL(YEAR(DATEADD("m", -1, DATE())), MONTH(DATEADD("m", -1, DATE())), 1) = DATESERIAL(YEAR([Date]), MONTH([Date]), 1), Revenue, NULL)) AS Previous_Month,
  Nz(Current_Month)- Nz(Previous_Month) AS Variance
FROM Sales
GROUP BY [Product_ID/No], [Product_Name]

结果：

Product_ID/No   Product_Name    Current_Month   Previous_Month  Variance
A               APPLE           110             50              60
B               BANANA          100             150             -50
C               CHERRY                          50              -50

Answer 2

此查询适用于SQL Server：

WITH Temp
AS (SELECT [Product_ID/No],
           [Product_Name],
           ISNULL(SUM(IIF(DATEPART(MONTH, GETDATE()) = DATEPART(MONTH, [Date]), Revenue, NULL)), 0) AS Current_Month,
           ISNULL(SUM(IIF(DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) = DATEPART(MONTH, [Date]), Revenue, NULL)), 0) AS Previous_Month
    FROM dbo.Sales
    GROUP BY [Product_ID/No],
             [Product_Name])
SELECT *,
       (Temp.Current_Month - Temp.Previous_Month) AS Variance
FROM Temp;