×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!
  • Students Click Here

*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

Needing a derived field that can look at ranges...

Needing a derived field that can look at ranges...

Needing a derived field that can look at ranges...

(OP)
Our vendor wants us to add a column that will identify employees that have had:
A- no change, we'd look at coverage action dt is less than prior file date (the previous Monday) to determine that no action has taken place during the past week
N-new business, if coverage election date is>current file date then insert "N".
C-change, if coverage election date is between the last file date(the previous Monday), and current file date(the current Monday) then insert "C".
T-term, if action date on the benefits panel for the coverage end date is between the last file date and current file date then "T".

I'm not sure how to use the decode statement to look at ranges.  Any suggestions?
TIA,

Sarah

RE: Needing a derived field that can look at ranges...

If this is Oracle you could use a case statement.  Here is an example:

CASE
    WHEN "PS_JOB"."EFFDT" > "PS_JOB"."ACTION_DT" THEN '>'
    WHEN "PS_JOB"."EFFDT" < "PS_JOB"."ACTION_DT" THEN '<'
    WHEN "PS_JOB"."EFFDT" = "PS_JOB"."ACTION_DT" THEN '='
    ELSE 'Other'
END

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

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