sql - Getting repeated rows for where with or condition -

i trying find employees worked during specific time period , hours worked during time period. query has join employee table has employee id pk , uses effective_date , expiration_date time measures employee's position timekeeping table has pay period id number pk , uses effective , expiration dates.

the problem expiration date in employee table if employee employed date '12/31/9999'. looking employees worked in year , current employees hours worked separated pay periods.

when take condition in account in or statement, duplicates employees have worked time period looking , beyond duplicate records '12/31/9999' , valid employee in time period.

this query using:

select             j.empl_id             ,j.dept             ,j.unit             ,j.last_nm             ,j.first_nm             ,j.title              ,j.eff_dt             ,j.exp_dt             ,tm1.pprd_id             ,tm1.empl_id             ,tm1.exp_dt             ,tm1.eff_dt              --pulling in daily hrs worked              ,(select nvl(sum(((to_number(substr(ti.day_1, 1                             ,instr(ti.day_1, ':', 1, 1)-1),99))*60)+                 (to_number(substr(ti.day_1                             ,instr(ti.day_1,':', -1, 1)+1),99))),0)               pprd_line ti                             ti.pprd_id=tm1.pprd_id               ) "day1"              ---and rest of days work period        pprd_line tm1     join    empl j on tm1.empl_id=j.empl_id       j.empl_id='some id number' --for test purposes, need break down depts-     ,     j.eff_dt >=to_date('1/1/2012','mm/dd/yyyy')     ,     (     j.exp_dt<=to_date('12/31/2012','mm/dd/yyyy')     or     j.exp_dt=to_date('12/31/9999','mm/dd/yyyy') --i think problem might here???     )  group      j.empl_id     ,j.dept     ,j.unit     ,j.last_nm     ,j.first_nm     ,j.title      ,j.eff_dt     ,j.exp_dt     ,tm1.pprd_id     ,tm1.empl_id     ,tm1.doc_id     ,tm1.exp_dt     ,tm1.eff_dt  order     j.eff_dt     ,tm1.eff_dt     ,tm1.exp_dt 

i'm pretty sure i'm missing simple @ point can't see forest trees. can out there point me in right direction?

an example of duplicate records:

for employee 1 year of 2012:

empl_id dept unit   last     first   title   eff date exp date   pprd id empl_id 00001   04   012    babbage  charles somejob 4/1/2012 10/15/2012 0407123 00001   exp date_1 eff date_1 4/15/2012  4/1/2012 

this record repeats 3 times , goes past pay periods in 2012 current pay period in 2013

the subquery use convert time able add hrs , mins compare down line.

i'm going take wild guess , see if want, remember not test there may typos.

if , if not, should read in faq how ask questions. if trying understand question should have been answered within 10 mins. because not clear asking no 1 answer question.

you should include inputs , outputs , expected output in question. data gave not output of select statement (it did not have day1 column).

select    j.empl_id    ,j.dept    ,j.unit    ,j.last_nm    ,j.first_nm    ,j.title     ,j.eff_dt    ,j.exp_dt    ,tm1.pprd_id    ,tm1.empl_id --  ,tm1.exp_dt  can't have these if summing accross multiple records. --   ,tm1.eff_dt    --pulling in daily hrs worked    ,nvl(sum(((to_number(substr(tm1.day_1, 1,instr(tm1.day_1, ':', 1, 1)-1),99))*60)+              (to_number(substr(tm1.day_1,instr(tm1.day_1,':', -1, 1)+1),99))),0)                "day1"              ---and rest of days work period    pprd_line tm1 join    empl j on tm1.empl_id=j.empl_id   j.empl_id='some id number' --for test purposes, need break down depts-    , j.eff_dt >=to_date('1/1/2012','mm/dd/yyyy')     and(j.exp_dt<=to_date('12/31/2012','mm/dd/yyyy') or j.exp_dt=to_date('12/31/9999','mm/dd/yyyy')) group     j.empl_id    ,j.dept    ,j.unit    ,j.last_nm    ,j.first_nm    ,j.title     ,tm1.pprd_id    ,tm1.empl_id    ,tm1.doc_id order     min(j.eff_dt)    ,max(tm1.eff_dt)    ,max(tm1.exp_dt)