The stored procedure below is suppose to get the
OrderCompletionDate year as last year AND and adds a month to current month.
So if I run it in January of 2018, it returns data for February 2017, and in February 2018, it will return the data from March 2017, so on and so forth.
When we get to December, it looks for month 13, which does not exist. I want it to get the current year's January data in this case. So when we get to December 2018, it should return the January 2018 data.
My question is:
How can I change this stored procedure so it still returns the last year and month +1 data. But when we get to December, it returns data for January of the current year?
CREATE PROCEDURE [dbo].[usp_GetAnniversaryCustomers]
SET NOCOUNT ON
REPLACE(LandlinePhoneNumber, ',', '') LandlinePhoneNumber,
REPLACE(MobilePhoneNumber, ',', '') MobilePhoneNumber,
REPLACE(ServiceAddress, ',', '') ServiceAddress,
REPLACE(ServiceAddressCity, ',', '') ServiceAddressCity,
OrderCompletionDate = CONVERT(VARCHAR(10), OrderCompletionDate, 101)
StatusID = 115 AND
YEAR(OrderCompletionDate) = YEAR(@date) - 1 AND
MONTH(OrderCompletionDate) = MONTH(@date) + 1