Link to home
Start Free TrialLog in
Avatar of Shiny
Shiny

asked on

Big Monthly Report

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?
Avatar of RedKelvin
RedKelvin

Firstly, remember you can use the sum keyword in sql, to sum the total of the days expenses per employee
SELECT SUM(column) FROM table

And if you are manipulating the data, and you don't need to initially display it, you can store the data in a DataTable, then show the dataTable through a DataGrid when you are done
Avatar of Shiny

ASKER

Yea, I am using SUM() to sum up each of the individual expenses for a certain day.  The key I am looking for is how to see each of the days seperately and efficently.
You can do it all with one SQL query for example, presume you have a table with 3 columns, Name, Amount and Date, you could execute the following sql

SELECT Table1.Name, Sum(Table1.Amount) AS SumOfAmount, Table1.Date
FROM Table1
GROUP BY Table1.Name, Table1.Date;

This would give you all emloyees and dates, grouped by employee name and date, the amount will be summed for each employee on each day.

Or you could specify the date and amount to return a set of records where the amount is greater than a certain amount, for a given day

SELECT Table1.Name, Sum(Table1.Amount) AS SumOfAmount, Table1.Date
FROM Table1
GROUP BY Table1.Name, Table1.Date
HAVING (((Sum(Table1.Amount))>4) AND ((Table1.Date)=#1/1/2000#));

That should do the trick, doesn't get any more efficient than one query

Enjoy
RedK
Avatar of Shiny

ASKER

I guess what I am really stuck on is taking that table with Name/Date/Hours, and formatting it into a calandar type display

Name  Expense01/06  Expense02/06  Expense03/06 etc. etc.

ASKER CERTIFIED SOLUTION
Avatar of RedKelvin
RedKelvin

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial