Business days between date function
I use the datediff for business days function in a number of mysql data flows and it works great. However, I'm getting some very strange results on the latest flow that I'm working on and I can't figure out what's going wrong.
I have a start date which I'm looking to compare to the current date. However, the code I'm using is generating results that are greater than the number of normal days between the dates!
For example, the code below generates a value of 186 for the start date of Nov 26 when a simple datediff function generates 109. But there's no consistently: for other dates, lesser values are generated:
Any idea what's going on?!
PS: I know it's trivial to do this in ETL, so no big deal, it's just really bugging me!
Here's the code:
CASE WHEN
`date_start` = CURRENT_DATE THEN 0
ELSE
DATEDIFF(
CASE WHEN DAYOFWEEK(CURRENT_DATE) = 7 THEN SUBDATE(CURRENT_DATE, INTERVAL 1 DAY)
WHEN DAYOFWEEK(CURRENT_DATE) = 1 THEN SUBDATE(CURRENT_DATE, INTERVAL 2 DAY) ELSE CURRENT_DATE END
,
CASE WHEN DAYOFWEEK(`date_start`) = 1 THEN ADDDATE(`date_start`,INTERVAL 1 DAY)
WHEN DAYOFWEEK(`date_start`) = 7 THEN ADDDATE(`date_start`,INTERVAL 2 DAY) ELSE `date_start` END
)

((CASE WHEN WEEK(CASE WHEN DAYOFWEEK(`date_start`) = 1 THEN ADDDATE(`date_start`,INTERVAL 1 DAY)
WHEN DAYOFWEEK(`date_start`) = 7 THEN ADDDATE(`date_start`,INTERVAL 2 DAY) ELSE `date_start` END) = WEEK(CASE WHEN DAYOFWEEK(CURRENT_DATE) = 7 THEN SUBDATE(CURRENT_DATE, INTERVAL 1 DAY)
WHEN DAYOFWEEK(CURRENT_DATE) = 1 THEN SUBDATE(CURRENT_DATE, INTERVAL 2 DAY) ELSE CURRENT_DATE END) THEN 0 ELSE WEEK(CASE WHEN DAYOFWEEK(CURRENT_DATE) = 7 THEN SUBDATE(CURRENT_DATE, INTERVAL 1 DAY)
WHEN DAYOFWEEK(CURRENT_DATE) = 1 THEN SUBDATE(CURRENT_DATE, INTERVAL 2 DAY) ELSE CURRENT_DATE END) END

CASE WHEN WEEK(CASE WHEN DAYOFWEEK(`date_start`) = 1 THEN ADDDATE(`date_start`,INTERVAL 1 DAY)
WHEN DAYOFWEEK(`date_start`) = 7 THEN ADDDATE(`date_start`,INTERVAL 2 DAY) ELSE `date_start` END) = WEEK(CASE WHEN DAYOFWEEK(CURRENT_DATE) = 7 THEN SUBDATE(CURRENT_DATE, INTERVAL 1 DAY)
WHEN DAYOFWEEK(CURRENT_DATE) = 1 THEN SUBDATE(CURRENT_DATE, INTERVAL 2 DAY) ELSE CURRENT_DATE END) THEN 0 ELSE WEEK(CASE WHEN DAYOFWEEK(`date_start`) = 1 THEN ADDDATE(`date_start`,INTERVAL 1 DAY)
WHEN DAYOFWEEK(`date_start`) = 7 THEN ADDDATE(`date_start`,INTERVAL 2 DAY) ELSE `date_start` END) END) * 2)
END bd
Best Answers

One issue you are running into is using the week function and your dates cross years. Since the week numbers start over at the beginning of each year, you are going to have issues with the math. You would need to incorporate if the year of both dates are the same or not and make the necessary adjustments.
I had to do a similar function and I started out starting small by just comparing two dates and trying to calculate the number of weekends between those two dates and then building my formula out from there.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 
Hi @LCash
Here's a simplified version for calculating the number of business days since today without using the WEEK function:
 Calculate the number of actual days between the two dates DATEDIFF(CURRENT_DATE(), `date_start`)  Subtract the days of weekends between the two dates  DATEDIFF(CURRENT_DATE() + INTERVAL (1  DAYOFWEEK(CURRENT_DATE())) DAY, `date_start` + INTERVAL (1  DAYOFWEEK(`date_start`)) DAY) / 7 * 2  If we start on a Sunday remove the partial week start  (CASE WHEN DAYOFWEEK(`date_start`) = 1 THEN 1 ELSE 0 END)  If we end on a Saturday remove the partial end of the week.  (CASE WHEN DAYOFWEEK(CURRENT_DATE()) = 7 THEN 1 ELSE 0 END)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers

One issue you are running into is using the week function and your dates cross years. Since the week numbers start over at the beginning of each year, you are going to have issues with the math. You would need to incorporate if the year of both dates are the same or not and make the necessary adjustments.
I had to do a similar function and I started out starting small by just comparing two dates and trying to calculate the number of weekends between those two dates and then building my formula out from there.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 
Hi @LCash
Here's a simplified version for calculating the number of business days since today without using the WEEK function:
 Calculate the number of actual days between the two dates DATEDIFF(CURRENT_DATE(), `date_start`)  Subtract the days of weekends between the two dates  DATEDIFF(CURRENT_DATE() + INTERVAL (1  DAYOFWEEK(CURRENT_DATE())) DAY, `date_start` + INTERVAL (1  DAYOFWEEK(`date_start`)) DAY) / 7 * 2  If we start on a Sunday remove the partial week start  (CASE WHEN DAYOFWEEK(`date_start`) = 1 THEN 1 ELSE 0 END)  If we end on a Saturday remove the partial end of the week.  (CASE WHEN DAYOFWEEK(CURRENT_DATE()) = 7 THEN 1 ELSE 0 END)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
SQL is a great tool, but I recommend you consider building a date dimension and then just marking the days as isWeekend or not.
Today they want week days, tomorrow they'll ask you to exclude holidays.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 
Thanks @MarkSnodgrass for the diagnosis, @GrantSmith for the solution, and @jaeW_at_Onyx for the recommendation!
0
Categories
 All Categories
 1.6K Product Ideas
 1.6K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 286 Workbench
 4 Cloud Amplifier
 6 Federated
 2.7K Transform
 91 SQL DataFlows
 583 Datasets
 2.1K Magic ETL
 3.6K Visualize
 2.4K Charting
 645 Beast Mode
 36 App Studio
 33 Variables
 629 Automate
 159 Apps
 431 APIs & Domo Developer
 37 Workflows
 2 DomoAI
 31 Predict
 12 Jupyter Workspaces
 19 R & Python Tiles
 377 Distribute
 107 Domo Everywhere
 266 Scheduled Reports
 4 Software Integrations
 104 Manage
 101 Governance & Security
 6 Domo University
 15 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 93 Community Announcements
 4.8K Archive