what are the column names in table PERSON_DATA
I think you need to move that where clause outside the cross apply?
Cross Apply (Select max(jobx.FDT) As FDT From Job jobx And jobx.action = 'XFR' And jobx.action_reason = 'TAF' ) As jobx Where jobx.EMPLID = pers.emplid
thanks yosiasz , no it not working.
Cross Apply (Select max(jobx.FDT) As FDT, emplid From Job jobx where jobx.action = 'XFR' And jobx.action_reason = 'TAF' group by fdt, emplid ) As jobx Where jobx.EMPLID = pers.emplid
yosiasz, I added your portion in bold, and added the WHERE clause to the end of second CROSS APPLY because it wasnt working for 2nd CROSS APPLY and, had to change the 2nd cross apply (Bolded) query to add CAST since I was getting : Operand type clash: int is incompatible with date. now its running but getting so many repeats for each/same EMPLID?? and query runs for long time, dont think the data is correct?
--, GREATEST(NVL(( Select max(JOBX.FDT)
-- From Job JOBX
-- Where JOBX.EMPLID = PERS.EMPLID
-- And JOBX.ACTION = 'XFR'
-- And JOBX.ACTION_REASON = 'TAF'), '0001-01-01')
-- , NVL(EMPL.HIRE_DT,'0001-01-01')
-- , NVL(EMPL.REHIRE_DT,'0001-01-01'))
-- ) As ADJ_HIRE_DATE ---SQL
From PERSON_DATA PERS, EMPLOYMENT EMPL
--Cross Apply (Select max(jobx.FDT) As FDT
-- From Job jobx
-- Where jobx.EMPLID = PERS.EMPLID
-- And jobx.action = 'XFR'
-- And jobx.action_reason = 'TAF'
-- ) As jobx
Cross Apply (Select max(jobx.FDT) As FDT, emplid
**From Job jobx **
where jobx.action = 'XFR'
And jobx.action_reason = 'TAF'
group by Fdt, emplid
) As jobx
---Where jobx.EMPLID = pers.emplid
Cross Apply (Select max(v.max_date) As adj_hire_date
-- Values (isnull(jobx.FDT,0)) -- default to 1900-01-01
values (isnull(jobx.FDT,cast('0001-01-01' as date)) )
-- , (isnull(empl.hire_dt, 0))
, (isnull(empl.hire_dt,CAST('0001-01-01' as date)) )
-- , (isnull(empl.rehire_dt, 0))
, (isnull(empl.REHIRE_DT,CAST('0001-01-01' as date)) )
) As v(max_date)) As adj
Where jobx.EMPLID = pers.emplid
You don't need the emplid in the first cross apply. Change the join to standard INNER JOIN syntax between the person and employment tables.
Using comma is causing your problems
Thanks Jeff, I tried your suggestion and its better now, just have to validate data against rest of query.