0

I am trying to create a query to return only the last date of a set of records. The process is: A task is created in Salesforce and triggers a journey. In the task, agents can choose to send an SMS now or schedule. The time they are scheduling in Salesforce is coming to Marketing Cloud one hour behind (e.g. SMS Scheduled at 15:00, in Journey Builder DE is shows Scheduled at 14:00).

I am using Wait By Attribute in JB and the lookup should be to the latest entry to the DE resulting from the query below.

SELECT
    [Task:Id],
    [Task:Who:Contact:Id],
    (SELECT
        CASE
            WHEN CAST([Task:SMS_Scheduled_at__c] AS DATETIME) <= DATEADD(hh,1,CAST(getdate() AS DATETIME))
                THEN max([Task:SMS_Scheduled_at__c])
            ELSE max(DATEADD(hh,1,[Task:SMS_Scheduled_at__c]))
        END
    ) AS 'Task:SMS_Scheduled_at__c'
FROM [SMS Campaigns JB]
GROUP BY [Task:Id], [Task:Who:Contact:Id], [Task:SMS_Scheduled_at__c]

I am not proficient in SQL so I am probably missing something simple, but I can't make it return only the latest date, it's returning all the rows. I tried to Group by only Task:Id but then I get the error:

Errors: Column 'C100016834.SMS Campaigns JB.Task:Who:Contact:Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

  • Can you confirm - do you only want to return the 1 record with the latest date? Or did you want the latest date for each of the records in [SMS Campaigns JB]? – Cameron Robert Oct 10 '19 at 11:41
  • 1
    Do not group on [Task:SMS_Scheduled_at__c], this is stopping the MAX function from aggregating the rows – zuzannamj Oct 10 '19 at 11:51
  • @CameronRobert indeed, I'd like only 1 record with the latest date per contact (I'm basing this on the fact no more than one SMS would be sent to a contct for the same campaign for at least one week. – Luiz Fernando Xavier Oct 10 '19 at 11:55
  • @zuzannamj I tried removing it from the Group By but it will fail validation with the code I added in the end of my post. – Luiz Fernando Xavier Oct 10 '19 at 11:55
2

Try these:

SELECT
    [Task:Id]
    ,[Task:Who:Contact:Id]
    ,max(DATEADD(hh,1,[Task:SMS_Scheduled_at__c])) AS 'Task:SMS_Scheduled_at__c'
FROM [SMS Campaigns JB]
GROUP BY [Task:Id], [Task:Who:Contact:Id]

If you need to add 1 hour to the schedule time, then try nesting the query before grouping, as below:

SELECT
    [Task:Id]
    ,[Task:Who:Contact:Id]
    ,max([Task:SMS_Scheduled_at__c]) AS 'Task:SMS_Scheduled_at__c'
FROM (
    SELECT
        [Task:Id]
        ,[Task:Who:Contact:Id]
        CASE
            WHEN CAST([Task:SMS_Scheduled_at__c] AS DATETIME) <= DATEADD(hh,1,CAST(getdate() AS DATETIME)) THEN [Task:SMS_Scheduled_at__c]
            ELSE DATEADD(hh,1,[Task:SMS_Scheduled_at__c])
        END AS 'Task:SMS_Scheduled_at__c'
    FROM [SMS Campaigns JB] a
    ) b
GROUP BY [Task:Id], [Task:Who:Contact:Id]
  • Hi Cameron, thanks for sending this over. Unfortunately both queries still returned all the rows from the original DE. But looking at your query I realized I don't really need Task:Id. I did a test removing it from SELECT and then it works to get only the latest record, which actually serves the purpose. I guess I could still try doing a sub-query to get the Task:Id that matches these last records but it doesn't seem to be necessary for what I am trying to achieve (but if you have any suggestion, it's always welcome). – Luiz Fernando Xavier Oct 10 '19 at 12:53
  • Glad I could help. SQL is a reasonably forgiving language, so play around and try the different way to bring data together! – Cameron Robert Oct 10 '19 at 13:47

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.