Questions tagged [sql]

Structured Query Language (SQL) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS)

Filter by
Sorted by
Tagged with
1
vote
1answer
81 views

can i assign and send out coupon codes by query

i have a campaign in which coupon codes should be emailed to new customers who are entered in a data extensions. I already have a coupon data extension with code and IsClaim flag which is FALSE by ...
1
vote
0answers
208 views

Marketing Cloud - using Data Views to create a DE with all customer interactions - email and SMS [closed]

I'm looking to create a data extension with all email and SMS interactions for all subscribers within our SFMC account. Understand this will be a huge query and will need to be segmented in some way - ...
1
vote
0answers
253 views

query counts of 5 different data extension fields

We are looking to query counts of subscribers that unsubscribed from 5 separate fields (or unsubscribe buckets) in our master data set within the past day. We need to ultimately see counts of how ...
1
vote
1answer
139 views

Can i target specific audience in a data extension for an email?

I have a data extension in which i have a list of email address of customers. In that data different types of customers. I know one option is to create filter if i want to send email to specific ...
1
vote
0answers
392 views

Total sent in last 7 days from data view

I am using this query to pull how many emails were sent in last 7 days. Select count(DISTINCT JobID) AS TotalUniqueSent FROM _Sent WITH (NOLOCK) WHERE DATEDIFF(day,EventDate,GETDATE()) <= 7 But ...
1
vote
1answer
95 views

Marketing Cloud - Replacing Multiple hardcoded values in JOINS with values from Parameter Table

