Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

need alert query when department changes in Employee Master Data

Hi experts 

i had created a query to get alert on current day base,,,when an employees Department changes to new department

but in Alert , Old department Code is appearing instead of Name,, And also when i run the query in SQL on employee is repeating more than once

pls find the below attached Query

------------------------------------------------

SELECT distinct T0.[U_EMPOLD_ID] as'Employee ID', T0.[empID] as 'SAP ID', isnull(T0.[firstName],'')+' '+isnull(T0.[middleName],'')+' '+isnull(T0.[lastName],'') as 'Full Name',

T2.[Remarks]as 'New Dept',T1.[Dept]as 'Old Dept',T0.[U_SponsName] As 'Sponsor Name',T0.[U_JoinDate] As 'Enrollement Date',T1.[UpdateDate] as 'Update Date'

FROM OHEM T0 

INNER JOIN OUDP T2 ON T0.dept=T2.Code

INNER JOIN AHEM T1 ON T2.Code = T1.dept

WHERE getdate() >= T1.[UpdateDate] AND (T1.[UpdateDate]+2) >= getdate() AND

T2.[Remarks]<> T1.[Dept] AND T1.[UpdateDate] >= [%0]

-----------------------------------------

Regards

replied

Hi Shahan,

Just add the underline in your SQL and add the field T3.Name in the fields you want to see.

select * from AHEM T0 inner join OHEM T1 on T0.EmpID = T1.empID inner join OUDP T2 ON T1.dept=T2.Code

Inner join OUDP T3 on T3.Code = T0.Dept

Where T0.[UpdateDate] >= getdate() -3 and T0.dept <> T1.dept

Please check.

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question