[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Big Monthly Report

Posted on 2006-07-05
5
Medium Priority
?
222 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

649 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