Greatest Function in SQL

Transact-SQL
21

what are the column names in table PERSON_DATA

22

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
23

thanks yosiasz , no it not working.

24 
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

¿¿

25

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?

Select
PERS.EMPLID
--, 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
, adj.adj_hire_date
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
From (
-- 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

image

26

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

27

Thanks Jeff, I tried your suggestion and its better now, just have to validate data against rest of query.
Pasi.