How to count only weekdays between two dates

weekdays
count
datediff

(Max Corbin) #1

Counting the days between two dates isn’t too hard in SQL. Most SQL dialects come with some version of the DATEDIFF function, which can be used to simply count the days between two points in time:

Redshift

DATEDIFF(day, start, stop) AS days

Postgres

DATEPART(day, start - stop) AS days

MySQL

DATEDIFF(stop, start) AS days

Snowflake

DATEDIFF(day, start, stop) AS days

But this will count all the days between those two dates. What if you want to exclude weekends? You could use a special derived table to do this, but there’s a straightforward way using a single short query that you can define as a measure in your LookML.

Note: you’ll want to make sure that your database will return 0 for Sunday and 6 for Saturday; this is important, or else your calculations will be one or two days off. You’ll also want to make sure that you have the day_of_week_index timeframe declared in the dimension groups for your start date and end date:

Necessary Dimension Groups

Old LookML
- dimension_group: start
  type: time
  timeframes: [raw, day_of_week_index] ## you can have other timeframes here too
  sql: ${TABLE}.start_date

- dimension_group: stop
  type: time
  timeframes: [raw, day_of_week_index] ## same here!
  sql: ${TABLE}.stop_date
New LookML
dimension_group: start {
  type: time
  timeframes: [raw, day_of_week_index] ## you can have other timeframes here too
  sql: ${TABLE}.start_date ;;
}

dimension_group: stop {
  type: time
  timeframes: [raw, day_of_week_index] ## same here!
  sql: ${TABLE}.stop_date ;;
}
Here's an example, using Redshift:

Some Sneaky SQL

DATEDIFF('day', start, stop) - (FLOOR(DATEDIFF('day', start, stop) / 7) * 2) +
CASE WHEN DATE_PART(dow, start) - DATE_PART(dow, stop) IN (1, 2, 3, 4, 5) AND DATE_PART(dow, stop) != 0
THEN 2 ELSE 0 END +
CASE WHEN DATE_PART(dow, start) != 0 AND DATE_PART(dow, stop) = 0
THEN 1 ELSE 0 END +
CASE WHEN DATE_PART(dow, start) = 0 AND DATE_PART(dow, stop) != 0
THEN 1 ELSE 0 END) AS weekdays

This can look a little complicated at first, but it can be broken into pieces. Let’s start with the familiar part:

DATEDIFF('day', start, stop)

This is super familiar; a simple DATEDIFF. This calculates the total number of days between the start and end dates.

Adjustment Zero: Subtract One Weekend for Each Complete Week

We’ll subtract the number of complete weeks between those two dates, multiplied by two, to approximate the number of non-weekdays in that same period.

DATEDIFF('day', start, stop) - (FLOOR(DATEDIFF('day', start, stop) / 7) * 2)

This is actually a pretty good guess. It gets within one or two days of the actual number every time, and if that’s all you need, then you can just use that! But if you’re like me, you’re not giving up there, not when we’re so close! So we’ll address three different cases that come up where we’ll need to make more adjustments to this pattern:

Adjustment One: Incomplete Weeks that Include Full Weekends

The most obvious way this naïve approach fails is when we have a partial week at the beginning or end of the period that includes Saturday and Sunday. For example, what happens when your start date is a Thursday, and your end date that next Wednesday? Then this algorithm will come up with a count of 6 weekdays. But what about that weekend in the middle? We missed it, because we’re only counting complete weeks. How do we get it to give us the correct total of 4?

We add this wild CASE WHEN statement:

DATEDIFF('day', start, stop) - ((FLOOR(DATEDIFF('day', start, stop) / 7) * 2) +
CASE WHEN DATE_PART(dow, start) - DATE_PART(dow, stop) IN (1, 2, 3, 4, 5) AND DATE_PART(dow, stop) != 0
THEN 2 ELSE 0 END)

That CASE WHEN statement will check for this exact scenario, and if it finds that we’ve got a partial week with a complete weekend, it will add 2 to the number of days we need to subtract from our original DATEDIFF. This gives us 4 weekdays, just like we expected.

Adjustment Two: Stop Day is a Sunday, but Start Day is Not

This situation is a problem for a related reason to the first; we have a partial week that isn’t being counted by our Adjustment Zero, but we don’t necessarily want to subtract two days. This is because we aren’t counting the last day in our period (the stop day) in our total number of days, so if we subtracted two days (one for Saturday and one for Sunday) we’d actually be short a day. So this adjustment will only remove one day from the total:

