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

[scotton]

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

 

[CharlesCook]

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