Question

How to count only weekdays between two dates

  • 9 September 2016
  • 6 replies
  • 855 views

Userlevel 3
Badge

The content of this article has been updated and migrated to a Looker Help Center article.


But, the information in the comments is useful, and so the article will remain here for discussion. For the actual contents, please visit the Help Center.



6 replies

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

Userlevel 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);;
}

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)

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)

Here’s an alternative solution (Standard SQL):


DATE_DIFF(resolved_date,created_date,DAY) - 
DATE_DIFF(resolved_date, created_date,WEEK(SATURDAY)) -
DATE_DIFF(resolved_date, created_date,WEEK(SUNDAY))

Although I find that this results in N+1 to the Google Sheets (haven’t tested in Excel) results of NETWORKDAYS function.


Ex: 7/5/19 - 9/4/19

NETWORKDAYS: 44

Above solution: 43


Seems like NETWORKDAYS in Google sheets includes both the start date and the end date, so Today + Tomorrow returns 2 days, Mon-Wed returns 3 days so the “between” description is a little ambiguous.

Userlevel 7
Badge +1

4 posts were split to a new topic: GROUP BY error counting only weekdays between two dates

Reply