Dynamic SQL Top() clause in SFMC

Question

I am looking to automate an IP Warm Up process. We have our plan that defines the number of contacts that should be sent to on each day across each domain group.

I would like to automate this, so am looking at how I can create a dynamic Top() clause that will select the correct number of contacts to push into a Send DE each day based on the plan.

DE: Warm_Up_Plan
Fields: Domain_Group | Volume | Send_Date
Example: Gmail | 400 | 9/11/2023 12:00:00 AM

DE: Gmail_Group_Contacts DE
Fields: Id | Email | Domain_Group
Example: 001 | [email protected] | Gmail

Send_Out DE
Fields: Id

The automation should look at the record in Warm_Up_Plan with today's date and the correct domain, and then select that many people from the Gmail_Group_Contacts. I have tried doing something like the below SQL query, however it errors with the message:

SELECT TOP (w.volume) g.id FROM Gmail_Group_Contacts g
INNER JOIN Warm_Up_Plan w
on w.Domain_Group = g.Domain_Group
WHERE w.Send_Date = '9/11/2023 12:00:00 AM'
AND w.Domain_Group = 'Verizon'

Error Message: The reference to column "volume" is not allowed in an argument to a TOP, OFFSET, or FETCH clause. Only references to columns at an outer scope or standalone expressions and subqueries are allowed here.

How can I make the Top() clause dynamic so that it pulls through the value in this manner?

Answer 1

Nice solution by Adam.

a different alternative I sometimes use is to store the "TOP" number in a control table with key/value pairs, and look it up based on some condition (e.g. id could be each day, considering timezones etc).

controlSettings (pseudo dates)

id,controlparameterKey,controlParameterValue
today,batchsize,5000
tomorrow,batchsize,10000
day after tomorrow,batchsize,15000

(pseudo date condition)

SELECT TOP 
  (SELECT 
    CAST(controlParameterValue AS INT) AS intValue



  FROM controlSettings 
  WHERE controlParameterKey = 'batchsize'
  AND id = today
  )


whateverField
FROM 
YourDE

if your controlParameterValue field is a number, you can omit the CAST AS, and just SELECT controlParameterValue.

I included the CAST as I usually use the control table for more than just one kind of parameter, and "controlParameterValue" might be a text field. My usecase is not IP warming, just wanted to chip that in as well as I frequently come back to this solution myself and I have to look it up somewhere ;)

Answer 2

You could do use the row_number() window function to assign a sequential number to each row (in random order) and then only select rows that less than or equal to your volume value. Something like this:

SELECT 
  x.id
, x.rowNum
from (

  select
    g.id 
  , w.volume
  , row_number() over (order by newid()) as rowNum
  FROM Gmail_Group_Contacts as g
  INNER JOIN Warm_Up_Plan as w on w.Domain_Group = g.Domain_Group
  WHERE w.Send_Date = '9/11/2023 12:00:00 AM'
  AND w.Domain_Group = 'Verizon'

) x
where 1=1
and x.rowNum <= x.volume

Answer 3

A bit late to the party, but might help future readers.

You can use the structure SELECT TOP (w.volume) g.id FROM Gmail_Group_Contacts g, if you wrap it in a CROSS APPLY statement.

SELECT g.* FROM Warm_Up_Plan w
CROSS APPLY (
    SELECT TOP (w.volume) g.id
    FROM Gmail_Group_Contacts g
    WHERE w.Domain_Group=g.Domain_Group
) g
WHERE w.Send_Date = '9/11/2023 12:00:00 AM'
AND w.Domain_Group = 'Verizon'