选择一年前后的数据

Question

我正在使用以下查询来提取一年的未来订单。有什么方法可以在今天之前和之后一年提取数据吗？

SELECT ord_ln.prdnum AS 'Product', ord.cusnam, product.prddsc AS 'Description',
       ord_ln.stdnetwgt AS 'Case_Weight', ord_ln.prdlincod AS 'Line_Code',
       ord_ln.ordqty AS 'Order_Qty', ord.plnshpdat AS 'Plan_Ship_Date'
FROM velocity.dbo.ord ord,
     velocity.dbo.ord_ln ord_ln,
     velocity.dbo.product product
WHERE ord.ordtky = ord_ln.ordtky
  AND product.prdtky = ord_ln.prdtky
  AND ((ord.plnshpdat Between DATEADD(dd,0,GETDATE()) And DATEADD(dd,365,GETDATE())))
ORDER BY ord_ln.prdlincod

提前谢谢。

Answer 1

在查询中使用之前，声明2个变量并计算年份。（以防万一你关心性能；）

Declare @NextYearDate Date= DATEADD(YY,1,getdate())
Declare @PrevYearDate Date= DATEADD(YY,-1,getdate())
SELECT ord_ln.prdnum AS 'Product', ord.cusnam, product.prddsc AS 'Description',
   ord_ln.stdnetwgt AS 'Case_Weight', ord_ln.prdlincod AS 'Line_Code',
   ord_ln.ordqty AS 'Order_Qty', ord.plnshpdat AS 'Plan_Ship_Date'
FROM velocity.dbo.ord ord,
   velocity.dbo.ord_ln ord_ln,
   velocity.dbo.product product
WHERE ord.ordtky = ord_ln.ordtky
   AND product.prdtky = ord_ln.prdtky
   AND (ord.plnshpdat Between @PrevYearDate and @NextYearDate)
ORDER BY ord_ln.prdlincod

Answer 2

这就是我编写查询的方式：

SELECT ol.prdnum as Product, o.cusnam, p.prddsc as Description,
       ol.stdnetwgt as Case_Weight, ol.prdlincod as Line_Code,
       ol.ordqty as Order_Qty, o.plnshpdat as Plan_Ship_Date
FROM velocity.dbo.ord o JOIN
     velocity.dbo.ord_ln ol
     ON o.ordtky = ol.ordtky JOIN
     velocity.dbo.product p
     ON p.prdtky = ol.prdtky
WHERE o.plnshpdat Between DATEADD(year, -1, GETDATE()) And DATEADD(year, 1, GETDATE())
ORDER BY ol.prdlincod;

更改：

• 我将表别名简化为表的缩写：更易于编写和阅读
• 我介绍了显式JOIN语法Never在FROM子句中使用逗号
• 我去掉了列别名周围的单引号。字符串和日期常量只能使用单引号。将它们用作列名可能会出错
• 我把日期算术改成了你的问题所要求的