×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Devired Field with Projected Dates

Devired Field with Projected Dates

Devired Field with Projected Dates

(OP)
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)'

RE: Devired Field with Projected Dates

You will want to do this in a macro.

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: Devired Field with Projected Dates

(OP)
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.  

RE: Devired Field with Projected Dates

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close