SQLTeam.com | Weblogs | Forums

Greatest Function in 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.