SQL for five days ago, excluding weekends

For many situations involving business dates, the best solution is a calendar table. For example, see Difference between two dates using office hours (22 May, 2001).

Presumably holidays do not matter in this instance. Hard to imagine why they would not, when weekends do. But as they do not, we can apply a formula. To develop this formula, let's first explore all possibilities.

If today is Monday, then, counting backwards, we skip over Sunday and Saturday, so Friday is one, Thursday two, and so on, taking us to last Monday as "five days ago." Here the quotes around "five days ago" will mean excluding weekends, and represents the date we are after, not literally five days ago. We will see the distinction in a minute.

If today is Tuesday, then, counting backwards, Monday is one day ago, we skip over Sunday and Saturday, Friday is two, Thursday three, and so on, taking us to last Tuesday as "five days ago."

This pattern—"five days ago" is the same day of the week, a week ago—repeats for Wednesday, Thursday, and Friday.

On Saturday, the pattern breaks. On Saturday, five days ago is Monday. And on Sunday, five days ago is also Monday, since we don't count the Saturday.

To sum up our findings and express them as data:

if today is     "5 days ago" is     subtract 
 Monday            Monday               7 
 Tuesday           Tuesday              7 
 Wednesday         Wednesday            7 
 Thursday          Thursday             7
 Friday            Friday               7
 Saturday          Monday               5  
 Sunday            Monday               6

The total days (including weekend days) to subtract from today, to get to the "five days ago" date, is shown in the right column.

Let's say that we number the days of the week with Sunday=1, Monday=2, and so on, to Saturday=7. Listing these weekday numbers against the number of days to subtract, we get:

if today is    weekday    subtract 
 Monday           2           7 
 Tuesday          3           7 
 Wednesday        4           7 
 Thursday         5           7
 Friday           6           7
 Saturday         7           5  
 Sunday           1           6

Here's the formula:

subtract = 7 - 2*(weekday/7) + (weekday-2)/7

Remember, this is the number of days to subtract from today, in order to get to the "five days ago" date. In this formula, division is integer division (i.e., round down). Please don't ask me how this formula was discovered. Let's just say it was trial and error.

How would we use this formula in SQL? Here's an example using MySQL syntax:

select distinct
       cust.fname
     , cust.lname
     , cust.phone
  from orders as o
inner
  join customers as cust
    on cust.id = orders.cust_id
 where o.date_ordered =
       date_sub( current_date
               , interval
       7 - 2 * floor(dayofweek(current_date)/7)
         + floor((dayofweek(current_date)-2)/7)
                     day )
   and o.date_shipped is null

This query returns customer contact information for customers with orders placed exactly "five days ago" which have not been shipped yet.