|
View previous topic :: View next topic
|
| Author |
Message |
leondan22
New User
Joined: 13 Dec 2017
Posts: 2
Location: USA
|
|
|
|
We receive an employee file from an outside entity in fixed block format. Record length = 36. There can be multiple records for an employee if they work in more than one position. The key fields to group on are AGENCY_CD and EMPLOYEE_ID. Our objective is to combine multiple records for an employee into 1 record, given the following criteria:
Select non-TEMP (permanent) position first.
If multiple records for an employee are non-TEMP or all are TEMP, select most recent appointment date.).
If all records have the same appointment date, select the highest position ID (which is unique).
It would be easy enough to group on AGENCY_CD and EMPLOYEE_ID, sort the records per the criteria above and then SUM to remove the duplicate records, but there's a catch. Each record has 4 privacy indicators with a value of 'Y' or 'N'. We want the strictest privacy indicator value ('Y') for each indicator for that employee in our output record. Example (focusing on the record with AGENCY_CD = 110000 AND EMPLOYEE_ID = 000000002):
INPUT:
| Code: |
AGENCY_CD EMPLOYEE_ID POSITION_ID TEMP_IND APPT_DT PRIVACY_1 PRIVACY_2 PRIVACY_3 PRIVACY_4
110000 000000001 111111 N 2000-08-01 N N N N
110000 000000002 222222 N 2014-01-22 Y N Y N
110000 000000002 333333 Y 2017-11-03 N Y N N
210000 000000001 121212 N 2015-07-15 N N N N
|
DESIRED OUTPUT:
| Code: |
AGENCY_CD EMPLOYEE_ID POSITION_ID TEMP_IND APPT_DT PRIVACY_1 PRIVACY_2 PRIVACY_3 PRIVACY_4
110000 000000001 111111 N 2000-08-01 N N N N
110000 000000002 222222 N 2014-01-22 Y Y Y N
210000 000000001 121212 N 2015-07-15 N N N N
|
Any help would be greatly appreciated! |
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2023
Location: USA
|
|
|
|
I'm lazy to test it, but try to use MAX operation in SORT, for your privacy columns.
'Y' value is greater than 'N' when considered in format BI. |
|
| Back to top |
|
|
leondan22
New User
Joined: 13 Dec 2017
Posts: 2
Location: USA
|
|
|
|
| sergeyken wrote: |
I'm lazy to test it, but try to use MAX operation in SORT, for your privacy columns.
'Y' value is greater than 'N' when considered in format BI. |
For the employee in my example with multiple position IDs, I would select POSITION_ID 222222 because it is the non-temporary position. PRIVACY_1 has a value of 'Y', so if I sort by max value for PRIVACY_1, all is good. However, the PRIVACY_2 indicator for that record has a value of 'N' whereas PRIVACY_2 indicator for that same employee in POSITION_ID 333333 has a value of 'Y'. Since I need the strictest value for each of the privacy indicators, I would need to replace the value of the PRIVACY_2 indicator for POSITION_ID 222222 with the PRIVACY_2 value found in POSITION_ID 333333. I hope that makes sense!  |
|
| Back to top |
|
|
|
|
