0

For example, we have a DE for each of our subscribers lists where a new subscribers are being added every day and we would like to have a single data extension to contain one field for "list" and other fields for "1dayago","2daysago", "3daysago". is it possible to query the subscribers based based on time from the "List" DE's and place them into the fields in the new DE?

  • I am having trouble visualizing the data you are looking to include in the 2nd data extension with the List and Xdaysago fields, could you provide an example? – Mac Jul 15 '16 at 16:24
  • Hi Mac here are some screenshots that might help you help me :), this is the source DE i.imgur.com/ZcVoeVR.png . this is the Destination DE i.imgur.com/Y9FGQ6m.png . And this is what i would like to accomplish i.imgur.com/xbQbPDZ.png . I am having a hard time with the query development. – Angel Jul 18 '16 at 19:07
0

It would be possible using sub-selects to do the counts, it won't be pretty and depending on the size of the data extensions it may not perform very well. Also, if you have many data extensions, the syntax could become really really messy.

Something like:

select 'tenways' as ListName, 
(select count(*) from TenWays_0 where datediff(day, registration_date, getdate()) = 1 ) as 1dayback, 
(select count(*) from TenWays_0 where datediff(day, registration_date, getdate()) = 2 ) as 2daysback, 
(select count(*) from TenWays_0 where datediff(day, registration_date, getdate()) = 3 ) as 3daysback 
union 
select 'list 2' as ListName, 
(select count(*) from List_2 where datediff(day, registration_date, getdate()) = 1 ) as 1dayback, 
(select count(*) from List_2 where datediff(day, registration_date, getdate()) = 2 ) as 2daysback, 
(select count(*) from List_2 where datediff(day, registration_date, getdate()) = 3 ) as 3daysback 

It might be easier if a single data extension was used as the master list for all of your subscriber records then there was a single separate data extension for list membership with fields for ListName, OptInDate, and Email Address.

  • Thank you so much MAC! i actually had a similar query running in another business unit but youre definitely cleared some stuff up. Thanks again! – Angel Jul 20 '16 at 22:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.