CODEWITHSUNDEEP
sqlsql-servert-sql
KSingh
KSingh

Reputation: 143

Unable to get correct date T-SQL stored procedure

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.

My error:

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]
    @date DATE
AS
    SET NOCOUNT ON

    SELECT
        CustomerID,
        BAN_ADSL_TN,
        REPLACE(LandlinePhoneNumber, ',', '') LandlinePhoneNumber,
        REPLACE(MobilePhoneNumber, ',', '') MobilePhoneNumber,
        FirstName,
        LastName,
        REPLACE(ServiceAddress, ',', '') ServiceAddress,
        ServiceAddressAptNo,
        REPLACE(ServiceAddressCity, ',', '') ServiceAddressCity,
        ServiceAddressState,
        ServiceAddressZip,
        OrderCompletionDate = CONVERT(VARCHAR(10), OrderCompletionDate, 101)
    FROM 
        dbo.Customers 
    WHERE 
        StatusID = 115 AND 
        YEAR(OrderCompletionDate) = YEAR(@date) - 1 AND 
        MONTH(OrderCompletionDate) = MONTH(@date) + 1
    ORDER BY 
        OrderCompletionDate DESC

Upvotes: 0

Views: 49

Answers (2)

ColdSolstice
ColdSolstice

Reputation: 465

This should work

Create PROCEDURE [dbo].[usp_GetAnniversaryCustomers]
 @date DATE
 AS
SET NOCOUNT ON

DECLARE @HistoricalDate DATE = DATEADD(month,-11,@date);
DECLARE @HistoricalDateStart DATE = DATEADD(month,DATEDIFF(month,0,@HistoricalDate),0);
DECLARE @HistoricalDateEnd DATE = DATEADD(month,1,@HistoricalDateStart);


SELECT  CustomerID,
    BAN_ADSL_TN,
    REPLACE(LandlinePhoneNumber,',','')LandlinePhoneNumber,
    REPLACE(MobilePhoneNumber,',','')MobilePhoneNumber,
    FirstName,
    LastName,
    REPLACE(ServiceAddress,',','')ServiceAddress,
    ServiceAddressAptNo,
    REPLACE(ServiceAddressCity,',','')ServiceAddressCity,
    ServiceAddressState,
    ServiceAddressZip,
    --MailingAddress,
    --MailingAddressAptNo,
    --MailingAddressCity,
    --MailingAddressState,
    --MailingAddressZip,
    --LanguageID,
    --Customer Service Number 
    OrderCompletionDate = CONVERT(VARCHAR(10), OrderCompletionDate, 101)
 FROM dbo.Customers 
 WHERE StatusID = 115
    AND OrderCompletionDate >= @HistoricalDateStart
    AND OrderCompletionDate < @HistoricalDateEnd
    ORDER BY OrderCompletionDate DESC

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

WHERE
        StatusID = 115
    AND OrderCompletionDate >= DATEADD(month, -11, @date)
    AND OrderCompletionDate <  DATEADD(month, -10, @date)

By using DATEADD() all the boundary cases are handled for you.

By using >= startDate AND < endDate you make the use of DATEADD() even simpler.

By putting all the calculations on the right hand side (manipulating the variable, not the table's data) you improve performance and enable use of indexes.

Upvotes: 3

Related Questions