Is there a way to populate fields in 1 data extension with data from multiple Data extensions?

Question

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?

Answer 1

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.