Length of Service calculation in OBIEE with years, months and days

aPsikus · May 9, 2018 8:38AM

Hi,

I'm trying to calculate length of service in format years, months and days as difference between Hire date and current date.

I have noticed that there is problem with day count for employees which are hired on day number higher than day number for current date as it is negative.

What I have done wrong?

CAST((TIMESTAMPDIFF(sql_tsi_month, "Person"."Hire Date", current_date)/12) as CHAR(2))||' year(s), ' ||CAST(TIMESTAMPDIFF(sql_tsi_month, "Person"."Hire Date", current_date)-((TIMESTAMPDIFF(sql_tsi_month, "Person"."Hire Date", current_date)/12)*12) AS CHAR(2))||' month(s), '||CAST(current_date - timestampadd(sql_tsi_month, TIMESTAMPDIFF(sql_tsi_month, "Person"."Hire Date", current_date), "Person"."Hire Date") AS CHAR(3))||' day(s)'

I assume that in part responsible for day calculation somehow should be used part responsible for month calculation.

and for example for line one it should be: 65 yrs, 6 months, 23 days instead of 65 yrs, 7 months, -7 days.

I have tried to translate this SQL for OBIEE formula, but something I done wrong.

Joel · May 9, 2018 10:25AM

You’ll need to do this in 2 steps

firstly, use the TIMESTAMPDIFF function to calculate days between the hire date and current date.

Secondly, use a custom format mask to convert number of days to years months days as required.

Also, see this thread - How to calculate the number of days/weeks/months between 2 dates?

aPsikus · May 10, 2018 3:58AM

Hi,

I saw mentioned topic, and didn't found there helpful information.

I'd like to do it in one calculation, so how format mask can do it?

Lee Gledhill · May 11, 2018 5:43AM

Hi,

I had a quick go at your issue and I think your month calculation might be wrong too (It looked a bit spurious in my test)

What I tried to do was achieve timestamps to calculate the date that the number of years plus months from the start date (in my example invoiced date) to then calculate the number of days between the last month and today.

I ended up using the below:

Where column 1 is your start date,

column 2 is today,

column 3 is your start date plus the number of years between the start date and current date,

column 4 is your start date plus years plus months,

column 5 is the formula I used to get number of months and the last column is the number of days from then until now. (what your initial issue was)

Have a try at that - just swap where I've used invoiced date for your hired date.

Here's the formula I used for number of days:

TIMESTAMPDIFF(SQL_TSI_DAY, TIMESTAMPADD(SQL_TSI_MONTH, TIMESTAMPDIFF(SQL_TSI_MONTH, TIMESTAMPADD(SQL_TSI_YEAR, TIMESTAMPDIFF(SQL_TSI_YEAR, "Invoice Date"."Invoiced Fiscal Date", CURRENT_DATE), "Invoice Date"."Invoiced Fiscal Date"), CURRENT_DATE), TIMESTAMPADD(SQL_TSI_YEAR, TIMESTAMPDIFF(SQL_TSI_YEAR, "Invoice Date"."Invoiced Fiscal Date", CURRENT_DATE), "Invoice Date"."Invoiced Fiscal Date")), CURRENT_DATE)

Note* This isn't fully tested but might help you with the logic at least.

Cheers, Lee

aPsikus · May 14, 2018 4:52AM

Hi Lee,

Thank you for detailed explanation and solution proposition, but it didn't solve the issue with days. Still are negative for start date with higher day number than in current date.

Please also note, that for years difference, you can't use SQL_TSI_Year, as calculation will be wrong. It need to be done on month level, and divided by 12.

Lee Gledhill · May 14, 2018 6:34AM

Hi there,

Apologies this wasn't successful though I thought at least the logic may help.
Do you see what I was trying to do? If you can find a Timestamp for the start date plus the number of months of service then you have the timestamp to look for the TIMSTAMPDIFF for the number of days between that timestamp and today.

Cheers,

Lee

Thomas Dodds · May 14, 2018 9:57AM

use the below to get the pieces in the physical mapping of a logical column ...

select sysdate, hiredate, trunc(months_between(sysdate,hiredate) / 12) as years, trunc(months_between(sysdate,hiredate) - (trunc(months_between(sysdate,hiredate) / 12) * 12)) as months, trunc(sysdate) - add_months(hiredate, trunc(months_between(sysdate,hiredate))) as days from emp;

aPsikus · May 16, 2018 7:22AM

Hi Lee,

No need to apologies

I must say that after taking a break from the issue, your post was kind of inspiration for me.

Not sure if I have understood your proposition correctly, but I have tested new idea/concept.

Results still aren't perfect, but at least pretty close. I hope that someone can help me now to write correct CASE statements to have all possibilities secured with calculation.

Here is what I have done and results.

