August 14, 2014 at 5:42 am
Let us assume that there are 100 employee in a company. And sum of salary of all employee is 10000. Find list of highest paid employees whose sum of salary is 8000. Remaining employee will fall in 20% bracket. I hope question is clear. Please let me know if anything else is required.
August 14, 2014 at 5:56 am
For such questions, it would be nice if you provided table DDL, sample data and desired output.
See the first link in my signature on how to do this.
CREATE TABLE [dbo].[Salaries](
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [int] NOT NULL,
[Salary] [numeric](18, 2) NOT NULL
);
GO
INSERT INTO dbo.Salaries([EmployeeID],[Salary])
VALUES (1,1000)
,(2,100)
,(3,250)
,(4,850)
,(5,1150)
,(6,600)
,(7,950)
,(8,300)
,(9,1500)
,(10,300)
,(11,250)
,(12,900)
,(13,850)
,(14,200)
,(15,800);
DECLARE @TotalSum NUMERIC(18,2);
SELECT @TotalSum = SUM(Salary)
FROM [dbo].[Salaries];
SELECT [EmployeeID], Salary, PercentageOfTotal
FROM
(
SELECT [EmployeeID], Salary, PercentageOfTotal = IIF(@TotalSum = 0,0,SUM(Salary) OVER (ORDER BY Salary DESC) / @TotalSum)
FROM [dbo].[Salaries]
) tmp
WHERE PercentageOfTotal <= 0.8;
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 14, 2014 at 6:18 am
Can you please give solution in SQL 2008 as well as IIF does not work in SQL 2008.
August 14, 2014 at 6:26 am
akash_singh (8/14/2014)
Can you please give solution in SQL 2008 as well as IIF does not work in SQL 2008.
IIF is shorthand for a CASE statement. I'm sure you can figure out how to replace it.
(by the way, this is a SQL Server 2012 forum)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 14, 2014 at 6:22 pm
akash_singh (8/14/2014)
Let us assume that there are 100 employee in a company. And sum of salary of all employee is 10000. Find list of highest paid employees whose sum of salary is 8000. Remaining employee will fall in 20% bracket. I hope question is clear. Please let me know if anything else is required.
How many employees and how often do you need to do this? I ask because there aren't many good (horrible, horrible performance or some nasty trick code to get performance) options in 2008 although there are some easy ones. We need to know so we can figure out the "best" option for you.
I share the sentiment. It would be nice if you had provided some test data and posted to the correct forum. 😉 See the first link in my signature line under "Useful Links" to learn how to create readily consumable test data in a hurry.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply