I'll try to keep this simple and easy to follow
Let's say I have a system where I run reports on Sales Agents. I return the data once a day and tally up the amount of meetings they attended along with a transaction (sales) summary.
The data is to be presented on ONE line.
To show this example, I have 3 tables: Agents, Meetings, and Transactions.
Agents contains:
Code:
AgentID Name
----------- --------------------------------------------------
1 Bob
2 Bill
Meetings contains:
Code:
MeetingID AgentID MeetingDate
----------- ----------- ------------------------------------------------------
1 1 2004-04-15 11:00:00.000
2 1 2004-04-15 12:00:00.000
3 1 2004-04-15 13:00:00.000
4 2 2004-04-15 09:00:00.000
Transactions contains:
Code:
TransID AgentID TransTypeID Amount
----------- ----------- ----------- -----------
1 1 1 5
2 1 2 10
3 1 3 15
4 1 1 10
5 2 3 50
I need the results returned ONE agent per line, total meetings, and each transaction type (with amount sum) displayed as shown in the following:
Code:
Name Meetings Type1 Sum1 Type2 Sum2 Type3 Sum3
-------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
Bill 1 0 0 0 0 1 50
Bob 3 2 15 1 10 1 15
The query I'm using for this is:
Code:
SELECT
A.Name,
COUNT(DISTINCT dbo.Meetings.MeetingID) AS Meetings,
Type1 = (SELECT COUNT(*) FROM Transactions T WHERE AgentID = A.AgentID AND T.TransTypeID = 1),
Sum1 = (SELECT ISNULL(SUM(Amount), 0) FROM Transactions T WHERE AgentID = A.AgentID AND T.TransTypeID = 1),
Type2 = (SELECT COUNT(*) FROM Transactions T WHERE AgentID = A.AgentID AND T.TransTypeID = 2),
Sum2 = (SELECT ISNULL(SUM(Amount), 0) FROM Transactions T WHERE AgentID = A.AgentID AND T.TransTypeID = 2),
Type3 = (SELECT COUNT(*) FROM Transactions T WHERE AgentID = A.AgentID AND T.TransTypeID = 3),
Sum3 = (SELECT ISNULL(SUM(Amount), 0) FROM Transactions T WHERE AgentID = A.AgentID AND T.TransTypeID = 3)
FROM
dbo.Agents A LEFT OUTER JOIN dbo.Meetings ON A.AgentID = dbo.Meetings.AgentID
GROUP BY
A.Name,
A.AgentID
ORDER BY
A.Name
The question I have is... is there a more efficient way to get the results I want? It seems highly inefficient to be querying against the same table six times for every record returned.
In the real world situation I'm working with, there are 6 different types with corresponding amounts that need to be displayed, so that'd be 12 queries per row. There could be tens of thousands of agents with hundreds of thousands of transaction records, so doing something like this would seem overkill on the server.
I COULD query it like so:
Code:
SELECT
dbo.Agents.Name,
COUNT(DISTINCT dbo.Meetings.MeetingID) AS Meetings,
dbo.Transactions.TransTypeID,
SUM(DISTINCT dbo.Transactions.Amount) AS TransAmount
FROM
dbo.Agents LEFT OUTER JOIN dbo.Transactions ON dbo.Agents.AgentID = dbo.Transactions.AgentID
LEFT OUTER JOIN dbo.Meetings ON dbo.Agents.AgentID = dbo.Meetings.AgentID
GROUP BY
dbo.Agents.Name,
dbo.Transactions.TransTypeID
ORDER BY
dbo.Agents.Name
which will return the following results:
Code:
Name Meetings TransTypeID TransAmount
-------------------------------------------------- ----------- ----------- -----------
Bill 1 3 50
Bob 3 1 15
Bob 3 2 10
Bob 3 3 15
...which would require me to loop through EACH record, check the TransType, then put it in it's proper position in the table in HTML, but this is done in asp.net and it's a headache to add items "manually" like that.
[edit]
Not to mention it would throw off paging. If the above dataset was limited to 2 records per page, instead of showing one for Bill and one for Bob, you'd end up with one for Bill, and 1/3 of the data for Bob.
However, if that's my only option, then I guess I have no choice
Just wanted other opinions on what I should do, or the most recommended route to tackle this. Thanks
