I currently have a database set up to keep track of all montly expenses spent by an employee. Each transaction is kept in its own record in a Table 'ExpenseTransaction.' I have stored procedure that can look up a single person and find all of their corresponding transactions for the month.
What I am looking to do is generate a montly report of people who went over their budget on a certain day (not for the month). What would be the best way to do this?
I was thinking about running this stored procedure mentioned above for each employee and keeping it somehow in a DataGrid or I was thinking of making a stored procedure with a tempory table with a column for the employeeID and colums for each day of the month. Except I am thinking at about 100 employees, running a stored procedure 100 times with 100s of these expense reports could take quite some time.
What would be the most efficent way to tackle this?