I am running a few similar queries to find the number of emails Sent/Opened/Clicked in the last 30 days. The query for Sent works fine, but the other two aren't. Not sure what the problem is. I do not get syntax errors, and it will start successfully - however, no data is populated into the data extension (whereas the data for _Sent DOES populate the data extension) -- I am also running a secondary query to check these counts, and all three of those work as expected.
Here is an example of a query that does not populate the data extension as it should - any ideas?:
SELECT 'Clicked in Last 30 Days' as dimension_name , count(*) AS dimension_count , j.EmailName as email_address FROM _Click p JOIN _Job j on j.JobID = p.JobID WHERE (p.EventDate BETWEEN DATEADD(day, -30, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP) AND (j.SendClassification = 'Default Commercial') GROUP BY j.EmailName
The solution ended up being that the text field I was trying to populate was limiting to 50 characters. Updating the text field length to something like 255 works fine now.