Date data types in SFMC are DateTime (or DateTime2) data types behind the scenes, so the time is always included. It's best to use AMPscript to format these, as you can't dictate the format stored in the data extension unless you store them as Text.
I generally just use the formatDate() function in AMPscript:
set @date_field = ...
Is there any particular reason you need the Device ID and System Token in the Data Extension? When sending to a DE in MobilePush, you only need the Contact Key in that Data Extension. The System Token and Device ID fields live on the MobilePush Demographics table, as long as you send to a Contact Key, Mobile Push will know where to fetch those values from.
depending on how I interpret your question (and the data extensions look like), I see two scenarios: 1) you use an INNER JOIN or 2) you first break up the first DE and then use a UNION.
However if you struggle with these kind of JOINS you may want to look a a drag-and-drop solution like DESelect. We have been using it to avoid having to write SQL and it ...
This could work, I've assumed the organization__c field is what you want to appear once and would like to pick the newest lastmodified date for that particular record. The data is split on organization__c and ordered by last modified date and then the newest/most recent row of each organization__c is retrieved.
You can retrieve data from the _PushAddress DE (using SQL or Ampscript of course). It contains all of the mobilePush attributes, including the deviceId.
for more information regarding the attributes themselves, check this topic:
Attributes in _PushAddress and _PushTag Data Views
Hope I could help.
try like this query
Name, Email, DateTime FROM (
SELECT Name, Email, DateTime,
ROW_NUMBER() OVER(PARTITION BY Email ORDER BY DateTime DESC) AS RowNumber FROM
WHERE t1.RowNumber = 1
The naming of your fields suggests that PKey should be your primary key, which cannot be empty in the first place, if you set up the data extension correctly.
So therefore you need to either
add this functionality to the query that initially fills your data extension, or
set it as a default value in the data extension setup.
For the SQL solution you ...
So assuming that you have your email address as your primary key this is what I would do:
WHERE OrderHeader_EmailAddress IN
You mentioned that you wanted to measure engagement, that's why I used the open instead of click data view. You don't need the where ...
I don't think it's doable with union using one query. I would create a staging Data Extension that would store records from your union, and then ROW_NUMBER in nested SELECT to get only newest record for each email. You should use ROW_NUMBER function to arrange record according to your needs and select just first record in list with RowNumber = 1
What you need to do is use max() and group by.
FieldA, FieldB, max(FieldC)
GROUP BY FieldA
UNION ALL will include duplicates across both your DEs, so if both have the same record in them, you'd have to control for that too.
Depending on what other fields you need apart from Job ID and Journey ID, you can play around with the following:
FROM _Sent s INNER JOIN _JourneyActivity j
ON j.JourneyActivityObjectID = s.TriggererSendDefinitionObjectID
If you just like to have the subscribers that haven't opened (or received) an email in the last 90 days you can use a simple SQL utilizing the "Subscribers" and "Open" data views.
However, if you just want to select the subscribers that did receive an email, but didn't open it, you need to also use the "Sent" data view.
An example that should lead you in ...
The issue is that you are comparing apples to oranges in your query.
When using DW as the formatting in DatePart, you are returned a number between 1 and 7. 1 is Sunday and 7 is Saturday.
So in order to find the day, you will need to do the comparison on the number and then translate the value saved from there.
select o.SubscriberKey, o....
You could try the following, given the Contact_Key in Organizations DE is equal to SubscriberKey (if it's not, how do they relate?):
s.EmailAddress AS Email_Address,
j.Event_ID AS EventID,
LEFT JOIN _bounce ...
You would essentially need to join the staging DE an the Target DE to have access to values from both data extensions and then use the SQL Case statement to compare the values.
WHEN s.Field is null THEN t.Field
END as Field
s.PrimaryKey = t....
Ashish, I'm having a hard time understanding what you actually try to achieve.
You say you want to achieve the same as a union statement, but the image you attached does not show that. In fact, I don't see the logic of how DE4 should be filled from that image.
I'm going to assume you made a mistake in your image of DE4 and want to get the rows of the 3 ...
As your target data extension has to be the one containing all of the fields in Data Extensions 1-4 (that you like to write to it), you can add the additional fields to your select-statements for data extensions 1-4 using a null value and an alias.
Prerequisite: The fields missing in your source data extensions need to be nullable in the target data ...
SELECT DeOneColumnName1 as DeFiveColumn1, DeOneColumnName2 as DeFiveColumn2, ..., email
SELECT DeTwoColumnName1 as DeFiveColumn2, DeTwoColumnName2 as DeFiveColumn1, ..., email
Assumption the target dataExtension has only X fields which are named "DeFiveColumn1", "DeFiveColumn2" , ... and email
You can use the as parameter ...
I would use JOINs to connect these.
You would want to decide which values you want (see above chart for a great visualization of SQL Join results) to keep, and that will determine your type of join.
For instance, if you want to return all results from matching values in the tables, you would use:
SELECT a.FieldA, a.FieldB, b.FieldC, c.FieldD
FROM [DE1] a
You can try using a full outer union:
SELECT fieldA, fieldB
(SELECT fieldA, fieldB FROM De1) 1
FULL OUTER JOIN
(SELECT fieldA, fieldB FROM De2) 2 on 1.fieldA=2.fieldA
FULL OUTER JOIN
(SELECT fieldA, fieldB FROM De3) 3 on 1.fieldA=3.fieldA
the target should be De4 and assuming your primary ID is fildA
You can achieve this by using UNIONS. Unions are a way to combine data of two or more dataextensions and insert the result into the dataextension (or table) of your choice.
Here are some nice posts regarding unions:
Depending on your use case you need to switch between union and union all. Union will perform in a way that you will only get distinct ...
This is possible with an Automation containing SQL query activities. You need to select the fields you like to write/update to/in your MasterDe and make sure you have a matching primary key, so the fields can be updated correctly.
Things you need to consider:
You need to have the field used as primary key in MasterDe present in your other data extensions.
I don't think you need the ROW_NUMBER function, the issue's with your grouping as p.EventDate is a time stamp (down to the millisecond). You'll get one row returned in your query for the most recent send to each Subscriber, effectively.
COUNT(1) AS TotalSent,
DATENAME(month, p.EventDate) as [MONTH],
'XX' as [Business_Unit],