Correct Syntax for AEP Query - Counts | Community
Skip to main content
M
montezh2001
Level 4
November 14, 2024
Solved

Correct Syntax for AEP Query - Counts

  • November 14, 2024
  • 1 reply
  • 829 views

Hello, 

 

I'm trying to get a count of all the profiles on my dataset where the DOB is less than 21 years old from the Current Date. I'm trying to use the following syntax in the Query service but get an error: 

 

SELECT
person.birthDate AS birthDate,
COUNT(1) eventCount
FROM
dataset_aep_coreprofile
WHERE
DATE_ADD (YEAR, 21, person.birthDate) > CURRENT_DATE;

GROUP BY
person.birthDate

 

ErrorCode: 42703 queryId: 9be3e30e-29df-49a9-b78e-06eb65e0cf73 Column YEAR does not exist.

 

I'm having a hard time understanding what SQL this service is using. Example: I'm used to using GETDATE() but I have to use CURRENT_DATE in this query. 

 

Please advise. 

 

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by DavidRoss91

    @montezh2001 try this:

    SELECT

    person.birthDate AS birthDate,

    COUNT(1) AS eventCount

    FROM

    dataset_aep_coreprofile

    WHERE

    person.birthDate + INTERVAL '21 years' > CURRENT_DATE

    GROUP BY

    person.birthDate;

     

    1 reply

    DavidRoss91
    Community Advisor
    DavidRoss91Community AdvisorAccepted solution
    Community Advisor
    November 14, 2024

    @montezh2001 try this:

    SELECT

    person.birthDate AS birthDate,

    COUNT(1) AS eventCount

    FROM

    dataset_aep_coreprofile

    WHERE

    person.birthDate + INTERVAL '21 years' > CURRENT_DATE

    GROUP BY

    person.birthDate;

     

      M
      MontexHo
      Level 2
      November 14, 2024

      @davidross91 - that code was 99% accurate. Here's the final code that worked: 

      SELECT
      person.birthDate AS birthDate,
      COUNT(1) AS eventCount
      FROM
      dataset_aep_coreprofile
      WHERE
      person.birthDate + INTERVAL '21 YEAR' > CURRENT_DATE
      GROUP BY
      person.birthDate;

       

       

      The code didn't like 'years' syntax. YEAR is the syntax to use. Thank you still, this lead me to the right coding to use. 

       

        DavidRoss91
        Community Advisor
        DavidRoss91Community Advisor
        Community Advisor
        November 15, 2024

        @montexho No problem at all, happy to help. This doc could be handy in the future for you:

        SQL Syntax in Query Service | Adobe Experience Platform

        Terms & ConditionsAccessibility statement

        Loading footer...