Get the top 6 totals for each client

Question

I am looking to create a script which selects the first 6 months of data from each client. What I have at the moment is most definitely not right, and can someone please help me with this.

select Ticket_ClientName, count(Ticket_ClientName) as DisplayNameCount, 
concat(datepart(year, cast(t.ticket_opendate as date)), RIGHT('00'+ convert(NVARCHAR(2),(datepart(month, cast(t.ticket_opendate as date)))),2)) as OpenDate, 
datename(month, cast(t.ticket_opendate as date)) as Month_Name
from dbo.Ticket t
where t.Ticket_ClientName is not null
and Ticket_DisplayId not like 'EH%'
and Ticket_Statusname not like 'Deleted'
group by ticket_ClientName,concat(datepart(year, cast(t.ticket_opendate as date)), RIGHT('00'+ convert(NVARCHAR(2),(datepart(month, cast(t.ticket_opendate as date)))),2)), datename(month, cast(t.ticket_opendate as date))

I know I would need a top 6 in somewhere, but I can't wrap my head around this. I would normally think of a for loop, so for example

for distinct ticket_clientname in tickets:

I'm just struggling for the best way to approach this. When I run the current query I get:

Ticket_ClientName DisplayNameCount OpenDate       Month_Name
------------------------------------------------------------
ClientName           1                202006         June
ClientName           1                202008         August
ClientName           13               202009         September

The data I would like is the top 6 per client I look forward to hearing from someone.

*Consumable* sample data, and expected results will help us help you. — Thom A, Aug 26 '21 at 13:23
Does this answer your question? [TOP N problem with GROUP BY clause](https://stackoverflow.com/questions/757763/top-n-problem-with-group-by-clause) — paneerakbari, Aug 26 '21 at 13:34
Is that the sample or expected, and where is the other, @cai120 ? From your comment, *"When I run the current query I get:"* it looks like neither. — Thom A, Aug 26 '21 at 13:36
@paneerakbari Hi, thanks for your comment, I am just reading this now, I will let you know. — cai120, Aug 26 '21 at 13:39
@Larnu that is the result I get and the expected result. I want the top 6 of each distinct client — cai120, Aug 26 '21 at 13:40
Like I mentioned, give us sample data and expected results; not the wrong results you currently get. We can't reverse engineer your data into what it looked like *before* your failed attempt and then work out what you *expect* to get from that. Help us help you. — Thom A, Aug 26 '21 at 13:41
@Larnu As previously mentioned, there is no explicitly wrong data, I just need the top 6 for each distinct client — cai120, Aug 26 '21 at 13:46
So that is your sample data? Ok, so what are your expected results for that data? — Thom A, Aug 26 '21 at 13:46
See also https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group — Charlieface, Aug 26 '21 at 14:23
Please provide queries to create the dbo.Ticket table and to insert sample data (make sure we have enough rows in the sample to cover all cases) and describe the expected result according to that sample data. Thanks — Ronen Ariely, Aug 26 '21 at 16:45
Can you add sample data and table schema so we can try and provide an answer? — Gudwlk, Aug 27 '21 at 09:24

score 1 · Accepted Answer · answered Aug 27 '21 at 10:51

I am adding a sample answer here. You can use Windows functions, Top (n) order by , to get your answer.

 SELECT 
   A.Ticket_ClientName
  ,A.DisplayNameCount
  ,A.OpenDate
  ,A.Month_Name
FROM
(

     select Ticket_ClientName, 
       count(Ticket_ClientName) as DisplayNameCount
       ,concat(datepart(year, cast(t.ticket_opendate as date)), RIGHT('00'+ convert(NVARCHAR(2),(datepart(month, cast(t.ticket_opendate as date)))),2)) as OpenDate, 
      datename(month, cast(t.ticket_opendate as date)) as Month_Name
      ,DATEPART(MM, cast(t.ticket_opendate as date)) as [Month_Name_int]
      from dbo.Ticket t
     where t.Ticket_ClientName is not null
       and Ticket_DisplayId not like 'EH%'
       and Ticket_Statusname not like 'Deleted'
       group by DATEPART(MM, cast(t.ticket_opendate as date)),ticket_ClientName,concat(datepart(year, cast(t.ticket_opendate as date)), RIGHT('00'+ convert(NVARCHAR(2),(datepart(month, cast(t.ticket_opendate as date)))),2)), datename(month, cast(t.ticket_opendate as date))
        )AS A 
      WHERE A.Month_Name_int <=6

I added my sample data and get the data ranked by month (int) Sample data i used:

Create table dbo.Ticket
(
 Ticket_ClientName varchar(100)
,ticket_opendate Date
,Ticket_DisplayId varchar(22) 
,Ticket_Statusname  varchar(100)
 )
 insert into  dbo.Ticket values ('test_client1' , '2020-01-01', 'AH', 'open')
  ,('test_client1' , '2020-01-02', 'AH', 'open')
 ,('test_client1' , '2020-01-03', 'AH', 'open')
 ,('test_client1' , '2020-01-04', 'AH', 'open')
 ,('test_client1' , '2020-01-05', 'AH', 'open')
 ,('test_client1' , '2020-01-06', 'AH', 'open')
 ,('test_client1' , '2020-02-02', 'AH', 'open')
 ,('test_client1' , '2020-04-03', 'AH', 'open')
 ,('test_client1' , '2020-03-04', 'AH', 'open')
 ,('test_client1' , '2020-02-05', 'AH', 'open')
 ,('test_client1' , '2020-03-06', 'AH', 'open')

Get the top 6 totals for each client

1 Answers1