DATEDIFF('day', start, stop) - ((FLOOR(DATEDIFF('day', start, stop) / 7) * 2) +
CASE WHEN DATE_PART(dow, start) - DATE_PART(dow, stop) IN (1, 2, 3, 4, 5) AND DATE_PART(dow, stop) != 0
THEN 2 ELSE 0 END +
CASE WHEN DATE_PART(dow, start) != 0 AND DATE_PART(dow, stop) = 0)

Adjustment Three: Start Day is a Sunday But Stop Day is Not

I’ll bet you saw this one coming. This is the reverse of the other adjustment; this time, our start day is a Sunday. For the same reason as the previous adjustment, we’ll want to add one more day to the number of days we’re subtracting from the first DATEDIFF. This gives us the complete expression:

DATEDIFF('day', start, stop) - ((FLOOR(DATEDIFF('day', start, stop) / 7) * 2) +
CASE WHEN DATE_PART(dow, start) - DATE_PART(dow, stop) IN (1, 2, 3, 4, 5) AND DATE_PART(dow, stop) != 0
THEN 2 ELSE 0 END +
CASE WHEN DATE_PART(dow, start) != 0 AND DATE_PART(dow, stop) = 0
THEN 1 ELSE 0 END +
CASE WHEN DATE_PART(dow, start) = 0 AND DATE_PART(dow, stop) != 0
THEN 1 ELSE 0 END)

You’re Done!

That’s all there is to it. This pattern can be adjusted to accommodate different start-of-the-week days, and definitely needs to be adjusted if your database assigns numbers differently to week days when you take the ‘day of week’ datepart.

Feel free to post into the comments if you made an interesting adjustment that adapts this pattern to a different SQL dialect or use case, I’d be stoked to see them!


Calculating KPIs with non-existent data for some employees
(Levi Davis) #2

For each old LookML code block in this article and its comments, we just added the New LookML equivalent code.


(jeffrey.martinez) #3

Adapted for Presto:

###Presto

dimension: weekdays_between {
type: number
sql: DATE_DIFF('day',${start_raw},${stop_raw}) - ((FLOOR(DATE_DIFF('day',${start_raw},${stop_raw})/7)*2) +
CASE WHEN DAY_OF_WEEK(${start_raw}) -
DAY_OF_WEEK(${stop_raw}) IN (1,2,3,4,5) AND DAY_OF_WEEK(${stop_raw}) !=0
THEN 2 ELSE 0 END +
CASE WHEN DAY_OF_WEEK(${start_raw}) != 0 AND DAY_OF_WEEK(${stop_raw}) = 0
THEN 1 ELSE 0 END +
CASE WHEN DAY_OF_WEEK(${start_raw}) = 0 AND DAY_OF_WEEK(${stop_raw}) != 0
THEN 1 ELSE 0 END);;
}

(Naveen Vemsani) #4

Adapted for Netezza

sum(distinct case when a12.ORDER_CURRENT_STATUS_CODE in (‘SHIPPED’) THEN dbods01…days_between( date(a12.ORDERDATE_PDT), date(pa11.WJXBFS1)) - ((FLOOR(dbods01…days_between (date(a12.ORDERDATE_PDT), date(pa11.WJXBFS1)) / 7) * 2) + CASE WHEN extract(dow from date(a12.ORDERDATE_PDT)) - extract(dow from date( pa11.WJXBFS1)) IN (2, 3, 4, 5,6) AND extract (dow from date( pa11.WJXBFS1)) <> 1 THEN 2 ELSE 0 END + CASE WHEN extract(dow from date(a12.ORDERDATE_PDT)) <> 1 AND extract(dow from date(pa11.WJXBFS1)) = 1 THEN 1 ELSE 0 END + CASE WHEN extract(dow from date(a12.ORDERDATE_PDT)) = 1 AND extract(dow from date(pa11.WJXBFS1)) <> 1 THEN 1 ELSE 0 END
)
ELSE NULL END)


(William Lane) #5

BigQuery (Standard SQL):

DATETIME_DIFF(cast(${end_time} as datetime),cast(${start_time} as datetime), DAY) -
          ((FLOOR(DATETIME_DIFF(cast(${end_time} as datetime), cast(${start_time} as datetime), DAY) / 7) * 2) +
          CASE WHEN (${start_day_of_week_index} - ${end_day_of_week_index}) IN (1, 2, 3, 4, 5) AND ${end_day_of_week_index} != 0
          THEN 2 ELSE 0 END +
          CASE WHEN ${start_day_of_week_index} != 0 AND ${end_day_of_week_index} = 0
          THEN 1 ELSE 0 END +
          CASE WHEN ${start_day_of_week_index} = 0 AND ${end_day_of_week_index} != 0
          THEN 1 ELSE 0 END)