Devired Field with Projected Dates

[kub901]

Hello All,

Can someone please help me with this problem?
I'm using MS SQL server. These are the current devrived fields I have created but unsure how to do the rest.

What I'm trying to do is enter a projected date to get years of Service.

I’m trying to create a report that would give me the years, month, days when I subtract it from another field. Here is an example.



HIRE_DT field – PROJ_DATE = PROJ_YRS_SRVC



HIRE_DT field = In PS_EMPLOYEES table

PROJ_DATE = Report Variable (date format)

PROJ_YRS_SRVC = Derived Field with this formula (DATEDIFF(yy,"PS_EMPLOYEES"."HIRE_DT" ,<<Proj_Date, "{d '"yyyy-mm-dd"'}">> )



In the DATEDIFF formula I can change “yy” to either “mm” or “dd” but I want to display all 3 so it will show like this (24 years, 1 months, 21 days)



Is there a way to do this? Thanks for your help.


'('+STR(( DATEDIFF(year,"PS_EMPLOYEES"."HIRE_DT" ,<<Proj_Date, "{d '"yyyy-mm-dd"'}">> ) ))
+' Years, ' +STR((DATEDIFF(month,"PS_EMPLOYEES"."HIRE_DT" ,<<Proj_Date, "{d '"yyyy-mm-dd"'}">> ) ))
+' Months, ' + STR((DATEDIFF(Day,"PS_EMPLOYEES"."HIRE_DT" ,<<Proj_Date, "{d '"yyyy-mm-dd"'}">> ) ))+ ' Days)'

 

[CharlesCook]

You will want to do this in a macro.

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[kub901]

Sorry for being ignorant but would be macro be inside of Reportsmith? I figured out how to convert this in Excel and was seeing if there's a way to do it inside ReportSmith.

 

[CharlesCook]

Sorry,

Yes I would use a Macro Derived Field in ReportSmith. The macro lang is an old version of basic. It gives you the ability to do If Then Else logic and loops etc. The mail difference between an SQL derived field and a Macro derived field is in SQL you must do all of the work with 1 command. You can nest commands in commands in SQL but it is still restricting of your logic. In a Macro command you can write a small program to do what you need. The downside to macros in ReportSmith is the macro lang is buggy, they are SLOOOOOOOW and hard to debug and every once in a while they don't work.

Charles

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting