SQL:仅将月份和年份列与日期进行比较 [英] SQL: Comparing Dates with only Month and Year columns

问题描述

我有一个表MonthlyShipments看起来像这样:

I have a table MonthlyShipments that looks like:

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子句当前看起来像:

I want to sum the quantities of the past 3 months, excluding the current month. My where clause currently looks like:

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())

它有效，但丑陋又侮辱人.关于使其更漂亮有什么建议吗?非常感谢！

It works, but its ugly and insulting. Any advice on making it prettier? Much appreciate!

推荐答案

好多了...

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

但是可以将嵌套的DATEADD用作计算列和索引列

however the nested DATEADD can be made a computed and indexed column

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

这篇关于SQL:仅将月份和年份列与日期进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！