CODEWITHSUNDEEP
sqlt-sqlrankpartition
Marc Proulx
Marc Proulx

Reputation: 33

Rank() Over with multiple Partition

I'm trying to find how many times a ticket change ownergroup. I'm using this query:

SELECT        
TICKETID, CHANGEDATE, OWNERGROUP, 
RANK() OVER (Partition by TICKETID, OWNERGROUP ORDER BY CHANGEDATE) rk
FROM TKSTATUS where TICKETID= '12345' order by CHANGEDATE

And got this Result Set: enter image description here

After I count the 1... and get 4.

I expect this result but cannot find how I can do this:

RK
1
1
2
1
2
3
1
1
1
2
3

and result to be 6.

I know it's not the way to do it because it group the TICKETID and OWNERGROUP before the rank.

I'll continue to find a way to do this but I would like to see if someone have some ideas.

Upvotes: 1

Views: 2573

Answers (3)

Antonio
Antonio

Reputation: 535

you could possibly use a CTE and the LAG function like this:

WITH CTE_Tickets as 
(  SELECT        
        TICKETID, CHANGEDATE, OWNERGROUP, 
        LAG(OWNERGROUP, 1) OVER (Partition by OWNERGROUP ORDER BY CHANGEDATE, OWNERGROUP ) as Prev
        FROM TKSTATUS where TICKETID= '12345')


    SELECT        
       TICKETID, CHANGEDATE, OWNERGROUP, Prev ,
       IIF(OWNERGROUP=Prev, 0, 1)  as changestatus
       FROM CTE_Tickets
       ORDER BY CHANGEDATE  , OWNERGROUP

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

In SQL Server 2012 you can use lag() to see the number of OwnerGroup changes by comparing the current row value with the previous row value of OwnerGroup.

select 
    TicketId
  , ChangeDate
  , OwnerGroup
  , OwnerGroupChange = case 
      when coalesce(OwnerGroup,'') 
          = lag(coalesce(OwnerGroup,'')) over (partition by TicketId order by ChangeDate)
        then 0 
    else 1 
    end
from TkStatus
where TicketId = '12345'
order by ChangeDate

To just get the number of changes you can use the above as a subquery or common table expression:

select 
    TicketId
  , OwnerGroupChange = sum(OwnerGroupChange) 
from (
  select 
      TicketId
    , ChangeDate
    , OwnerGroup
    , OwnerGroupChange = case 
        when coalesce(OwnerGroup,'') 
            = lag(coalesce(OwnerGroup,'')) over (partition by TicketId order by ChangeDate)
          then 0 
      else 1 
      end
  from TkStatus
  where TicketId = '12345'
  ) s

Prior to SQL Server 2012, you can use outer apply() to get the previous OwnerGroup like so:

select 
    t.TicketId
  , t.ChangeDate
  , t.OwnerGroup
  , OwnerGroupChange = case 
      when coalesce(t.OwnerGroup,'') = coalesce(x.OwnerGroup,'')
        then 0 
    else 1 
    end
from TkStatus t
  outer apply (
    select top 1
      i.OwnerGroup
    from TkStatus i
    where i.TicketId = t.TicketId
      and i.ChangeDate < t.ChangeDate
    order by i.ChangeDate desc
    ) x
where t.TicketId = '12345'
order by t.ChangeDate

Upvotes: 1

LONG
LONG

Reputation: 4610

    SELECT        
    TICKETID, CHANGEDATE, OWNERGROUP, 
    DENSE_RANK() OVER (Partition by TICKETID ORDER BY CHANGEDATE) as RK
    FROM TKSTATUS where TICKETID= '12345' order by CHANGEDATE

Upvotes: 0

Related Questions