Eric L. Barnes

Today I hit an interesting problem and when creating a report for an internal billing system. The system itself automatically bills each customer every year and we have two types of customers. One that pays with a credit card through Stripe, and another type that we invoice and their billing departments pay.

Because invoices take longer for us to be paid we always create the invoice 30 days before it’s actually due and for Stripe customers,​ we do it 7 days early. This way if anything goes wrong like a declined credit card it gives them extra time to fix it before becoming delinquent.

Of course, the side effect to this is it causes our stored “next billing date” to be 7 or 30 days off depending on the type of customer, and we keep the original date in case the customer ever decides to switch from one to the other.

With that setup out of the way, I needed to create a list of customers ordered by when they would be billed. Like all things, there are many ways to solve this but for this, I decided to do it directly through SQL. What I wanted to happen was if an organization is a Stripe customer then creating a “real_bill_date” set to 7 days before it’s due, otherwise create a “real_bill_date” to 30 days prior.

Here is the query I was able to create:

SELECT
  CASE
    WHEN
      organizations.stripe_active = \'1\'
        THEN
          DATE_SUB(next_bill_date,INTERVAL 7 DAY)
    ELSE
      DATE_SUB(next_bill_date,INTERVAL 30 DAY)
  END AS real_bill_date,
  organizations.*
FROM
  organizations 
WHERE 
  subscription_active = 1
ORDER BY 
  real_bill_date ASC

This uses SQL WHEN/ELSE clauses to determine what should be happening. Basically translating into “if Stripe is active, then subtract 7 days”, “else subtract 30 days”.

This is useful for the next time you think you have to do a query and then do a query looping each to get a calculated field. Yes, that typically works but is process intensive and can lead to timeouts. Doing it directly through SQL is usually much faster.