sql- show only the last four month

Question

my good reference has been How to filter my results so it shows the last four months of data - sql

So far I have

SELECT ItemCode, SUM(QuantityOrdered) AS Total_Quantity
FROM OrderDetail 
GROUP BY ItemCode
ORDER BY SUM(QuantityOrdered) DESC;

It shows

ItemCode  Total_Quantity 
PL         1200    
MA          975        
153         200

While PL's Orders are qty 200 on 9/23/2011 qty 200 on 2/3/2014 qty 200 on 12/6/2016 qty 200 1/21/2017

How can I filter it out to show only last four months result from today's month?

Can I do something on Query design or SQL code?

Any idea on Syntax error on line WHERE dbo_SO_SalesOrderHeader.OrderDate >= dateadd(month, -4, cast(getdate() AS DATE)). Thank you so much!

SELECT dbo_SO_SalesOrderDetail.ItemCode, SUM(dbo_SO_SalesOrderDetail.QuantityOrdered) AS Total_Quantity, dbo_SO_SalesOrderHeader.OrderDate
FROM dbo_SO_SalesOrderDetail INNER 
JOIN dbo_SO_SalesOrderHeader ON dbo_SO_SalesOrderDetail.SalesOrderNo = dbo_SO_SalesOrderHeader.SalesOrderNo
WHERE dbo_SO_SalesOrderHeader.OrderDate >= dateadd(month, -4, cast(getdate() AS DATE))
GROUP BY dbo_SO_SalesOrderDetail.ItemCode
ORDER BY SUM(dbo_SO_SalesOrderDetail.QuantityOrdered) DESC;

Answer 1

Add a where clause and compare the "DateField" to the current date. So if your DateField is named OrderDate replace "DateField" with "OrderDate"

SELECT ItemCode, SUM(QuantityOrdered) AS Total_Quantity
FROM OrderDetail 
where DateField >= dateadd(month, -4, cast(getdate() as DATE)) 
GROUP BY ItemCode
ORDER BY SUM(QuantityOrdered) DESC;

I am guessing that your datefield is actually in the OrderHeader table and if so you will need to join the OrderHeader table. Below is an example

SELECT ItemCode, SUM(QuantityOrdered) AS Total_Quantity
    FROM OrderDetail 
    JOIN OrderHeader on OrderHeader.Orderid=OrderDetail.OrderID
    WHERE OrderHeader.OrderDate >= dateadd(month, -4, cast(getdate() as DATE)) 
    GROUP BY ItemCode
    ORDER BY SUM(QuantityOrdered) DESC;