I’ll provide a solution using a scalar UDF called NumPayments. The function accepts two inputs: @startdate (the standing order/direct debit start date), and @enddate (the date that the calculation should be checked against—typically the date when the query is run). Here’s the function’s header:

CREATEFUNCTION dbo.NumPayments

(

@startdate AS DATETIME,

@enddateAS DATETIME

)RETURNS INT

The logic behind the calculation is actually quite straightforward. First calculate the difference in terms of months between the starting year-and-month and the ending year-and-month, then add 1 to account for both the starting month and ending month. Then, subtract 1 in case the ending day is smaller than the starting day. Per the original request that I got, if the day part of the end date is on or after the day part of the start date, there is a payment in the end month, otherwise there isn’t. If the day part of the start date is greater than the maximum possible day part of a given month, the payment for that month happens on the first of the next month. The last part of the calculation handles this scenario correctly (subtract 1 in case the ending day is smaller than the starting day).

As an example, suppose you are given the following sample data representing different dates in which you make the request:

USE tempdb;

IFOBJECT_ID('dbo.SampleDates', 'U') IS NOT NULL

DROP TABLE dbo.SampleDates;

CREATETABLE dbo.SampleDates

(

dt DATETIME NOT NULL PRIMARY KEY

);

GO

INSERTINTO dbo.SampleDates(dt)

SELECT '20090228'

UNION ALL SELECT '20090301'

UNION ALL SELECT '20090429'

UNION ALL SELECT '20090430'

UNION ALL SELECT '20090501';

And you run the following query to calculate how many standing order/direct debit payments should have been received by the date in the table, for a start date of October 30th, 2008:

DECLARE @startdate AS DATETIME;

SET @startdate = '20081030';

SELECT

@startdate AS startdate, dt AS enddate,

dbo.NumPayments(@startdate, dt) AS payments

FROM dbo.SampleDates;

You are supposed to get the following output:

startdate enddate payments

----------------------- ----------------------- -----------

2008-10-30 00:00:00.000 2009-02-28 00:00:00.000 4

2008-10-30 00:00:00.000 2009-03-01 00:00:00.000 5

2008-10-30 00:00:00.000 2009-04-29 00:00:00.000 6

2008-10-30 00:00:00.000 2009-04-30 00:00:00.000 7

2008-10-30 00:00:00.000 2009-05-01 00:00:00.000 7

Here’s the definition of the UDF that I used to address this calculation: