Tabellenübersicht

Tabelle 'namebasics'

Spalten:
nconst
primaryName
birthYear
deathYear
primaryProfession
knownForTitles

Tabelle namebasics

Tabelle 'titlebasics'

Spalten:
tconst
titleType
primaryTitle
originalTitle
isAdult
startYear
endYear
runtimeMinutes
genres

Tabelle titlebasics

Tabelle 'titleprincipals'

Spalten:
tconst
ordering
nconst
category
job
characters

Tabelle titleprincipals

Der komplett zusammengesetzte Query:

SELECT titlebasics.primaryTitle AS Filmtitel, titlebasics.startYear AS Erscheinungsjahr, titleprincipals.characters AS Rolle, titlebasics.genres AS Genre
FROM titlebasics
INNER JOIN titleprincipals ON titlebasics.tconst=titleprincipals.tconst
INNER JOIN namebasics ON titleprincipals.nconst=namebasics.nconst
WHERE namebasics.primaryName='Keanu Reeves' ORDER BY Erscheinungsjahr LIMIT 0, 400;

Mit farblicher Hervorhebung der Quelltabellen:

SELECT titlebasics.primaryTitle AS Filmtitel, titlebasics.startYear AS Erscheinungsjahr, titleprincipals.characters AS Rolle, titlebasics.genres AS Genre
FROM titlebasics
INNER JOIN titleprincipals ON titlebasics.tconst=titleprincipals.tconst
INNER JOIN namebasics ON titleprincipals.nconst=namebasics.nconst
WHERE namebasics.primaryName='Keanu Reeves' ORDER BY Erscheinungsjahr LIMIT 0, 400;

Führt zu der Ausgabe:

Query Result

Zeitspannen:

/* Find all Subscribers registered to an event within the next month */

SELECT
SubscriberKey,
EmailAddress
FROM [db_table_or_data_extension]
WHERE EventDate BETWEEN GETDATE() AND DATEADD(MONTH, 1, GETDATE())



/* Find all Subscribers registered to an event within the past 2 months */

SELECT
SubscriberKey,
EmailAddress
FROM [db_table_or_data_extension]
WHERE EventDate BETWEEN DATEADD(MONTH, -1, GETDATE()) AND DATEADD(MONTH, -2, GETDATE())

SSJS


/* Add data to a data extension */

<script runat="server">
/* https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/ssjs_platformDataExtensionInsertData.html */
var rows = Platform.Function.InsertData("CustomerData",["FirstName","LastName","Email"],["Alfred E.","Neumann","alfredeneumann@example.com"]);
var rows = Platform.Function.InsertData("vossi_debugLog_01",["message"],["Lorem ipsum dolor sit amet."]);
//var rows = Platform.Function.InsertData("CustomerData",["FirstName","LastName","Email"],["Joe","Smith","jsmith@example.com"]);
</script>

/* Add data to a data extension 2 */

<script runat="server">
/* https://ampscript.xyz/how-tos/read-data-extension-with-ssjs/ */
var rows = Platform.Function.InsertData("CustomerData",["FirstName","LastName","Email"],["Alfred E.","Neumann","alfredeneumann@example.com"]);
var rows = Platform.Function.InsertData("vossi_debugLog_01",["message"],["Lorem ipsum dolor sit amet."]);
//var rows = Platform.Function.InsertData("CustomerData",["FirstName","LastName","Email"],["Joe","Smith","jsmith@example.com"]);
</script>


<script runat="client">
console.log('Lorem ipsum on your JS console.');
</script>

/* Add client side console output for debugging */

/* https://ampscript.xyz/how-tos/read-data-extension-with-ssjs/ */


<script runat="server">
Platform.Load("core","1.1");
Variable.SetValue("name","Mister Blue");
</script>


<script runat="client">
console.log('%%=v(@name)=%%');
</script>

/* further reading: https://joernberkefeld.github.io/SFMC-Cookbook/ssjs/ */