calculate difference in months between start date and end date and present it in years
add result as year to start date
calculate difference in days between date from point 2 and end date and present it in months
add result as month to date from point 2
calculate difference in days between date from point 4 and end date and present in days

Formulas are below.

FLOOR(TIMESTAMPDIFF(sql_tsi_month, "Person"."Hire Date", current_date)/12)timestampadd(sql_tsi_year, CAST(FLOOR(TIMESTAMPDIFF(sql_tsi_month, "Person"."Hire Date", current_date)/12) AS INT),"Person"."Hire Date")FLOOR(TIMESTAMPDIFF(sql_tsi_day, timestampadd(sql_tsi_year, CAST(FLOOR(TIMESTAMPDIFF(sql_tsi_month, "Person"."Hire Date", current_date)/12) AS INT),"Person"."Hire Date"), current_date)/30)timestampadd(sql_tsi_month, CAST(FLOOR(TIMESTAMPDIFF(sql_tsi_day, timestampadd(sql_tsi_year, CAST(FLOOR(TIMESTAMPDIFF(sql_tsi_month, "Person"."Hire Date", current_date)/12) AS INT),"Person"."Hire Date"), current_date)/30) as INT), timestampadd(sql_tsi_year, CAST(FLOOR(TIMESTAMPDIFF(sql_tsi_month, "Person"."Hire Date", current_date)/12) AS INT),"Person"."Hire Date"))floor(timestampdiff(sql_tsi_day,timestampadd(sql_tsi_month, CAST(FLOOR(TIMESTAMPDIFF(sql_tsi_day, timestampadd(sql_tsi_year, CAST(FLOOR(TIMESTAMPDIFF(sql_tsi_month, "Person"."Hire Date", current_date)/12) AS INT),"Person"."Hire Date"), current_date)/30) as INT), timestampadd(sql_tsi_year, CAST(FLOOR(TIMESTAMPDIFF(sql_tsi_month, "Person"."Hire Date", current_date)/12) AS INT),"Person"."Hire Date")), current_date))

Comparison with Excel DATEDIFF function

If month is the same in both dates: results for month is negative. As you can see it means that year should be -1, and month 0.

If day number from start date is higher than day number from current date: days are negative. As you can see it means that month should be -1, and day 30-difference.

As you can see whole problem is not so easy. There are required CASE statements to have correct calculations.

And now I have stuck to set these CASE.

Can someone help me to investigate and write these statements?

aPsikus · May 16, 2018 7:24AM

If nothing will help, solution will be to implement this SQL code in ETL.

But I still have hope that it is possible to solve in presentation layer.

Thomas Dodds · May 16, 2018 7:30AM

If you want the result to be additive (and) re-usable - then yes, implement in the data warehouse. Restate the snapshot fact nightly (as days is the smallest date granularity you are going to) and forget this front end logic. Now you can easily do MAX, MIN, AVG, etc and if you store it over time (by dating the snapshot) you can tell if it is growing or shrinking over time. Much more value that calculating it on the fly at request run-time.

Jerry Casey · May 18, 2018 8:44AM

I have pasted an answer from the Stack-Overflow website by Alex Poole - (see bottom for proper credit).

I've adapted it to OBIEE front-end. Replace my "Project Schedule Milestones"."CON Complete Actual" with your hire_date.

Months Diff: EVALUATE('MONTHS_BETWEEN(%1,%2)' as double,CURRENT_DATE,"Project Schedule Milestones"."CON Complete Actual")

Years: EVALUATE('TRUNC(%1)' as integer, (EVALUATE('MONTHS_BETWEEN(%1,%2)' as double,CURRENT_DATE,"Project Schedule Milestones"."CON Complete Actual")/12 ))

Months: EVALUATE('MOD(%1,%2)' as integer,(EVALUATE('MONTHS_BETWEEN(%1,%2)' as double,CURRENT_DATE,"Project Schedule Milestones"."CON Complete Actual")),12)

Days: 31*(EVALUATE('MONTHS_BETWEEN(%1,%2)' as double,CURRENT_DATE,"Project Schedule Milestones"."CON Complete Actual")-EVALUATE('TRUNC(%1)' as INTEGER,EVALUATE('MONTHS_BETWEEN(%1,%2)' as INTEGER,CURRENT_DATE,"Project Schedule Milestones"."CON Complete Actual"))) NOTE: The fractional remainder on MONTHS_BETWEEN is based on a 31 day month. The MOD value takes actual month lengths into account. The one day difference from your expected result may be due to differing definitions of "between".

Here's Alex Poole's Original Post:

https://stackoverflow.com/questions/29149219/how-to-get-difference-between-two-dates-in-oracle-and-show-it-as-x-years-y-mon

MFA for Oracle Community

Oracle Analytics Cloud and Server

Categories

Length of Service calculation in OBIEE with years, months and days

Answers