CODEWITHSUNDEEP
sqlsql-servert-sql
James Khan
James Khan

Reputation: 841

Partition by and order by with multiple clauses

I have call data that is split into separate segments detailing each part of it journey. So for one call, it may have 3 segments like below in table, CallData_Segments

   Destination Number  / Call Type / Service Nos / Unique Call ID / Start DateTime / End DateTime 
    234 4   14  666       22082020 01:00    2208202001:01 
    234 4   15  666       22082020 01:00    22082020 01:01 
    234 5   12  666       22082020 01:03    22082020 01:04 
    234 3   13  666       22082020 01:04    22082020 01:06

The rules are :

So for the above example the output expected is as follows ( 2 Segments ):

Destination Number  / Call Type / START Service Nos /END Service Nos / Unique Call ID / Start DateTime / End DateTime / Segment Nos
    234 4   14  15  666     22082020 01:00  22082020 01:03  1
    234 5   12  13  666     22082020 01:03  22082020 01:06  2

I have tried a few combinations with row numbers with partition and order by and joining onto the same table without success. Any ideas ?

Upvotes: 0

Views: 109

Answers (1)

SteveC
SteveC

Reputation: 6015

Without sample data... something like this

;with partitions_cte as (
    select *, row_number() over (partition by [Unique Call ID] order by [Start DateTime]) rn
    from CallData_Segments)
select *
from partitions_cte
where
  [Call Type]=5
  or rn=1
  and [Unique Call ID] not in(select [Unique Call ID] 
                              from CallData_Segments
                              group by [Unique Call ID]
                              having count(*)=1);

Upvotes: 1

Related Questions