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.
For each old LookML code block in this article and its comments, we just added the New LookML equivalent code.
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.
4 posts were split to a new topic: GROUP BY error counting only weekdays between two dates