I'm trying to create a piece of code which will define each three working days as a different period from the first date of each month given the current date as the input. This will need to be used in a process to assign a value to an attribute and will also need to be used to filter a query dynamically.
This is worked out by the following steps:
- Take the differences between the start and the current date and divide by 7
- Which gives a whole number and a remainder e.g. 18/7 = 2.6
- Take the whole number and multiply by five e.g. 2 * 5 = 10
- Take the remainder and multiply by 10 e.g. 0.6 * 10 = 6
- Where I’m stuck… if the multiplied remainder is over 5 then round down to 5 e.g. 6 > 5 = 5
- Sum the two numbers plus 1 and divide by 3 e.g. (10 + 6 + 1) / 3 = 5.6
- Round up to a whole number e.g. 5.6 = 6
Code:
Steps 1 - 3
ROUND((FLOOR(DAY_DIFFERENCE(FIRST_DAY_OF_MONTH(CURRENT_DATE),CURRENT_DATE+6) / 7) * 5 +
Steps 4 - 5
ROUND(DAY_DIFFERENCE(FIRST_DAY_OF_MONTH(CURRENT_DATE),CURRENT_DATE+6) / 7 -
FLOOR(DAY_DIFFERENCE(FIRST_DAY_OF_MONTH(CURRENT_DATE),CURRENT_DATE+6) / 7),1) * 10
Steps 6-7
+ 1 ) / 3 , 0)
The problem I’m having is with step five, as it needs to be rounded down to 5 if the value returned is more than 5. If this was only being used in a process it wouldn’t be a problem as I could use an If statement. However I also need to display these results in query’s. Which as far as I’m aware can’t use if statements to filter the data(?).
I'm thinking I may just have to create something in SQL and then import it into a BO to set the values for the query, but ideally I would like one piece of code that could filter the period. Does anyone know how I can do this?
Whole Statement
ROUND((FLOOR(DAY_DIFFERENCE(FIRST_DAY_OF_MONTH(CURRENT_DATE),CURRENT_DATE+6) / 7) * 5 +
ROUND(DAY_DIFFERENCE(FIRST_DAY_OF_MONTH(CURRENT_DATE),CURRENT_DATE+6) / 7 -
FLOOR(DAY_DIFFERENCE(FIRST_DAY_OF_MONTH(CURRENT_DATE),CURRENT_DATE+6) / 7),1) * 10 + 1 ) / 3 , 0)
