SQL:仅比较日期与月份和年份列

Question

我有一个表MonthlyShipments，如下所示:

partnumber | quantity | month | year |
part1      |       12 |     6 | 2011 |
part1      |       22 |     5 | 2011 |
part1      |       32 |     4 | 2011 |
part1      |       42 |     3 | 2011 |
part1      |       52 |     2 | 2011 |

我想对过去 3 个月的数量进行求和，不包括当月。我的 where 子句目前如下所示:

where
  MonthlyShipments.Month <> MONTH(GETDATE()) AND 
  CAST(
    (CAST(MonthlyShipments.Month as nvarchar(2)) + 
    '-01-' + 
    CAST(MonthlyShipments.Year as nvarchar(4))) as DateTime)
  > DATEADD(m, -4, GETDATE())

它确实有效，但它丑陋且具有侮辱性。有什么建议可以让它变得更漂亮吗？非常感谢!

Answer 1

也好不了多少...

DATEDIFF(
    month,
    DATEADD(Year, MonthlyShipments.Year-1900,
        DATEADD(Month, MonthlyShipments.Month-1, 0)
           ),
    GETDATE()
    ) BETWEEN 1 AND 3

但是，嵌套的 DATEADD 可以成为计算列和索引列

ALTER TABLE MonthlyShipments ADD
    ShipDate AS DATEADD(Year, MonthlyShipments.Year-1900,
            DATEADD(Month, MonthlyShipments.Month-1, 0)
               )

这给出了

WHERE DATEDIFF(month, ShipDate, GETDATE()) BETWEEN 1 AND 3