Business days between date function

LCash

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

MarkSnodgrass

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.