Round down condition without using an IF?

[JHew]

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:

1. Take the differences between the start and the current date and divide by 7
2. Which gives a whole number and a remainder e.g. 18/7 = 2.6
3. Take the whole number and multiply by five e.g. 2 * 5 = 10
4. Take the remainder and multiply by 10 e.g. 0.6 * 10 = 6
5. Where I’m stuck… if the multiplied remainder is over 5 then round down to 5 e.g. 6 > 5 = 5
6. Sum the two numbers plus 1 and divide by 3 e.g. (10 + 6 + 1) / 3 = 5.6
7. Round up to a whole number e.g. 5.6 = 6

Code:

Steps 1 - 3

Code: Select all

ROUND((FLOOR(DAY_DIFFERENCE(FIRST_DAY_OF_MONTH(CURRENT_DATE),CURRENT_DATE+6) / 7) * 5 + 

Steps 4 - 5

Code: Select all

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

Code: Select all

+ 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

Code: Select all

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)  

[BobK]

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.

Take a look at the MIN2 function. It takes 2 numbers and returns the smallest of the two.
for example:
MIN2(YourRemainder, 5)
will return 5 if YourRemainder is 6 or more or it will return YourRemainder if YourRemainder is 5 or lower.

Also, to calculate the remainder of your first division, take a look at the MOD function. The MOD function returns the remainder of the first number divided by the second number.

Both of these functions are explained in the "Mathematical Functions" section of the AwareIM User Guide.