Solved

Big Monthly Report

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

ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

Question has a verified solution.

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

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

770 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