really hope someone can help me with this as I've hit a wall trying to figure this out myself and would make life a lot easier at work. Unfortunately can't share the data as it's confidential, so I've prepared a mock scenario below to help explain the issue.
Data Example - https://docs.google.com/spreadsheets/d/1o3vUDEyK3OAHorlQIL_FuOn_b92pyv7uV8_YHfUHcII/edit?usp=sharing
Although this is easily done in a spreadsheet as shown in my example above, due to work I have to complete this task through SQL, but I also have a 10,000 row limit to contend with (global limit, not changeable).
The data in table 2 is exceeding this limit, and a workaround for this would be to simply replace the date column in table 2 with the merged group column in table 1. Then calculating the total number of interactions between the start and end dates specified in table 1 grouping by agent name and group, eventually ending up with the hopeful combined output example in columns L:N
Therefore rather than outputting a list for every agent for each day, we could instead show the number of interactions for each month for their month period, which will drastically reduce the number of rows.
Be grateful for any response to this, thanks in advance! Tom
If the "Agents" can be part of max 1 "Group", then this should work:
select
`Table 1`.`Agent Name` as `Agent`,
`Table 1`.`Merged` as `Group`,
sum(`Table 2`.`Number of Interactions`) as `Number of Interactions`
from
`Table 2`
inner join `Table 1`
on (`Table 1`.`Agent Name` = `Table 2`.`Agent Name`
and `Table 1`.`Start_date` <= `Table 2`.`Date`
and `Table 1`.`End_date` >= `Table 2`.`Date`)
group by
`Table 1`.`Agent Name`,
`Table 1`.`Merged`