Salesforce Marketing Cloud query activities do not allow variables or temporary tables according to the "SQL Support" section of this official documentation (http://help.marketingcloud.com/en/...
1
vote
0answers
19 views

Marketing Cloud User and Role data pull

Trying to pull user and role data to cover t eh following. Is there any Reports or SQL out there that can help with this? List of all users Business Unit and what roles are assigned to each user ...
1
vote
1answer
53 views

Pulling data from DE on multiple values of a variable

I have a requirement wherein I have to query a database for the following condition. Database [Report Pull] has let us say 2 columns CustomerID ReportDt I have have to find all the customers ...
1
vote
2answers
807 views

Emailing Report - Automation Studio

Is there a way to Email a Scheduled Report (created using SQL) in Marketing Cloud Automation Studio?
0
votes
3answers
56 views

How to write SQL query to pull only not matching Email IDs

I need to write a query to pull records which Email IDs are not matching in another table. For example, I have a two tables whose structures looks something like this: Table1 ID | Name | ...
0
votes
1answer
63 views

Problem with SQL that i want to JOIN _Sent with DE

I have a problem with my SQL where my goal is to create a Data Extension that the records will populate with the number of sent that a subscriber inside in one DE received by the month: The problem ...
0
votes
1answer
46 views

Marketing Cloud ContactID, where do I find this?

how do i access the "contactID" in SQL in the Marketing Cloud ? what data view is it in? when looking at contact builder I see data like this. Contact Contact ID: 1030837 Contact Key: ...
0
votes
1answer
31 views

Data View Counts not Matching FTP upload counts

So I am exporting data views using the automation studio for each of the data view objects (open, click etc.) to FTP then from there our backend guy takes these csv files and inserts them into another ...
0
votes
1answer
61 views

Unique opens and clicks from Child BU for child and parent account for past 1 year not working

I have tried basic SQL which is on help document to get last 30 days opens is not working. here is what i have Select j.JobID, j.EmailName, j.DeliveredTime as SendTime, o.EventDate as OpenTime, s....
0
votes
2answers
653 views

Query Contacts that Exit Journey

Is there a way to query Marketing Cloud to show contacts who have exited a journey? Thanks in advance.
0
votes
3answers
28 views

Does Salesforce indexes work in the same way as a SQL indexes?

I mean SQL indexes generally speed up retrieving and slow down inserting. Does Salesforce indexes work in the same way as a SQL indexes?
0
votes
1answer
42 views

Tips for using SQL skills efficiently in Automation Studio [closed]

As a Marketing Cloud marketeer I really see queries as very powerful in creating strong segments. So, I've dived into SQL and query writing using codeacademy and w3schools, but even though I find ...
0
votes
1answer
93 views

How to select only the characters appearing before a specific symbol in a SQL Select statement

I have strings in a database like this: firstname.lastname@domain.com And I only need the characters that appear before the @ symbol. I am trying to find a simple way to do this in SQL.
0
votes
2answers
267 views

DateDiff not working

I am using the datediff function and it doesn't seem to be working. What I am currently using DATEDIFF(year, creation_date__c, SYSDATETIMEOFFSET()) AS AnniversaryYears It just gives an empty ...
0
votes
1answer
87 views

Discrepancy in tracking data from data views

We are using the following query to get tracking data into a staging data extension. The data we are getting is looking weird - the open and click dates have a timestamp that is earlier than the ...
0
votes
3answers
171 views

Use measure in SQL

I would like to use a measure in a SQL request. Here is my SQL request that works : SELECT C.[Civilite] as 'Civilite', C.[Prenom] as 'Prenom', C.[Nom] as 'Nom', C.[Email] as 'Email', C.[...
0
votes
1answer
978 views

SQL Incorrect syntax near ')'

I'm trying to write a SQL query in a marketing cloud automation studio activity SELECT * FROM [table 1] WHERE [field 1] IN ( 'a2F6F000002WUVmUAO', 'a2F6F000002WTqbUAG', '...
0
votes
2answers
74 views

Date 14 days before current date

I have a subscription program and I want to create a journey and send an email 14 days before the subscription program ends. In order to do that I need to run a query to get only the customers whose ...
0
votes
1answer
49 views

Union Opens, Deliveries, Bounces and Sents from pastmonth

I have one monthly automation by report that the report goes to ftp via CSV and after run an Import File that populates a Data Extension with the totally numbers of sents, opens, deliveries and others ...
0
votes
1answer
81 views

Query to fill the empty column with the next available value from the Data Extension [duplicate]

I have a data extension with a customerID and 15 products assigned to them(screenshot attached) Some of the 15 products are missing the value and I would like to write a query so if Product1 is empty ...
0
votes
1answer
64 views

SQL Querying subscribers older than 6 months

I am trying to query data from an existing data extension into a new data extension and filter out the subscribers that were added within the last six months. The existing data extension does not have ...
0
votes
1answer
48 views

Help to Change the Language for SQL Activity - Getdate

I tried to do a simple sintax, but i have a problem with the language and "getdate" I'm Brazillian and have an automation that update a Data Extension (Primary Key is month) with a FTP integration ...
0
votes
1answer
1k views

how can i delete rows from a data extension based on date?

i can't seem to find a solution that can allow me to delete only those rows from a data extension where "Timestamp" column has a data older than last 15 days from today and I want to do this on daily ...
0
votes
2answers
3k views

How to use SQL ORDER BY statement in marketing cloud (error says requires TOP)

I have a data extension that is created using SQL query on hidden tables that record tracking info related to sent emails. Each table contains JOB ID which is the key to associating the tracking data. ...
0
votes
3answers
2k views

Query Failing: Time Out

I have a query that has started failing due to the fact that it has over 400M rows. Although there are joins and fuzzy matching (LIKE '%XXXX%') it is still failing. Using NO LOCK too I have used ...
0
votes
1answer
1k views

Using CASE to update a field

I'm getting an error on my case statement in SFMC. Is it a simple mistake of syntax? SELECT *, CASE WHEN status <> 'active' THEN 'active' ELSE status END AS status FROM [sample table] WHERE ...
0
votes
1answer
496 views

Query: Can inner join be used together with Group by clause

I have 2 data extensions. DE A has Account IDs and it is the primary key. DE B has Lead ID, but this is not the primary key. DE B has a column called 'Amount'. For those A.[Account ID] = B.[Lead ID] ...
0
votes
2answers
2k views

Querying business unit from Enterprise Account

I have a requirements for reports on business units from the enterprise level. I am trying to generate reports on data from each business unit from the enterprise level. Is there a way to write SQL ...
0
votes
1answer
26 views

“Old style JOIN (ANSI JOINs) syntax” error in a simple query without subqueries and joins

Validating the following query: SELECT GetDate() as Valid_From, 'Lorem Ipsum' as Filler FROM SampleSourceDE shows this error: An error occurred while checking the query syntax. ...
0
votes
2answers
76 views

Identifying records in MC to delete

I'm trying to delete a large number of records from SFMC to avoid being charged extra. I've run several different SQLs to help identify the records I don't need, but when I run the Contact Count ...
0
votes
1answer
40 views

Query to find unique openers in last 30 days

I am trying to find out users who opened an email in the last 30 days from users who received one. Can someone help me verify if this is the right way to do it? I found few query examples and few I'...
0
votes
2answers
48 views

Get subscribers with a changed status during last X days

We would like to send a list of subscribers' status changes both from "Active" to "Unsubscribed" and vice versa to SFTP. We would like to run this activity two or three times an hour to imitate real-...
0
votes
1answer
177 views

Find active subscribers for each month for last 6 months

I am trying to find out the number of active subscribers who opened at least one email in the last 30 days, I would like to find this info for last 6 or 12 months, to monitor the growth of active ...
0
votes
1answer
42 views

Updating records from the base DE in Marketing Cloud

We are doing a big SQL that combines data from 3 synced data extensions (objects from CRM) and 3 local data extensions in Marketing Cloud. The data lands in the target DE, that's OK. However, we need ...
0
votes
1answer
114 views

Delete records from a Data Extension created by Journey on the basis of date

I have a journey in which entry criteria is Salesforce Data. Whenever a contact enters into a journey, the journey puts its details into a DE(created by the journey itself). I want to write automation ...
0
votes
1answer
138 views

Is there a BASE64 Encoding Function using SQL in Automation Studio? [closed]

We'd like to BASE64 Encode our Email Addresses so we can send them to an external third-party (it's the third-party's requirement). We know how to do it using AMPscript, but not in SQL. Ultimately, ...
0
votes
1answer
131 views

Marketing Cloud: Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object

Everyday we have an automation running to import new magento customers from an FTP file to our contact DataBase. Today, for the first time we had this error message. Query failed during execution. ...
0
votes
2answers
65 views

We have a SQL Query looking for _Open and _Click data that is failing because it keeps timing out

The query is intended to populate LastClick and LastOpen data in our Master Subscribers DE. Here is the query: SELECT a.Email, a.LeadUID, MAX(o.EventDate) as LastOpenDate, MAX(c.EventDate) as ...
0
votes
1answer
633 views

Date format in SQL query

We need a specific format date to personalize our emails, for example, the US date format is MM/DD/YYYY HH:MM:SS and we need to display DD/MM/YYYY. Is it possible to change the date format directly ...
0
votes
1answer
189 views

Will Update SQL In Automation Studio Remove Records That Don't Satisfy Requirement?

I am attempting to automate our payment failures to alert consumers their subscription payment hasn't processed. My initial thought was to run a SQL activity once a day and inject new records into a ...
0
votes
2answers
633 views

Error when trying to query All Subscribers

I'm trying to query all subscribers through an SQL query. I've copied a query which I am using in another instance of marketing cloud which works fine. However in the new instance the query activity ...
0
votes
3answers
3k views

SQL query case when like

I have 2 tables: TABLE_1 PRODUCT | CATEGORIES_ID A               | 1,4,115,3 B       &...
0
votes
1answer
45 views

SOQL required for a SQL

Cam someone please convert the below SQL query to SOQL? SQL: Select id, Name, product_code, (select product_code from product b where b.parent_id=a. id) as parent_code from product a How to ...
0
votes
1answer
574 views

Old style JOIN (ANSI JOINs) syntax is not allowed. Split DATE

I'm using a simple SQL query to get DAY, MONTH, YEAR out of a Date field (BIRTH_DATE) in Automation Studio (of marketing cloud). SELECT CUST_CODE , EXCLUDED_FROM_NPS , BIRTH_DATE , DATEPART(DD,...
0
votes
1answer
2k views

Need Help on SQL QUERY in SFMC

I am running the following query select * from Policy where policynumber in ( select policynumber from [Email] where newsletteroptin = 'Y' and Source NOT IN ('CWS') ) I also have tried removing ...

1
3 4
5
6 7
12