Do the databases have exactly the same tables and records?

Oh, now I get it!  (One day, I swear I'm gonna learn how to read!)
Basically, you need two things to do this - a ForEach File Loop, and an...

"automated unmanned job" - the "processing database" would have an autoexec macro in it that kicks off the processing of the databases in some folder (uses DIR() to loop over...

"I can't use CHOOSE"? What kind of requirement is that?
You have to do it in SSRS, because you said you can't modify the underlying query. How do they suggest...

Are you allowed to create calculated fields in your report definition?
=CHOOSE(Fields!GradeLevel.Value,"Pre K","K","1st","2nd","3rd","4th","5th","6th","7th", "8th", "9th", "10th", "11th", "12th")
(You could alter it to include different messages for each...

The reason I asked about Access is that I don't know how to dynamically select which columns are getting imported/mapped to which destination columns in SQL Server. I have done...

You can do this in Access too... Open a front end (no data, just linked tables, and some code). Then loop through the table(s) and deal with them. Append to...

Thanks for posting the INSERT scripts.  But without a corresponding CREATE TABLE script, the data has nowhere to go.

That's a huge question... Are you looking for a book on SQL Server Administration or T-SQL?

I tried to reproduce your problem by using DECLARE inside the query window in PowerBI (after you specify the database etc), and mine worked. Granted, my SQL was a lot...

Yes, you're right. I would absolutely do this as a stored procedure. If you post the CREATE TABLE and INSERT scripts for your Agents and ActiveCalls tables (maybe 2 records...

Joe,
Here's my really small test setup:
USE TempDB;
GO

CREATE TABLE Agent(
    AgentID INT IDENTITY,
    FirstName VARCHAR(15) NOT NULL,
    LastName VARCHAR(20) NOT NULL
CONSTRAINT Agent_PK PRIMARY...

You can capture the ID of the person with the fewest calls, and then use it in another query:
DECLARE @AgentID INT;

SELECT TOP 1
    @NextAgentID =...