Need to Pull result from last year into inventory usage report

Question

I have an inventory usage report that pulls in transactions from the previous year. Right now I have results for the current year. What is the correct date syntax for me to use?

Below is what I have currently for this year I am using the get date function for this year and need it to calculate the same information, but break it down monthly for last year.

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

My suggestion is to shorten the original query a bit. If I'm not completely wrong, it can be written as follows:

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;

This works as follows:

The data in OINM is restricted to the year in question, summing the OutQty values per month and creating a total for the year which is labelled as month number 0 (CTE OINM_Base). Using a PIVOT query, the sums appearing on different rows are turned into columns (CTE OINM_Data). The aggregation for the OITW data is also done saparately (CTE OITW_Data).

Both OINM_Data and OITW_Data are finally joined with the data building the main rows from OITM. Here, the final calculations are done (Available and Value $).

Now, to change the query to return the previous year, only one thing has to be changed (see my comment "change this for the previous year"), namely :

YEAR(GETDATE()) has to be replaced by YEAR(GETDATE()) - 1.