CODEWITHSUNDEEP
sqlsql-server
New2Programming
New2Programming

Reputation: 383

Select 10 percent for each group for the previous month

I can get the top 10 percent for the previous month using

select top 10 percent ID, Ref, Entered_Date, [Type], CREATEDBY, Office, Created_Date, Amt from Tbl 
where DATEPART(m, Entered_Date) = DATEPART(m, DATEADD(m, -1, getdate())) AND DATEPART(yyyy, Entered_Date) = DATEPART(yyyy, DATEADD(m, -1, getdate())) and 
CreatedBy ='User1')
order by amt DESC

I am having to do this many times for each user using a union, how can I do this on one query? When I add the other users CreatedBy in('User1','User2') it doesn't work. I had a look at row over partition but cant figure it out. I'm using SSMS 2017.

Updated with below 

Select * From(
select ID, Ref, Entered_Date, [Type], CREATEDBY, Office, Created_Date, Amt 
NTILE (10) OVER ( PARTITION BY CREATEDBY ORDER BY Amt desc)  AS PercentageNo
from Tbl 
where DATEPART(m, Entered_Date) = DATEPART(m, DATEADD(m, -1, getdate())) AND DATEPART(yyyy, Entered_Date) = DATEPART(yyyy, DATEADD(m, -1, getdate())) 
/*Entered_Date between DATEADD(m, -2, getdate())  and DATEADD(m, -1, getdate()) */ )as SubQuery
where PercentageNo=1 order By Amt

Upvotes: 0

Views: 282

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

If you want the top 10% for each user, I would recommend using logic such as this:

select ID, Ref, Entered_Date, [Type], CREATEDBY, Office, Created_Date, Amt
from (select t.*,
             row_number() over (partition by user order by amt desc) as seqnum,
             count(*) over (partition by user) as cnt
      from Tbl t
      where Entered_Date >= dateadd(month, -1, dateadd(day, 1 - day(getdate()), convert(date, getdate()))) and
            Entered_Date < dateadd(day, 1 - day(getdate()), convert(date, getdate()))
      ) t
where seqnum <= cnt / 10;
order by user, amt desc;

Note the change to the date arithmetic. The functions are all applied only on getdate(). This allows indexes to be used to winnow down the data.

The key for solving your problem is using window functions with the partition by user construct to do separate counts and enumerations for each user.

Upvotes: 0

New2Programming
New2Programming

Reputation: 383

This I now woking as expected thanks to Gordon Linoff Re Dates and Caius Jard for pointing me in to Ntile

Select * From(
select ID, Ref, Entered_Date, [Type], CREATEDBY, Office, Created_Date, Amt 
NTILE (10) OVER ( PARTITION BY CREATEDBY ORDER BY Amt desc)  AS PercentageNo
from Tbl 
where Entered_Date >= dateadd(month, -1, dateadd(day, 1 - day(getdate()), convert(date, getdate()))) and Entered_Date < dateadd(day, 1 - day(getdate()), convert(date, getdate())) as SubQuery where PercentageNo=1 order By Amt

Upvotes: 0

ahmad moghimi
ahmad moghimi

Reputation: 21

you can use row_number() for this reason

Upvotes: 1

Afshin Rashidi
Afshin Rashidi

Reputation: 353

you can use GROUP BY for select percentage of each user as this:

select      top 10 percent ID, Ref, Entered_Date, [Type], CREATEDBY, Office, Created_Date, Amt 
FROM Tbl 
where       DATEPART(m, Entered_Date) = DATEPART(m, DATEADD(m, -1, getdate())) 
            AND DATEPART(yyyy, Entered_Date) = DATEPART(yyyy, DATEADD(m, -1, getdate())) 
            AND CreatedBy IN ('User1','User2')
GROUP BY    ID, Ref, Entered_Date, [Type], CREATEDBY, Office, Created_Date, Amt
order by    amt DESC

Upvotes: 1

Related Questions