SQL 2005 - Calculation field with confusing total

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

JohnMac328Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ralmadaCommented:
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
JohnMac328Author Commented:
Here is a screen shot of a result.
Example.jpg
0
ralmadaCommented:
And what is the expected result?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

JohnMac328Author 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.
0
ralmadaCommented:
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
JohnMac328Author 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.  
0
rgc6789Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnMac328Author Commented:
At least I got the right credit to the right person this time.

Thanks again
0
rgc6789Commented:
Glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.