Marketing Cloud - SQL Group By returning all rows

Question

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.

Answer 1

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]