0

Hi everybody iam facing some issues. I want to create a sql activity which gives me the most recent record based on my date field called (created date) from DE: integration 2play and saves those records (green) only to new DE.

* Due to comments i changed code accordingly but still getting error: Incorrect syntax near the keyword 'as'.*

Any suggestions?

Please find my code below.

Hope any body can help me out.

enter image description here

 SELECT ID1, ID2, Name, [Phone number], [last login], MAX[created date] as [created date], [email address]    
    FROM [integration 2play]           
    GROUP BY ID1, ID2, Name, [Phone number], [last login], [email address]
|improve this question
  • What issues are you seeing with the code you have? Is it erroring, returning incorrect answers or null? etc... – Gortonington Jun 20 '18 at 12:01
  • Hi @Gortonington sorry i got the error: An error occurred while checking the query syntax. Errors: Incorrect syntax near the keyword 'as'. From Data Extension is called "integration 2 play" – Werner Holm Jun 20 '18 at 12:59
2

It looks like just some syntax errors. As @Data_Kid pointed out there were issues with extra commas and you needed to bracket or double quote the DE name.

He was missing one item though, Max[Created Date] needed parenthesis around it to utilize the Max function.

Example:

SELECT ID1
, ID2
, Name
, [Phone number]
, [last login]
, MAX([created date]) as [created date]
, [email address]    
    FROM [integration 2play]           
    GROUP BY ID1
    , ID2
    , Name
    , [Phone number]
    , [last login]
    , [email address]
|improve this answer
  • Snap!!+1 was stumped – Samuel Dare Jun 20 '18 at 15:35
  • 1
    of course both of you :) @Data_Kid & Gortonington – Werner Holm Jun 20 '18 at 15:43
  • I have one more question. If iwould have a second "Date" field and i have to compare both and capture only the most recent date out of these two its ONLY archievalble by using CASE statement and when NULL value using COALESCE? Am i right on that? – Werner Holm Jun 21 '18 at 0:44
  • Like: SELECT ID1, ID2, Name, [Phone number], MAX[last login] as [last login], MAX[created date] as [created date], [email address] FROM [integration 2play] GROUP BY ID1, ID2, Name, [Phone number], WILL NOT WORK as 2 MAX statements are used? @Data_Kid :) – Werner Holm Jun 21 '18 at 0:52
1

You need to wrap the DE name in brackets [integration 2play] and take out the ,' from after [email address]

 SELECT ID1, ID2, Name, [Phone number], [last login], MAX[created date] as [created date], [email address]    
    FROM [integration 2play]
    WHERE [created date] >= DATEADD(dd,-1,getdate())

    GROUP BY ID1, ID2, Name, [Phone number], [last login], [email address]

Also you dont need the WHERE [created date] >= DATEADD(dd,-1,getdate()) since you are using the MAX(created date], it becomes redundant.

SELECT ID1
, ID2
, Name
, [Phone number]
, [last login]
, MAX[created date] as [created date]
, [email address]    
FROM [integration 2play]
GROUP BY ID1, ID2, Name, [Phone number], [last login], [email address]
|improve this answer
  • thank you i changed it i now receive: An error occurred while checking the query syntax. Errors: Incorrect syntax near the keyword 'as'. – Werner Holm Jun 20 '18 at 13:06
  • also take out the ,' after the [email address]` – Samuel Dare Jun 20 '18 at 13:08
  • thank you for the hints but sadly it still says: An error occurred while checking the query syntax. Errors: Incorrect syntax near the keyword 'as'. – Werner Holm Jun 20 '18 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.