DecDeclareDeclare @Days INT = 30 --Notify before the date of Anniversary

SELECT P01.PIN,
       p01.NAME as #Name#FullName,
       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.notest.e-mail' 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 (5,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