Solved

SQL 2005 - Calculation field with confusing total

Posted on 2009-06-29
9
160 Views
Last Modified: 2012-05-07
I have a query that works but I need a column that tells what amount the employee will get when they complete the next group of tests.  If an employee accumulates  a sum of 5 credits, they are paid $50.  When they complete the next 5 they are paid $100, the report should show them that the next amount they are to be paid is $50.  If they had taken 15 tests, then it would show that they are to be paid $100 when they do complete the next 5 tests.  The pattern is 50-100-50-100.  The current query  shows  what they have accumulated in credits and calculated how much they have already been paid.

Thanks,
John
SELECT     Employees.First_Name, Employees.Last_Name, SUM(Test.Credits_Earned) AS Credits, ROUND(SUM(Test.Credits_Earned) / 5, 0, 1) 
                      * 50 + ROUND(SUM(Test.Credits_Earned) / 10, 0, 1) * 50 AS AmountPaid
FROM         Employees INNER JOIN
                      Test ON Employees.EmployeeID = Test.EmployeeID
GROUP BY Employees.First_Name, Employees.Last_Name, Employees.EmployeeID
HAVING      (Employees.EmployeeID IS NOT NULL) AND (SUM(Test.Credits_Earned) > 0)
ORDER BY Employees.First_Name, Employees.Last_Name

Open in new window

0
Comment
Question by:JohnMac328
[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
  • 4
  • 3
  • 2
9 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 24737084
Have a look. Is this what you're looking for?
If not can you post some sample data and expected result?

select 	a.*, 
	case 	when a.Credits = 5 then 50
		when a.Credits = 10 then 100
		when a.Credits = 15 then 100
		else 0 end as tobepaid
 
from (
 
SELECT  Employees.First_Name, 
	Employees.Last_Name, 
	SUM(Test.Credits_Earned) AS Credits, 
	ROUND(SUM(Test.Credits_Earned) / 5, 0, 1) * 50 + ROUND(SUM(Test.Credits_Earned) / 10, 0, 1) * 50 AS AmountPaid
FROM         Employees 
 
INNER JOIN Test ON Employees.EmployeeID = Test.EmployeeID
 
GROUP BY Employees.First_Name, 
	Employees.Last_Name, 
	Employees.EmployeeID
HAVING      (Employees.EmployeeID IS NOT NULL) AND (SUM(Test.Credits_Earned) > 0)
) a
ORDER BY a.First_Name, a.Last_Name

Open in new window

0
 

Author Comment

by:JohnMac328
ID: 24737201
Here is a screen shot of a result.
Example.jpg
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24737272
And what is the expected result?
0
Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

 

Author Comment

by:JohnMac328
ID: 24737305
That query is correct, now all that is needed is a column that shows what the next amount to be paid will be, either $50 or $100.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24737372
I'm still not following you with the pattern. For the sample data provided, can you please tell me what would be the result?
so for the first case with 128 Credits and AmountPaid 1850. What is the next amount to be paid? and why?
Can you do the same with the other cases?
0
 

Author Comment

by:JohnMac328
ID: 24737421
Look at the one that has an accumulation of 7 credits.  They took 5 tests (generally 1 credit for each test), and were paid $50.  The column should show that they will be paid $100 upon completion of the next 5 tests.  
0
 
LVL 5

Accepted Solution

by:
rgc6789 earned 500 total points
ID: 24738236
Add this to your select:
,Case When {fn mod(SUM(Test.Credits_Earned),10)} Between 0 And 4 Then 50 Else 100 End As NextAmtPaid

Open in new window

0
 

Author Closing Comment

by:JohnMac328
ID: 31598039
At least I got the right credit to the right person this time.

Thanks again
0
 
LVL 5

Expert Comment

by:rgc6789
ID: 24738332
Glad to help.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 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