DECLARE @DAYS INT = 30 --Notify before the Birthday

SELECT p01.PIN as #Pin,
p01.Firstname as #Firstname,
p01.Lastname as #Lastname,
       p01.Name  AS #Name,
	   CONVERT(VARCHAR, p01.BIRTHDATE, 107) AS #Birthdate,
	   (CONVERT(INT, CONVERT(CHAR(8),DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE())), 112)) - CONVERT(CHAR(8), p01.BIRTHDATE, 112)) / 10000 as #Age,
       --email.TELENO AS #Email,
'sigrid.kviteberg@adonis.no' as #Email,
	   org.NAME as #Company

FROM   pw001p01 p01
Join PWORG org on p01.client = org.numorgid
     and orgtype = 2
	 --and org.numorgid IN (23,10000606,10000607,10000643,10000648,10000649,10000650,10000651,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.BIRTHDATE, 112)) / 10000
	   in (10,20,30,40) --Age filter
	   and 
	   DATEADD(YY,(CONVERT(INT, CONVERT(CHAR(8),DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE())), 112)) - CONVERT(CHAR(8), p01.BIRTHDATE, 112)) / 10000, DATEDIFF(day, 0, p01.BIRTHDATE)) = DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE()))
	   and p01.EMPLOYMENTENDDATE is null