tanguduk
Warnings : 1
New User
Joined: 29 Aug 2005
Posts: 14
|
|
|
|
My requirment is this ,
I have two tables 216 and 240 the have the fields like this
| Code: |
PASR216A_EPEPTLS
EMP_NR
EA_YEAR
EA_MONTHS_WRKD
EA_HOURS_WRKD
EA_HOURS_EQUIV
EA_SSF_HOURS_EQUIV
EA_SSG_HOURS_EQUIV
EA_PILOT_MTHS_WKD |
and
| Code: |
PASR240A_PEPHIST
EMP_NR
PH_STRT_DTE
PH_END_DTE
PH_PEP_TYP
PH_PAS_PLN_NR
PH_WORK_GRP
PH_PORT_CDE |
My requirement is I will expalin with an example
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
| Code: |
SELECT DISTINCT A.EMP_NR, B.EMP_NR,
A.EA_YEAR,
B.PH_STRT_DTE,B.PH_END_DTE,
A.EA_MONTHS_WRKD,
B.PH_PEP_TYP
FROM
PASR216A_EPEPTLS A,
PASR240A_PEPHIST B
WHERE
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 |
|
