Solved

Big Monthly Report

Posted on 2006-07-05
5
219 Views
Last Modified: 2010-04-16
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?
0
Comment
Question by:Shiny
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 22

Expert Comment

by:RedKelvin
ID: 17048005
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
0
 
LVL 1

Author Comment

by:Shiny
ID: 17048108
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.
0
 
LVL 22

Expert Comment

by:RedKelvin
ID: 17048215
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
0
 
LVL 1

Author Comment

by:Shiny
ID: 17050442
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.

0
 
LVL 22

Accepted Solution

by:
RedKelvin earned 500 total points
ID: 17054686
Are you stuck on how you want to lay it out (as in how it will look)?

Or are you having trouble formatting, taking the data retrieved from the query and putting it into the datagrid through code?
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question