View sfmc-sargable-where-clause-comparison.sql
/* do not do this */
select
o.subscriberkey
from _open o
and cast(o.eventdate as date) = convert(date, getDate()-1)
and o.isunique = 1
/* do this instead */
select
o.subscriberkey
View sfmc-trim-column-value-with-left.sql
select
, m.emailAddress
, left(m.firstName,50) as firstName
from Master_Subscriber m
View sfmc-handle-null-column-values-with-isnull.sql
select
s.[SubscriberKey] [Subscriber Key]
, s.EmailAddress [Email Address]
, case
when r.RegionID = '1' then 'Omaha'
when r.RegionID = '2' then 'Kansas City'
when r.RegionID = '4' then 'Texas'
else 'Online'
end as [Store Location]
, isnull(r.RegionId,'0') RegionID
View sfmc-deduplication-with-row-number-partition.sql
select
x.emailaddress
, x.subscriberkey
from (
select
w.emailaddress
, w.subscriberkey
, w.insertDate
, row_number() over (partition by w.subscriberkey order by w.insertDate asc) ranking
from Welcome_Trigger w
View sfmc-asc-xml.html
<html>
<head>
<style>
body, a, input {font-family:sans-serif;}
</style>
</head>
<body style="font-family:sans-serif">
%%=now()=%%
<h1>XML Parse with AMPScript</h1>
View sfmc-asc-json.html
<html>
<head>
<style>
body, a, input {font-family:sans-serif;}
</style>
</head>
<body style="font-family:sans-serif">
%%=now()=%%
<h1>JSON Parse with GTL</h1>
%%[
View sfmc-sql-geo-distance.sql
SELECT
x.emailAddress
, x.firstName
, x.zip
, x.store_zip
, x.store_phone
, x.ranking
, x.distance /* added 2014-11-13 */
from (
SELECT
View sfmc-dedupe-external-rows.html
%%[
var @i, @rows, @row, @productName, @prevProductName, @rowsToReturn
var @rowsToReturn = 0 /* up to 2000 */
SET @rows = LookupOrderedRows("SubscriberProducts" ,@rowsToReturn, "ProductName asc", "EmailAddress",emailaddr)
set @prevProductName = ""
FOR @i = 1 TO RowCount(@rows) DO