I need to grab the CI that has the latest date with all the child PI# that go with it. The issue is that some PI# were under other CI# that have been closed already. The results I'm getting are pulling old CI#s and current CI#s. I need to pull all WOs, PIs, MPIs and others that users have inputed time into this year alone. So I pulled out of 2 tables the user name, dates and time recorded for this year. I then joined it to the table that holds the CI information but once I do that it gives more than one time per date and it has old CI# and current CI#s. All help possible is greatly appreciated. :-)

This message has been edited. Last edited by: Kerry,

Please post your Join.

 
JOIN
 AT_TASK_INFO_V3.AT_TASK_INFO.TASK_ID_NBR IN AT_TASK_INFO_V3 TO MULTIPLE
 AT_DRY_INFO_V2.AT_DRY_INFO_V2.TASK_ID_NBR IN AT_DRY_INFO_V2 AS J0
 END
JOIN
 AT_DRY_INFO_V2.AT_DRY_INFO_V2.ASGN_USER_ID_TXT IN AT_TASK_INFO_V3 TO UNIQUE
 AT_EMPL_INFO.AT_EMPL_INFO.EMPL_USER_ID IN AT_EMPL_INFO AS J1
 END
DEFINE FILE AT_TASK_INFO_V3
Min_rec/D12=DRY_MNT_REC;
MINREC/D12=IF Min_rec IS MISSING THEN 0 ELSE Min_rec;
USER_ID/A10=UPCASE(10, AT_DRY_INFO_V2.AT_DRY_INFO_V2.ASGN_USER_ID_TXT, 'A10');
NAME/A75=PRSN_FRST_NME || '' | PRSN_LAST_NME;
NEW_NAMES/A55=LCWORD(55, NAME, NEW_NAMES);
TASKASGN_TXT/P20=EDIT(TASK_ASGN_TXT);
END
TABLE FILE AT_TASK_INFO_V3
BY 'AT_TASK_INFO_V3.AT_TASK_INFO.TASKASGN_TXT'
BY 'AT_TASK_INFO_V3.AT_TASK_INFO.TASK_ASGN_TYPE_TXT'
BY 'AT_TASK_INFO_V3.AT_TASK_INFO.TASK_DESC_TXT'
BY 'AT_DRY_INFO_V2.AT_DRY_INFO_V2.MINREC'
BY 'AT_DRY_INFO_V2.AT_DRY_INFO_V2.DRY_CMPL_DT'
BY 'AT_EMPL_INFO.AT_EMPL_INFO.EMPL_USER_ID'
BY 'AT_EMPL_INFO.AT_EMPL_INFO.NAME'
WHERE DRY_CMPL_DT GE '20090101';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_TASK FORMAT FOCUS INDEX 'TASKASGN_TXT'
 

This creates the hold file with all the time and dates. I then join it to WR_IP_INFO table like this.

 
JOIN
 WR_PI_INFO.WR_PI_INFO.PI_CD IN WR_PI_INFO TO MULTIPLE
 HOLD_TASK.SEG01.TASKASGN_TXT IN HOLD_TASK AS J1
 END
TABLE FILE WR_PI_INFO
BY 'WR_PI_INFO.WR_PI_INFO.PI_CD'
BY 'WR_PI_INFO.WR_PI_INFO.PI_RQST_PRNT_CD' AS 'CI'
BY 'WR_PI_INFO.WR_PI_INFO.PI_NME_TXT' AS 'CI Name'
BY 'WR_PI_INFO.WR_PI_INFO.PI_DESC_TXT' AS 'CI Desc'
BY 'HOLD_TASK.SEG01.TASK_ASGN_TYPE_TXT'
BY 'HOLD_TASK.SEG01.EMPL_USER_ID'
BY 'HOLD_TASK.SEG01.NAME'
BY 'HOLD_TASK.SEG01.MINREC'
BY 'HOLD_TASK.SEG01.DRY_CMPL_DT'
BY 'HOLD_TASK.SEG01.TASK_DESC_TXT'
WHERE DRY_CMPL_DT GE '20090101';
WHERE PI_RQST_PRNT_CD NE '-1';
WHERE WR_PI_INFO.WR_PI_INFO.PI_CD EQ 2413;
WHERE ( WR_PI_INFO.WR_PI_INFO.PI_CD EQ TASKASGN_TXT );
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
 

I hope I did this correctly, the adding of the join you requested.

This check on DRY_CMPL_DT - hasn't that already been done in the first request?
If so, then it may indeed happen that info from the WR_PI_INFO database may be shown of different dates.

What would happen if you turn the join around?
JOIN
HOLD_TASK.SEG01.TASKASGN_TXT IN HOLD_TASK TO (MULTIPLE)
WR_PI_INFO.WR_PI_INFO.PI_CD IN WR_PI_INFO AS J1
END
You'd have to decide if the join is still to MULTIPLE or not, hence the ().

Thanks but I was told the end_dt had to be a certain one. :-) Wish I had known this a few days ago. Now I just need to do a MORE to combine them. Thanks for all you help.

This can be set to resolved. Thanks again!!!