vakman


Say I have a table that keeps track of the employees my business hires, including the date they are hired and the salaries they are paid. What I'm trying to do is write a query that will tell me which employee it was I hired that took the total amount of money I pay in salaries over a certain threshold.

E.g. my threshold is $100,000 for salaries, I hire Employee A and pay him/her $50,000; a week or two later I hire employee B on a salary of $70,000. So in this case it is Employee B that took me past my threshold.

Say our employee table looks like this:

CREATE TABLE Employees( employeeName varchar(100), datehired datetime, salary money)

Any idea how I would write a query to do this. NOTE that I'm not interested writing a loop or using a cursor to look at each row to determine this.




Re: Finding the nth row that breaks a threshold

MohammedU


You can use of the following script...to suit your requirement...

CREATE TABLE RunningTotals

(

Userid INT,

TotalCount INT

)

INSERT RunningTotals(Userid,TotalCount) VALUES(2,3)

INSERT RunningTotals(Userid,TotalCount) VALUES(3,5)

INSERT RunningTotals(Userid,TotalCount) VALUES(36,2)

INSERT RunningTotals(Userid,TotalCount) VALUES(43,4)

INSERT RunningTotals(Userid,TotalCount) VALUES(45,9)

INSERT RunningTotals(Userid,TotalCount) VALUES(46,2)

--Solution

SELECT

a.userid,

a.TotalCount,

(select sum(totalCount) from RunningTotals b where b.userid

<=

a.userid) as RunningTotal

from

RunningTotals a

where (select sum(totalCount) from RunningTotals b where b.userid

<=

a.userid) < 23







Re: Finding the nth row that breaks a threshold

ManiD

The following may be helpfull for you..

Create table #Employees(empid int identity(1,1), employeeName varchar(100), datehired datetime, salary money)

Insert Into #Employees Select * from Employees

Declare @threshold as money
Select @threshold = 100000

Select * From
(
Select
M.EmpId,
M.datehired,
M.salary,
Sum(S.salary) ToalSalary
From
#Employees M
Join #Employees S on S.EmpId <= M.EmpId
Group By
M.EmpId,
M.datehired,
M.salary
) as Data
Where
ToalSalary > @threshold