My goal is create a Data Extension in the Parent BU with a column for my 5 BUs where the record will populate by the total number of sent last 30 days from each BU i have.
I utilize only a sender profile if it's help anyone to know how i can do this.
I thinks i can SQL, but how can i segment for each BU?
EDIT: I tried to used this sintax below, but the same number goes to all fields. How i can solved this?
My thought the arquitecture it's like the image attached
SELECT COUNT(CASE WHEN AccountID ='100000001' THEN 1 ELSE 0 END) as [PARENT_BU], COUNT(CASE WHEN AccountID = '100000002' THEN 1 ELSE 0 END) AS [BU1], COUNT(CASE WHEN AccountID = '100000003' THEN 1 ELSE 0 END) AS [BU2], COUNT(CASE WHEN AccountID = '100000004' THEN 1 ELSE 0 END) AS [BU3], DATENAME(month, PickupTime) AS [MONTH] FROM _Job WHERE PickupTime BETWEEN GETDATE()-38 AND GETDATE() GROUP BY DATENAME(month, PickupTime)