Example of SQL task
Updating an action list with action records
Take for instance the example of sending out every day birthday emails, triggered thanks to an action list that contains an action code field with the value 'birthday' and the id of the contact. Every day new records are added to the action list and for each one of these records a birthday email is sent.
To fill this action list automatically, an sql task is created which at a daily basis does an update of the database to update an action list with the birthday of the contacts that have their birthday today. This is what the Stored Procedure for the SQL task looks like
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO ACTION_BIRTHDAY_MAIL (USERID, CREATED_DT, ACTIONCODE)
SELECT
ID,
GETDATE(),
'BIRTHDAY' AS ACTIONCODE
FROM
USERS_RETAIL with (nolock)
WHERE
DATEPART(dd,BIRTHDAY)=DATEPART(dd,GETDATE()) AND
DATEPART(mm,BIRTHDAY)=DATEPART(mm,GETDATE())
END
Calculating purchase metrics
Another example is calculating purchase metrics based on information stored in the orders and orderline tables. These tables do not contain aggregate information and don't store totals for instance.
The Stored Procedure used for the SQL tasks looks like following:
AS
BEGIN
WITH CTE (USERID, FIRST_PURCHASE_DT, LAST_PURCHASE_DT) AS
(
SELECT
O.USERID,
MIN(O.ORDER_DT) AS FIRST_PURCHASE_DT,
MAX(O.ORDER_DT) AS LAST_PURCHASE_DT
FROM DATA_RETAIL_ORDERS O with (nolock)
GROUP BY O.USERID
)
MERGE DATA_RETAIL_PURCHASE_METRICS AS target
USING (SELECT USERID, FIRST_PURCHASE_DT, LAST_PURCHASE_DT FROM CTE) AS source (USERID, FIRST_PURCHASE_DT, LAST_PURCHASE_DT)
ON (target.USERID = source.USERID)
WHEN MATCHED THEN
UPDATE SET
FIRST_PURCHASE_DT = source.FIRST_PURCHASE_DT,
LAST_PURCHASE_DT = source.LAST_PURCHASE_DT,
MODIFIED_DT = GETDATE()
WHEN NOT MATCHED THEN
INSERT (FIRST_PURCHASE_DT, LAST_PURCHASE_DT, USERID, CREATED_DT)
VALUES (source.FIRST_PURCHASE_DT, source.LAST_PURCHASE_DT, source.USERID, GETDATE());
/* 1 year */
WITH CTE (USERID, N_BUYS_1YR, A_PURCHASE_1YR) AS
(
SELECT
L.USERID,
COUNT(*) AS N_BUYS_1YR,
SUM(CONVERT(FLOAT, L.QUANTITY) * L.SALES_PRICE) AS A_PURCHASE_1YR
FROM DATA_RETAIL_ORDERLINES L
WHERE L.ORDER_DT > DATEADD(YEAR, -1, GETDATE())
GROUP BY L.USERID
)
MERGE DATA_RETAIL_PURCHASE_METRICS AS target
USING (SELECT USERID, N_BUYS_1YR, A_PURCHASE_1YR FROM CTE) AS source (USERID, N_BUYS_1YR, A_PURCHASE_1YR)
ON (target.USERID = source.USERID)
WHEN MATCHED THEN
UPDATE SET
N_BUYS_1YR = source.N_BUYS_1YR,
A_PURCHASE_1YR = source.A_PURCHASE_1YR,
MODIFIED_DT = GETDATE()
WHEN NOT MATCHED THEN
INSERT (N_BUYS_1YR, A_PURCHASE_1YR, USERID, CREATED_DT)
VALUES (source.N_BUYS_1YR, source.A_PURCHASE_1YR, source.USERID, GETDATE());
/* 6 months */
WITH CTE (USERID, N_BUYS_6MON, A_PURCHASE_6MON) AS
(
SELECT
L.USERID,
COUNT(*) AS N_BUYS_6MON,
SUM(CONVERT(FLOAT, L.QUANTITY) * L.SALES_PRICE) AS A_PURCHASE_6MON
FROM DATA_RETAIL_ORDERLINES L
WHERE L.ORDER_DT > DATEADD(MONTH, -6, GETDATE())
GROUP BY L.USERID
)
MERGE DATA_RETAIL_PURCHASE_METRICS AS target
USING (SELECT USERID, N_BUYS_6MON, A_PURCHASE_6MON FROM CTE) AS source (USERID, N_BUYS_6MON, A_PURCHASE_6MON)
ON (target.USERID = source.USERID)
WHEN MATCHED THEN
UPDATE SET
N_BUYS_6MON = source.N_BUYS_6MON,
A_PURCHASE_6MON = source.A_PURCHASE_6MON,
MODIFIED_DT = GETDATE()
WHEN NOT MATCHED THEN
INSERT (N_BUYS_6MON, A_PURCHASE_6MON, USERID, CREATED_DT)
VALUES (source.N_BUYS_6MON, source.A_PURCHASE_6MON, source.USERID, GETDATE());
/* 3 months */
WITH CTE (USERID, N_BUYS_3MON, A_PURCHASE_3MON) AS
(
SELECT
L.USERID,
COUNT(*) AS N_BUYS_3MON,
SUM(CONVERT(FLOAT, L.QUANTITY) * L.SALES_PRICE) AS A_PURCHASE_3MON
FROM DATA_RETAIL_ORDERLINES L
WHERE L.ORDER_DT > DATEADD(MONTH, -3, GETDATE())
GROUP BY L.USERID
)
MERGE DATA_RETAIL_PURCHASE_METRICS AS target
USING (SELECT USERID, N_BUYS_3MON, A_PURCHASE_3MON FROM CTE) AS source (USERID, N_BUYS_3MON, A_PURCHASE_3MON)
ON (target.USERID = source.USERID)
WHEN MATCHED THEN
UPDATE SET
N_BUYS_3MON = source.N_BUYS_3MON,
A_PURCHASE_3MON = source.A_PURCHASE_3MON,
MODIFIED_DT = GETDATE()
WHEN NOT MATCHED THEN
INSERT (N_BUYS_3MON, A_PURCHASE_3MON, USERID, CREATED_DT)
VALUES (source.N_BUYS_3MON, source.A_PURCHASE_3MON, source.USERID, GETDATE());
/* TOTAL */
WITH CTE (USERID, T_PURCHASE, AVG_PURCHASE_AMOUNT) AS
(
SELECT
L.USERID
, SUM(CONVERT(FLOAT, L.QUANTITY) * L.SALES_PRICE)
, AVG(CONVERT(FLOAT, L.QUANTITY) * L.SALES_PRICE)
FROM DATA_RETAIL_ORDERLINES L
GROUP BY L.USERID
)
MERGE DATA_RETAIL_PURCHASE_METRICS AS TARGET
USING (
SELECT USERID
, T_PURCHASE
, AVG_PURCHASE_AMOUNT
, GETDATE()
FROM CTE
)
AS SOURCE (USERID, T_PURCHASE, AVG_PURCHASE_AMOUNT, DT)
ON (TARGET.USERID = SOURCE.USERID)
WHEN MATCHED THEN
UPDATE SET
T_PURCHASE = SOURCE.T_PURCHASE
, AVG_PURCHASE_AMOUNT = SOURCE.AVG_PURCHASE_AMOUNT
, MODIFIED_DT = SOURCE.DT
WHEN NOT MATCHED THEN
INSERT (USERID, T_PURCHASE, AVG_PURCHASE_AMOUNT, CREATED_DT)
VALUES (SOURCE.USERID, SOURCE.T_PURCHASE, SOURCE.AVG_PURCHASE_AMOUNT, SOURCE.DT);
END
