查询以按月份返回24个月的销售历史记录，在其中使用month()子句多次返回当年的结果

Question

我想按月报告过去24个月的销售额。下个月，第25个月的一组数字应该会从报告中删除，并再次反映过去24个月的情况。我不知道如何编写查询来处理年份更改。这是我的东西。

select  [Amount] * -1 as 'Parts Not Sold On Service Order Current', 0 as 'Parts Sold     On Service Order', [Document Date]
from    [G_L Entry]
where   [G_L Account No_] between '40000' and '49999'
and     [Dimension code] = 'par'
and     [Document No_] not like 'PSV%'
AND     YEAR([Document Date]) = YEAR(CURRENT_TIMESTAMP)
AND     MONTH([Document Date]) = MONTH(CURRENT_TIMESTAMP)
union
    select  [Amount] * -1 as 'Parts Not Sold On Service Order Current -1', 0 as 'Parts Sold     On Service Order', [Document Date]
from    [G_L Entry]
where   [G_L Account No_] between '40000' and '49999'
and     [Dimension code] = 'par'
and     [Document No_] not like 'PSV%'
AND     YEAR([Document Date]) = YEAR(CURRENT_TIMESTAMP) 
AND     MONTH([Document Date]) = MONTH(CURRENT_TIMESTAMP) -1
union
    select  [Amount] * -1 as 'Parts Not Sold On Service Order Current -2', 0 as 'Parts Sold     On Service Order', [Document Date]
from    [G_L Entry]
where   [G_L Account No_] between '40000' and '49999'
and     [Dimension code] = 'par'
and     [Document No_] not like 'PSV%'
AND     YEAR([Document Date]) = YEAR(CURRENT_TIMESTAMP) 
AND     MONTH([Document Date]) = MONTH(CURRENT_TIMESTAMP) -2

在进入前一年后，这开始重复同一年的结果。自上一年以来，月份可能只有5个月。下个月是6个月。如何编写查询来处理此问题？

Answer 1

假设您的查询已经在其他方面按照您希望的方式工作，请尝试将以下内容添加到WHERE语句中：

AND DateDiff(Month, [Document Date], CURRENT_TIMESTAMP) < 24

你可能需要玩一点，才能让它适合你的特定报告（例如，"<23"、"<25"等）

我不是一个读心术的人，但将其集成到当前的WHERE语句中可能会给你想要的东西，并将你的查询缩短为以下内容（没有union）：

select  [Amount] * -1 as 'Parts Not Sold On Service Order Current', 0 as 'Parts Sold On Service Order', [Document Date]
from    [G_L Entry]
where   [G_L Account No_] between '40000' and '49999'
and     [Dimension code] = 'par'
and     [Document No_] not like 'PSV%'
AND     DateDiff(Month, [Document Date], CURRENT_TIMESTAMP) < 24