Retrieve Last 10 Records with where Clause

Question

Hi,

Hi,
Please look into Table & I have a Query...
I need a sum of Income of Last(Latest) 10 Records of every Employee of only Those Month where Leave Status=0

Thanks,

Answer 1

Use ROW_NUMBER() OVER PARTITION BY.

http://www.sqlusa.com/bestpractices2005/overpartitionby/

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 2

Try the below:(10 is made as 5 for easiness.)

Drop table t1
create Table t1(EID int,Income int,Date1 Date,Leave int)
Insert into t1 
Select 123,10,GETDATE()-1,1
Union All
Select 123,10,GETDATE()-2,0
Union All
Select 123,10,GETDATE()-3,0
Union All
Select 123,10,GETDATE()-4,1
Union All
Select 123,10,GETDATE()-5,1
Union All
Select 123,10,GETDATE()-6,1
Union All
Select 123,10,GETDATE()-7,1
Union All
Select 123,10,GETDATE()-7,1
Union All
Select 124,10,GETDATE(),1
Union All
Select 124,10,GETDATE()-1,0
Union All
Select 124,10,GETDATE()-2,1
Union All
Select 125,10,GETDATE()-1,1
Union All
Select 125,10,GETDATE()-2,1
Union All
Select 125,10,GETDATE()-3,1
Select A.EID,SUM(Income) From
(
Select *,ROW_NUMBER() Over(PARTITION by EID Order by Date1 desc) Rn From t1 Where Leave = 1
) A Where Rn<6
Group by A.EID

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 3

Try

with set1 as
(select substring(convert(varchar,salmonth,112),1,6) mth
from hra
group by substring(convert(varchar,salmonth,112),1,6)
having max(leave)=0)
select top 10 *
from hra
where substring(convert(varchar,salmonth,112),1,6) in(select mth from set1)
order by salmonth desc;

---

Many Thanks & Best Regards, Hua Min

Answer 4

I'm not sure exactly what you are looking for.  But perhaps

;With cteEmp As
(Select Distinct EmpID, EmpNm
From HRA),
cteIncome As
(Select EmpID, EmpNm, Income, Leave
  Row_Number() Over(Partition By EmpID Order By SalMonth Desc) As rn
From HRA)
Select e.EmpID, e.EmpNm, Sum(i.Income) As SumOfIncome
From cteEmp e
Left Outer Join cteIncome i On e.EmpID = i.EmpID
  And i.rn <= 10 And i.Leave = 0
Group By e.EmpID, e.EmpNm;

Tom