SQL for five days ago, excluding weekends

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL How to check SQL query construction with the Mimer Validator Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join An SQL solution for a customer order homework problem How to use SQL's POSITION function with substrings Using SQL date functions to get totals for last three days Using CASE in the SQL ORDER BY clause What's the difference between an SQL inner join and equijoin? Oracle and SQL Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to use the Oracle Database SQL Reference Manual How to use SQL Developer to run SQL statements How to work with the Oracle database home page How to use SQL*Plus in Oracle How to use SQL Developer to work with an Oracle database How to view and edit table column definitions How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join Oracle and SQL Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary autonomous transaction  (SearchOracle.com) CFML  (SearchOracle.com) dynamic SQL  (SearchOracle.com) foreign key  (SearchOracle.com) Java Database Connectivity  (SearchOracle.com) Open Database Connectivity  (SearchOracle.com) Oracle  (SearchOracle.com) stored procedure  (SearchOracle.com) The Open Group  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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.