What I meant was.. it needs to tally it for a particular date range, not necessarily "once per day".
The reason I can't pull data every 15 minutes, or hour or whatever is because these are dynamic reports.
Based on different criteria the user selects (columns to display, data to filter on, etc), I need to rebuild the stored procedures. There's a base query, for example, the Agent Meeting/Transaction report above, but the user can also choose to filter that data based on a certain product or other various information.
When the user selects one or more filters to add to the type of report, I take all those filters and apply them to the base query. If a product filter is added, I append a join to the products table and the appropriate where clause, and repeat this for each additional filter.
The problem lies in the Transaction Types. These types are user defined, and the user can pick & choose which types to show.
Say the base report query looks like this:
Code:
SELECT
A.Name,
COUNT(DISTINCT dbo.Meetings.MeetingID) AS Meetings
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
...then they decide "oh, I wanna add type 3 and type 4 columns to display."
I need to take that query and change it (rebuild the stored proc.) so it looks like:
Code:
SELECT
A.Name,
COUNT(DISTINCT dbo.Meetings.MeetingID) AS Meetings,
Type3 = (SELECT COUNT(*) FROM Transactions T WHERE AgentID = A.AgentID AND T.TransTypeID = 4),
Sum3 = (SELECT ISNULL(SUM(Amount), 0) FROM Transactions T WHERE AgentID = A.AgentID AND T.TransTypeID = 3),
Type4 = (SELECT COUNT(*) FROM Transactions T WHERE AgentID = A.AgentID AND T.TransTypeID = 4),
Sum4 = (SELECT ISNULL(SUM(Amount), 0) FROM Transactions T WHERE AgentID = A.AgentID AND T.TransTypeID = 4)
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
So far, I haven't run into any issues because the data I'm working with is relatively small. I was thinking about filling it with a few million dummy records and testing it out, though.
Overall, I'm just wondering if my method of achieving the following results using the query shown is as good as I can get, or in other words, is there a better more efficient way that I should be doing this?
And given the additional info I provided (dynamic reports, infinite types/columns that can be selected by the user, rebuilding stored procs), is there another way I should be looking at this?
Code:
These results:
Name Meetings Type1 Sum1 Type2 Sum2 Type3 Sum3
-------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
Bill 1 0 0 0 0 1 50
Bob 3 2 15 1 10 1 15
Using this Query:
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