sqlsalesforcesalesforce-marketing-cloud

Salesforce Marketing Cloud - SQL query to filter a Data Extension to only get people that is their 65th bd in the current year


I have a Sync Data extension that I have to filter.

I need to do a SQL query to filter it to only get people that is their 65th birthday in the current year.

I also have to select this people by record type.

This is what I have:

SELECT *
FROM
(
    SELECT
        *,
        Age = DATEDIFF(yy, Date_of_Birth__c, GETDATE()) -
            IIF(DATEPART(m, Date_of_Birth__c) < DATEPART(m, GETDATE()), 0,
            IIF(DATEPART(m, Date_of_Birth__c) > DATEPART(m, GETDATE()), 1,
            IIF(DATEPART(d, Date_of_Birth__c) > DATEPART(d, GETDATE()), 1, 0)))
    FROM ITH_Patients_Account
) x
WHERE Age = 65 AND RecordTypeId = 'XXXXXXXXXXXXXXX'

I tried the above SQL query

The result was a valid syntax but does not work.


Solution

  • only get people that is their 65th birthday in the current year.

    I can simplify the above query as :

    SELECT *
    FROM ITH_Patients_Account
    WHERE RecordTypeId = 'XXXXXXXXXXXXXXX'
    AND YEAR(DATEADD(yy, 65, Date_of_Birth__c)) = YEAR(GETDATE())
    AND DATEADD(yy, 65, Date_of_Birth__c) <= GETDATE();
    

    This shall give you records of people whose 65th birthday falls in the current year or has already passed in the same year only.