Solved

SQL 2005 - Calculation field with confusing total

Posted on 2009-06-29
9
153 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
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
Comment Utility
Here is a screen shot of a result.
Example.jpg
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
And what is the expected result?
0
 

Author Comment

by:JohnMac328
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 41

Expert Comment

by:ralmada
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
At least I got the right credit to the right person this time.

Thanks again
0
 
LVL 5

Expert Comment

by:rgc6789
Comment Utility
Glad to help.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
In this article I will describe the Copy Database Wizard 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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now