- My Platform
- Tools
- Learn
#### Learn

Go back - Community
- Support

61Views

10Comments

Solved

Rank: #697

Solution

Nuno's idea is great to avoid all the unnecessary iterations (assuming DiffDays runs on constant time). The difference is negliglble for a few calls, but if you are going to use it constantly (e.g. logic that is running 24/7 with potentially large timespans) then you should do your best to optimize it. I also noticed that my flow above does not work for negative differences (which you might need for your use case), so that can be corrected easily too.

To generalize these ideas to any situation, you can do the following (*DiffIsNegative *is of type Boolean, *FullWeeks *is of type Integer and *TempDateTime *is of type DateTime):

Now, assuming you implemented the flow in my previous post as DiffDaysWithoutWeekends, you can now create the following "improved" flow:

So it is exactly the same core idea but with the additional considerations for handling the negative case, and optimizing the number of iterations by applying Nuno's method to the general case.

These assignments ("Initialize") are used to apply Nuno's suggestion, along with the final "Add Full Week Weekdays" assignment before the end. Just consider, for every full week that has passed (7 full days), 5 of those days must be non-weekend days; therefore in the end we need to add (# of Full Weeks * 5) to the final count:

These assignments ("Swap DT1-DT2 and Mark as Negative") are needed to handle the negative cases, along with the "DiffShouldBeNegative" subflow towards the end, basically making it count backward instead of forward:

We do not need to multiply the (FullWeeks * 5) by (-1) in the negative case because FullWeeks can already be a negative number since it is calculated using the built-in DiffDays(), so we don't need to take care of that part.

Hope all of this makes sense! In the end the optimizations may not be needed at all, and there might be better ways to present/simplify the flow (such as creating a single action with all the necessary logic), but this should cover all the main points. I got a bit overly invested into the problem so I figured I might as well share my efforts lol :)

3 replies

Last reply on 2020-09-27

Show thread

mvp_badge

MVP

Rank: #71

Solution

Just a small improvement into Francisco idea:

This is for holidays, so the number of days is small, a week or two each time. But imagine this is for longer periods: a leave or a sabbatical. No need to do all the math day by day.

Imagine DiffDays returns 180.

Trunc 180/7 to know the number of weeks. (25, so 125 week days)

Add 25*7 days to your date (175) and just check the final 5 days to see if they are weekend or not.

So, do this before Francisco's flow and you just saved 175 loops in 180 days.

If you also need to consider holidays, the answer is a bit longer. You need to create a list with all the holidays and check if your interval includes any holiday that is not on weekend.

1 reply

on 2020-09-25

Show thread

Rank: #697

Something like this should do the trick:

Create an action like this with the output as an Integer (turn it into a function if you want to use it inside expressions):

Basically it starts at DateTime1 and counts manually the days up to DateTime2 if those days aren't weekends. I did some tests and it seems to work fine, but if you understand the logic you should also be able to edit it to fit more specific needs. Let me know if that works!

1 reply

on 2020-09-25

Show thread

mvp_badge

MVP

Rank: #71

Solution

Just a small improvement into Francisco idea:

This is for holidays, so the number of days is small, a week or two each time. But imagine this is for longer periods: a leave or a sabbatical. No need to do all the math day by day.

Imagine DiffDays returns 180.

Trunc 180/7 to know the number of weeks. (25, so 125 week days)

Add 25*7 days to your date (175) and just check the final 5 days to see if they are weekend or not.

So, do this before Francisco's flow and you just saved 175 loops in 180 days.

If you also need to consider holidays, the answer is a bit longer. You need to create a list with all the holidays and check if your interval includes any holiday that is not on weekend.

1 reply

on 2020-09-25

Show thread

Rank: #697

Solution

Nuno's idea is great to avoid all the unnecessary iterations (assuming DiffDays runs on constant time). The difference is negliglble for a few calls, but if you are going to use it constantly (e.g. logic that is running 24/7 with potentially large timespans) then you should do your best to optimize it. I also noticed that my flow above does not work for negative differences (which you might need for your use case), so that can be corrected easily too.

To generalize these ideas to any situation, you can do the following (*DiffIsNegative *is of type Boolean, *FullWeeks *is of type Integer and *TempDateTime *is of type DateTime):

Now, assuming you implemented the flow in my previous post as DiffDaysWithoutWeekends, you can now create the following "improved" flow:

So it is exactly the same core idea but with the additional considerations for handling the negative case, and optimizing the number of iterations by applying Nuno's method to the general case.

These assignments ("Initialize") are used to apply Nuno's suggestion, along with the final "Add Full Week Weekdays" assignment before the end. Just consider, for every full week that has passed (7 full days), 5 of those days must be non-weekend days; therefore in the end we need to add (# of Full Weeks * 5) to the final count:

These assignments ("Swap DT1-DT2 and Mark as Negative") are needed to handle the negative cases, along with the "DiffShouldBeNegative" subflow towards the end, basically making it count backward instead of forward:

We do not need to multiply the (FullWeeks * 5) by (-1) in the negative case because FullWeeks can already be a negative number since it is calculated using the built-in DiffDays(), so we don't need to take care of that part.

Hope all of this makes sense! In the end the optimizations may not be needed at all, and there might be better ways to present/simplify the flow (such as creating a single action with all the necessary logic), but this should cover all the main points. I got a bit overly invested into the problem so I figured I might as well share my efforts lol :)

3 replies

Last reply on 2020-09-27

Show thread

Rank: #56

Hi all,

For future reference, this scenario can now be implemented using the Date Time Utils component, along with other client-side date time actions:

https://www.outsystems.com/forge/component-overview/9759/date-time-utils

Hope it helps.

Regards,

João

Champion

Rank: #275

Just for an alternative approach you can also do it using an SQL query. Not that it's any more efficient but can be done in a single call.

SELECT

(DATEDIFF(dd, @fromdate, @todate) + 1)

-(DATEDIFF(wk, @fromdate, @todate) * 2)

-(CASE WHEN DATENAME(dw, @fromdate) = 'Sunday' THEN 1 ELSE 0 END)

-(CASE WHEN DATENAME(dw, @todate) = 'Saturday' THEN 1 ELSE 0 END)

As NoOfWeekDays

Hi everyone,

I have one doubt.

I need to implement an expression/function between dates to calculate the vacations of the employees, but i need to take out the weekend days( saturday and sunday).

I use a DiffDays() and this calculated the difference between the dates but included the weekend days.

How I can take out the weekend days?