需要将去年的结果提取到库存使用情况报告中

Question

我有一个库存使用情况报告，其中提取了上一年的事务。现在我有今年的结果。我使用的正确日期语法是什么？

以下是我今年目前拥有的内容，我正在使用今年的获取日期函数，需要它来计算相同的信息，但去年每月分解一次。

SELECT OITM.ItemCode, OITM.ItemName AS 'Description', OITM.CardCode AS 
'Vendor',
SUM(OITW.OnHand) AS 'On Hand', SUM(OITW.OnOrder) AS 'On Order', 
SUM(OITW.IsCommited) AS 'Committed',
(SUM(OITW.OnHand)+SUM(OITW.OnOrder)-SUM(OITW.IsCommited)) AS 'Available', 
OITM.AvgPrice AS 'Unit Cost',(SUM(OITW.OnHand)*OITM.AvgPrice) AS 'Value $',
(SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND 
YEAR(OINM.DocDate)=((year, -1, GETDATE()) AND OINM.ItemCode = OITM.ItemCode 
GROUP BY
OINM.ItemCode) AS 'Prev. Year',
(SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND 
MONTH(OINM.DocDate)='1' AND YEAR(OINM.DocDate)=(year, -1, GETDATE()) AND 
OINM.ItemCode =
OITM.ItemCode GROUP BY OINM.ItemCode) AS 'JAN',
(SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND 
MONTH(OINM.DocDate)='2' AND YEAR(OINM.DocDate)=(year, -1, GETDATE()) AND 
OINM.ItemCode =
OITM.ItemCode GROUP BY OINM.ItemCode) AS 'FEB',  
(SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND 
MONTH(OINM.DocDate)='3' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND 
OINM.ItemCode =
OITM.ItemCode GROUP BY OINM.ItemCode) AS 'MAR',
(SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND 
MONTH(OINM.DocDate)='4' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND 
OINM.ItemCode =
OITM.ItemCode GROUP BY OINM.ItemCode) AS 'APR',
(SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND 
MONTH(OINM.DocDate)='5' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND 
OINM.ItemCode =
OITM.ItemCode GROUP BY OINM.ItemCode) AS 'MAY',
(SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND 
MONTH(OINM.DocDate)='6' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND 
OINM.ItemCode =
OITM.ItemCode GROUP BY OINM.ItemCode) AS 'JUN',
(SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND 
MONTH(OINM.DocDate)='7' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND 
OINM.ItemCode =
OITM.ItemCode GROUP BY OINM.ItemCode) AS 'JUL',
(SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND 
MONTH(OINM.DocDate)='8' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND 
OINM.ItemCode =
OITM.ItemCode GROUP BY OINM.ItemCode) AS 'AUG',
(SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND 
MONTH(OINM.DocDate)='9' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND 
OINM.ItemCode =
OITM.ItemCode GROUP BY OINM.ItemCode) AS 'SEP',
(SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND 
MONTH(OINM.DocDate)='10' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND 
OINM.ItemCode =
OITM.ItemCode GROUP BY OINM.ItemCode) AS 'OCT',
(SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND 
MONTH(OINM.DocDate)='11' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND 
OINM.ItemCode =
OITM.ItemCode GROUP BY OINM.ItemCode) AS 'NOV',
(SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND 
MONTH(OINM.DocDate)='12' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND 
OINM.ItemCode =
OITM.ItemCode GROUP BY OINM.ItemCode) AS 'DEC'
FROM OITM, OITW
WHERE OITM.ItemCode=OITW.ItemCode

Answer 1

我的建议是稍微缩短原始查询。如果我没有完全错，可以这样写：

WITH 
  OINM_Base (ItemCode, [Month], OutQty) AS (
    SELECT 
      ItemCode,
      CASE GROUPING(MONTH(DocDate))
        WHEN 1 THEN 0 ELSE MONTH(DocDate)
      END,
      SUM(OutQty)
    FROM OINM
    WHERE TransType <> '67' AND YEAR(DocDate) = YEAR(GETDATE()) -- change this for the previous year
    GROUP BY ItemCode, ROLLUP(MONTH(DocDate))
  ),
  OINM_Data (ItemCode, [Year], JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, [DEC]) AS (
    SELECT ItemCode, [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]
    FROM OINM_Base
    PIVOT (SUM(OutQty) FOR [Month] IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) pvt
  ),
  OITW_Data (ItemCode, [On Hand], [On Order], [Committed]) AS (
    SELECT ItemCode, SUM(OnHand), SUM(OnOrder), SUM(IsCommited)
    FROM OITW
    GROUP BY ItemCode
  )
SELECT
  tm.ItemCode,
  tm.ItemName AS [Description],
  tm.CardCode AS Vendor,
  tw.[On Hand], tw.[On Order], tw.[Committed],
  tw.[On Hand] + tw.[On Order] - tw.[Committed] AS Available,
  tm.AvgPrice AS [Unit Cost],
  tw.[On Hand] * tm.AvgPrice AS [Value $],
  nm.[Year], nm.JAN, nm.FEB, nm.MAR, nm.APR, nm.MAY, nm.JUN, nm.JUL, nm.AUG, nm.SEP, nm.OCT, nm.NOV, nm.[DEC]
FROM OITM tm
  INNER JOIN OITW_Data tw ON tm.ItemCode = tw.ItemCode
  LEFT OUTER JOIN OINM_Data nm ON tm.ItemCode = nm.ItemCode;

其工作原理如下：

OINM中的数据仅限于相关年份，将每月的OutQty值相加，并为标记为月份编号 0 (CTE OINM_Base 的年份创建一个总计。使用 PIVOT 查询，不同行上显示的总和将转换为列 (CTE OINM_Data (。OITW 数据的聚合也是按顺序完成的(CTE OITW_Data (。

OINM_Data和OITW_Data最终都与从OITM构建主行的数据连接。在这里，最终计算完成(Available和Value $(。

现在，要将查询更改为返回上一年，只需更改一件事(请参阅我的评论">为前一年更改此内容"(，即：

YEAR(GETDATE())必须被YEAR(GETDATE()) - 1取代.