0

I need to create a daily automation to query contacts who's status in All subscribers changed to Unsubscribed last 24 hours. I am totally new to query, but I tried this..

SELECT 
EmailAddress,
SubscriberKey,
Status,
EventDate as 'UnsubscribeDate',
FROM
_ListSubscribers 
WHERE
ListID = 305
AND
Status = 'Unsubscribed'
AND
EventDate > dateadd(hh,-24,getdate())

I received 'Errors: Incorrect syntax near the keyword 'FROM'.' message. Could someone please guide me through what is wrong? Thank you!

Improve this question

2 Answers 2

Reset to default
1

First run the below without filter DateUnsubscribed > dateadd(hh,-24,getdate())

SELECT 
EmailAddress,
SubscriberKey,
Status,
DateUnsubscribed as 'UnsubscribeDate'
FROM
_ListSubscribers 
WHERE
ListID = 305
AND
Status = 'Unsubscribed'

Once it returns the result then try running below

SELECT 
EmailAddress,
SubscriberKey,
Status,
DateUnsubscribed as 'UnsubscribeDate'
FROM
_ListSubscribers 
WHERE
ListID = 305
AND
Status = 'Unsubscribed'
AND
DateUnsubscribed > dateadd(hh,-24,getdate())
Improve this answer
1
  • Happy to help!!
    – Chandan
    Commented Mar 8, 2018 at 10:34
0

Errors: Incorrect syntax near the keyword 'FROM'.'

take out the , before from (i.e. after 'UnsubscribeDate')

SELECT 
EmailAddress,
SubscriberKey,
Status,
EventDate as 'UnsubscribeDate'
FROM
_ListSubscribers 
WHERE
ListID = 305
AND
Status = 'Unsubscribed'
AND
EventDate > dateadd(hh,-24,getdate())
Improve this answer
6
  • Thank you @Data_Kid, now I have another error message...An error occurred while checking the query syntax. Errors: Invalid column name 'EventDate'.
    – HRJ
    Commented Mar 8, 2018 at 1:28
  • Are data views enabled in your account?
    – Chandan
    Commented Mar 8, 2018 at 4:34
  • Seems like you may need to use DateUnsubscribed instead of EventDate.
    – Chandan
    Commented Mar 8, 2018 at 4:37
  • I've tried like this and it ran successfully, but returned zero records. SELECT l.EmailAddress, l.SubscriberKey, l.Status, u.EventDate as 'UnsubscribeDate' FROM [_ListSubscribers] l JOIN [_Unsubscribe] u on l.SubscriberKey = u.SubscriberKey WHERE l.ListID = 305 AND l.Status = 'Unsubscribed' AND u.EventDate > dateadd(hh,-24,getdate())
    – HRJ
    Commented Mar 8, 2018 at 5:05
  • @Chandan, do we need to enable data view?
    – HRJ
    Commented Mar 8, 2018 at 5:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .