SQL query to match whole number of weeks

Question

I am setting up a recurring billing processor*. The client wants to be able to set that clients can choose the billing to occur fortnightly, aka every two weeks.

I could attempt to calculate the 'next' payment date myself, but it seems it would be much more reliable to record the first payment date, and then use an SQL query to calculate 'is today a 2 N weeks after the first date'.

select * from recurring_payments
where (datediff(first_billing_date, now() % 14) = 0

However that has the downside of doing a (presumably) unindexable full table scan. Is there a better way of finding rows for who a field matches 2N days ago?

This is in MySQL initially, but moving to MSSQL in a few weeks, so a generic method would be better than a bespoke feature.

*yes, I'm scared.

Which RDBMS are you using? SQL Server has `DATEPART(week, @date)` for example. However, most financial software I've worked on is configured with configurable pay period dates. There's a pay period table with the year, pay period, start and end dates. Usually there's a way to configure them automatically, but the system allows you to configure the specific dates of a pay period as necessary. After all, you don't know when a year end rollover will take place. — Bacon Bits, Sep 09 '14 at 18:43
*"it seems it would be much more reliable to record the first payment date, and then use an SQL query to calculate 'is today a 2 N weeks after the first date'."* Maybe not. What happens if the calculated date falls on Christmas Day? Or on a Sunday? — Mike Sherrill 'Cat Recall', Sep 09 '14 at 18:54
You can add an indicator with numbers from 1 to 14 that will indicate the payment "cycle" — Fabien TheSolution, Sep 09 '14 at 19:06
@MikeSherrill'CatRecall' online payment systems are available on sundays + other holidays. — Danack, Sep 09 '14 at 20:04
@Danack: Is this for an online payment system? Might be good to add that to your question. — Mike Sherrill 'Cat Recall', Sep 09 '14 at 21:27

score 0 · Answer 1 · answered Sep 10 '14 at 00:42

So, like I said, you can add an indicator with numbers from 1 to 14 that will indicate the payment "cycle".

So you will have 14 cycles and you will just have to retrieve those that are in your today's cycle...8 in my example, as we are september 9.

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE recurring_payments
    (`clientid` int, `first_billing_date` date, `cycle` int)
;

INSERT INTO recurring_payments
    (`clientid`, `first_billing_date`, `cycle`)
VALUES
    (1, '2014-08-19', 1),
    (2, '2014-08-21', 3),
    (3, '2014-08-23', 5),
    (4, '2014-08-25', 7),
    (5, '2014-08-26', 8),
    (6, '2014-08-27', 9),
    (7, '2014-08-29', 11),
    (8, '2014-08-30', 12),
    (9, '2014-09-01', 14),
    (10, '2014-09-02', 1),
    (11, '2014-09-03', 2),
    (12, '2014-09-05', 4),
    (13, '2014-09-06', 5),
    (14, '2014-09-07', 6),
    (15, '2014-09-08', 7)
;

Query 1:

select *
from recurring_payments
where cycle = 8 and first_billing_date < curdate()

Results:

| CLIENTID |            FIRST_BILLING_DATE | CYCLE |
|----------|-------------------------------|-------|
|        5 | August, 26 2014 00:00:00+0000 |     8 |

SQL query to match whole number of weeks

1 Answers1