How to get rows with a date is less than 30 days in SQL Server?

advertisements

I have the following table structure:

Table: tbl_CustomerPromos:

COLUMN       DATATYPE
PromoCodeID  int (IDENTITY)
CustomerID   int
PromoCode    nvarchar(50)
AddedDate    DateTime
IsPromoUsed  smallint

I want to send email notification to all customers AFTER EVERY 7 days from the DAY of JOINING (AddedDate) until the next 30 days.

select
   pc.[CustomerID]
   , pc.[PromoCode]
   , pc.[DiscountAmount]
   , ISNULL(pc.[IsPromoUsed], 0) IsPromoUsed
   , pc.[AddedBy]
   , pc.[AddedDate]
   , (c.FirstName + ' ' + c.LastName) FullName
   , c.Email
from
   tbl_PromoCodesNewCustomer pc
Left join
   tbl_Customers c on pc.CustomerID = c.CustomerID
Where
   isnull(pc.IsPromoUsed, 0) <> 1
   AND pc.AddedDate = ?

NOTE: The 30days is to be counted from the day of joining (AddedDate) to next 30 days and not from joining month.

So, how to get rows with a date that is less than 30 days (AddedDate is not greater than 30 days from today's date)


To get the 'every 7 days' filter, I would suggest you MODULO the date diff (after checking that its within the 30 day period). A CTE will help DRY this up. Also, I'm assuming here that you'll only be running this exactly once per day.

WITH EligiblePromoCodes AS
(
   SELECT
      CustomerId,
      PromoCode,
      IsPromoUsed,
      DiscountAmount,
      AddedDate,
      AddedBy,
      DATEDIFF(dd, AddedDate, CURRENT_TIMESTAMP) AS DaysSinceAdded
   FROM
     tbl_PromoCodesNewCustomer
)
SELECT
   pc.[CustomerID]
   , pc.[PromoCode]
   , pc.[DiscountAmount]
   , ISNULL(pc.[IsPromoUsed], 0) IsPromoUsed
   , pc.[AddedBy]
   , pc.[AddedDate]
   , (c.FirstName + ' ' + c.LastName) FullName
   , c.Email
from
   EligiblePromoCodes pc
Left join
   tbl_Customers c on pc.CustomerID = c.CustomerID
Where
   isnull(pc.IsPromoUsed, 0) <> 1
   AND DaysSinceAdded <= 30
   AND DaysSinceAdded % 7 = 0;

SqlFiddle here