Solved

Big Monthly Report

Posted on 2006-07-05
5
218 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
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
itextsharp with c# 3 18
Header Font Size in Grid View 6 27
Return array 3 20
How to set focus on a dynamic control 18 26
Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

733 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