How to get top selling items a year ago

I want to get the top selling product of the month last year.

Example today’s month is May 2021, so I want to get the top selling products of May 2020, if the month changes to June, it automatically changes the @Date1 to June 1 and @Date2 to June 30.

Here is my query for getting the top selling using a DateTimePicker.

SELECT SUM
( Sales.Qty ) AS Qty_Sold,
Sales.ProductID,
Products.ProductDesc,
Sales.Price,
SUM ( Sales.Total ) AS Total_Sales FROM
dbo.Sales
INNER JOIN dbo.Products ON Sales.ProductID = Products.ProductID WHERE
Sales.SalesDate BETWEEN @Date1 AND @Date2 GROUP BY
Sales.ProductID,
Products.ProductDesc,
Sales.Price

But how to do it without the DateTimePicker.

Answer

You mean base it on SQLServer’s clock?

BETWEEN 
  DATEADD(day, 1, EOMONTH(GetUtcDate(), -13))) AND EOMONTH(GetUtcDate(), -12)

EOMONTH is a handy function that converts an input date to be the date of the last day in the month. GetUtcDate() gets the current time (in utc) of sqlserver’s clock

This means we can take sqlserver’s clock date today (2nd), eomonth it with a -13 months scroll to become 30 april last year, add a day to it to become 1st may

Similarly for the end date we can eomonth the current date to become end of may, with a -12 month scroll to become end of may last year

Note that if the dates in your sales table have times on too, you should instead:

WHERE 
  Sales.SalesDate >= DATEADD(day, 1, EOMONTH(GetUtcDate(), -13)) AND 
  Sales.SalesDate < DATEADD(day, 1, EOMONTH(GetUtcDate(), -12)) 

Because a time represents a “decimal” part of a day (if midnight 00:00 is like 1.0, it means 23:59:59.999999 is like 1.999999..) if you use BETWEEN then you’d miss off every product sold after midnight on the end day. Instead, use >= and < with the “less than” being “less than midnight of the next day” which will catch products sold even at 23:59:59.999999

We should do this rather than converting the table data by chopping the time off; always try to avoid converting or manipulating table data to make it match a parameter because it’s usually a major performance impact