DecDeclare @Days INT = 30 --Notify before the date of Anniversary
SELECT P01.PIN,
p01.NAME as #Name,
p01.FIRSTNAME as #Firstname,
p01.LASTNAME as #Lastname,
p01.EMPLOYMENTSTARTDATE as #EmploymentStartDate,
(CONVERT(INT, CONVERT(CHAR(8),DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE())), 112)) - CONVERT(CHAR(8), p01.EMPLOYMENTSTARTDATE, 112)) / 10000 as #YearsInService,
CONVERT(VARCHAR(15),DATEADD(YYYY,(CONVERT(INT, CONVERT(CHAR(8),DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE())), 112)) - CONVERT(CHAR(8), p01.EMPLOYMENTSTARTDATE, 112)) / 10000, DATEDIFF(day, 0, p01.employmentstartdate)),107) #AnniversaryDate,
org.Name as #Company,
-- email.TELENO as #Email
'sigrid.kviteberg@adonis.no,camilla.emmerhoff@adonis.no,per.ove.kviteberg@adonis.no' as #Email
FROM PW001P01 P01
Join PWORG org on p01.CLIENT = org.NUMORGID and org.ORGTYPE = 2 and org.NUMORGID in (23,10000606,10000607,10000643,10000648,10000649,10000650,10000662,10000668,10000669) --Include the Numorgid of the companies
LEFT JOIN PW001P0T email ON email.PIN = p01.PIN AND email.TELETYPE = 6 AND email.TELEPRIORITY IS NOT NULL AND NOT EXISTS (SELECT 1 FROM PW001P0T t WHERE t.PIN = email.PIN AND t.TELETYPE = email.TELETYPE AND t.TELEPRIORITY IS NOT NULL
AND (t.TELEPRIORITY < email.TELEPRIORITY OR t.TELEPRIORITY = email.TELEPRIORITY AND t.SEQUENCENO < email.SEQUENCENO))
WHERE (CONVERT(INT, CONVERT(CHAR(8),DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE())), 112)) - CONVERT(CHAR(8), p01.EMPLOYMENTSTARTDATE, 112)) / 10000
in (10,15,20,25,30,35,40,45,50,55,60,65,70) --Years in Service
and DATEADD(YY,(CONVERT(INT, CONVERT(CHAR(8),DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE())), 112)) - CONVERT(CHAR(8), p01.EMPLOYMENTSTARTDATE, 112)) / 10000, DATEDIFF(day, 0, p01.employmentstartdate)) = DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE()))
and p01.EMPLOYMENTENDDATE is null
|
