Finding Recurring Dates in MSSQL using HAVING

2007 March 30
tags: MSSQL
by Paul Marcotte
Recently, I automated a manual billing process for recurring invoices. In order to batch process the job, I decided to use a stored procedure that gets the billing details for all recurring invoices by date. MSSQL date functions DATEADD, DATEDIFF and DATEPART take some getting used to, but are well documented and (once understood) made the query nice and light. What I missed was the fact that not all months are created equal.Using @BillingDate as my input date, my first take ran something like this: SELECT
   ...
FROM
   Invoice
HAVING
   (((DATEDIFF(month, MIN(CreatedDate), @BillingDate)/Frequency)-(COUNT(InvoiceID)-1)) > 0) AND
   (DATEPART(day,MIN(CreatedDate)) = DATEPART(day,@BillingDate))
The subtle nuances of our legacy system aside, the HAVING clause above reads "get billing details for invoices that are set to happen this month on this day". This ever-so-clever query ignored the fact that an invoice date may happen after the last day of a given month. For instance, an invoice created on November 30 which is set to recur quarterly would happen on February 30...yes? Nope. Back to the drawing board. In order to get the billing details on days that fall outside the last day of a month, I needed a clause that would "get billing details for invoices that are set to happen this month on this day, or after today if today is the last day of the month."That's a mouthful in plain English. Here's how how it turned out in T-SQL. SELECT
...
FROM
   Invoice
HAVING
   (((DATEDIFF(month, MIN(CreatedDate), @BillingDate)/Frequency)-(COUNT(BillingID)-1)) > 0) AND
   (
      (DATEPART(day,MIN(CreatedDate)) = DATEPART(day,@BillingDate)) OR      
      (
         ((DATEPART(day,MIN(CreatedDate)) - DATEPART(day,@BillingDate)) > 0) AND
         ((DATEPART(month,DATEADD(day,1,@BillingDate)) - (DATEPART(month,@BillingDate))) > 0)
      )
   )