Let me try this again with an example. If you find employee 1234 on the 216 table with months worked > 0 for 1999. Thyen you go to the 240 table and you query for employee number = 1234 and PH_Pep_Typ = 'E'. It may return multiple rows. Then you need to see if any part of 1999 is included between the PH_Strt_Dte and PH_End_Dte. If it is, no further action is required for this employee. If it is now, then you need to add this employee to the output list. Include employee number and year from 216 table.
My query I have written is
SELECT DISTINCT A.EMP_NR, B.EMP_NR,
A.EMP_NR = B.EMP_NR
AND A.EA_MONTHS_WRKD > 0
AND B.PH_PEP_TYP = 'E'
AND (A.EA_YEAR < CHAR(YEAR(B.PH_STRT_DTE))
OR A.EA_YEAR > CHAR(YEAR(B.PH_END_DTE)));
but the probelm is
ok, there are 2 records on 240 for emp 136. One is for 1985-1986 and the other is for 1998-1999. Both records on 216 are matching to both records on 240. We are not displaying the matches to the 1998-1999.
240 records because the date range logic eliminates those. But we are displaying both matches to the 1985-1986 records because the date range logic includes those. That is the cause, Please let me know if you have any solution ofrthis
Edited: Please use BBcode when You post some code, that's rather readable...Anuj
Joined: 23 Nov 2006 Posts: 19270 Location: Inside the Matrix
It may help if you post some sample data from the 2 tables and the result you want when your query is run. You would only need to include values for the columns you want to use for selection or in the result. Include rows that have values that would be selected and some that would not.