We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL 2005 - Calculation field with confusing total

Medium Priority
183 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

Comment
Watch Question

Commented:
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

Author

Commented:
Here is a screen shot of a result.
Example.jpg

Commented:
And what is the expected result?

Author

Commented:
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.

Commented:
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?

Author

Commented:
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.  
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
At least I got the right credit to the right person this time.

Thanks again

Commented:
Glad to help.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.