CODEWITHSUNDEEP
sql-servert-sql
James
James

Reputation: 95

Selecting data from multiple rows in grouped results

I have come across a TSQL problem where I have some grouped results and need some data from each of the rows that make up the grouped results, ideally - I would like the Recipient from the first row as 'HuntGroup and the Recipient from the second row as 'Recipient'.

There may be 1 or 2 rows in the grouped results so I will only need results from where there are 2 rows. There is a CorrelationCode that ties the two records together - one row will have the hunt group info and the other will have the answering user.

If you want to test, you can generate the data from the below: 

CREATE TABLE #phone_calls
(
    Caller VARCHAR(200),
    Recipient VARCHAR(200),
    CorrelationCode VARCHAR(200),
    StartTime VARCHAR(200),
    EndTime VARCHAR(200),
);

INSERT INTO #phone_calls(Caller, Recipient, CorrelationCode, StartTime, EndTime)
VALUES 
    ( 'user159', 'huntgroup1',  'AEF4562JJH',   DATEADD(mi, -60,GETDATE()), DATEADD(mi, -60, GETDATE()) ),
    ( 'user159', 'user10',      'AEF4562JJH',   DATEADD(mi, -59,GETDATE()), DATEADD(mi, -56, GETDATE()) ),
    ( 'user11', 'huntgroup2',   'OSJEJDJE883',  DATEADD(mi, -58,GETDATE()), DATEADD(mi, -58, GETDATE()) ),
    ( 'user11', 'user5',        'OSJEJDJE883',  DATEADD(mi, -57,GETDATE()), DATEADD(mi, -56, GETDATE()) ),
    ( 'user449', 'huntgroup1',  'SHEJJSMM27',   DATEADD(mi, -56,GETDATE()), DATEADD(mi, -56, GETDATE()) ),
    ( 'user449', 'user91',      'SHEJJSMM27',   DATEADD(mi, -55,GETDATE()), DATEADD(mi, -54, GETDATE()) ),
    ( 'user128', 'huntgroup1',  'USJEHHS45461', DATEADD(mi, -54,GETDATE()), DATEADD(mi, -54, GETDATE()) ),
    ( 'user128', 'user46',      'USJEHHS45461', DATEADD(mi, -53,GETDATE()), DATEADD(mi, -46, GETDATE()) ),
    ( 'user120', 'huntgroup1',  'SEES45646',    DATEADD(mi, -52,GETDATE()), DATEADD(mi, -52, GETDATE()) ),
    ( 'user120', 'user5',       'SEES45646',    DATEADD(mi, -51,GETDATE()), DATEADD(mi, -49, GETDATE()) ),
    ( 'user316', 'huntgroup1',  'LLLS45646',    DATEADD(mi, -50,GETDATE()), DATEADD(mi, -50, GETDATE()) ),
    ( 'user316', 'user5',       'LLLS45646',    DATEADD(mi, -49,GETDATE()), DATEADD(mi, -49, GETDATE()) ),
    ( 'user19', 'huntgroup1',   'YHJASA434',    DATEADD(mi, -48,GETDATE()), DATEADD(mi, -48, GETDATE()) ),
    ( 'user202', 'huntgroup1',  '78RTGDHDG',    DATEADD(mi, -47,GETDATE()), DATEADD(mi, -47, GETDATE()) ),
    ( 'user141', 'huntgroup2',  'CFGBN1231',    DATEADD(mi, -46,GETDATE()), DATEADD(mi, -46, GETDATE()) ),
    ( 'user168', 'huntgroup1',  'KKLDJ5463',    DATEADD(mi, -45,GETDATE()), DATEADD(mi, -45, GETDATE()) ),
    ( 'user168', 'user91',      'KKLDJ5463',    DATEADD(mi, -44,GETDATE()), DATEADD(mi, -40, GETDATE()) );

A SELECT * will give the following results: 

Caller      Recipient   CorrelationCode StartTime       EndTime
user159     huntgroup1  AEF4562JJH  Sep 13 2018  3:56PM Sep 13 2018  3:56PM
user159     user10      AEF4562JJH  Sep 13 2018  3:57PM Sep 13 2018  4:00PM
user11      huntgroup2  OSJEJDJE883 Sep 13 2018  3:58PM Sep 13 2018  3:58PM
user11      user5       OSJEJDJE883 Sep 13 2018  3:59PM Sep 13 2018  4:00PM
user449     huntgroup1  SHEJJSMM27  Sep 13 2018  4:00PM Sep 13 2018  4:00PM
user449     user91      SHEJJSMM27  Sep 13 2018  4:01PM Sep 13 2018  4:02PM
user128     huntgroup1  USJEHHS45461    Sep 13 2018  4:02PM Sep 13 2018  4:02PM
user128     user46      USJEHHS45461    Sep 13 2018  4:03PM Sep 13 2018  4:10PM
user120     huntgroup1  SEES45646   Sep 13 2018  4:04PM Sep 13 2018  4:04PM
user120     user5       SEES45646   Sep 13 2018  4:05PM Sep 13 2018  4:07PM
user316     huntgroup1  LLLS45646   Sep 13 2018  4:06PM Sep 13 2018  4:06PM
user316     user5       LLLS45646   Sep 13 2018  4:07PM Sep 13 2018  4:07PM
user19      huntgroup1  YHJASA434   Sep 13 2018  4:08PM Sep 13 2018  4:08PM
user202     huntgroup1  78RTGDHDG   Sep 13 2018  4:09PM Sep 13 2018  4:09PM
user141     huntgroup2  CFGBN1231   Sep 13 2018  4:10PM Sep 13 2018  4:10PM
user168     huntgroup1  KKLDJ5463   Sep 13 2018  4:11PM Sep 13 2018  4:11PM
user168     user91      KKLDJ5463   Sep 13 2018  4:12PM Sep 13 2018  4:16PM

What I am trying to get in the result set is: 

Caller      HuntGroup   Recipient   CorrelationCode StartTime       EndTime
user159     huntgroup1  user10      AEF4562JJH  Sep 13 2018  3:56PM Sep 13 2018  4:00PM
user168     huntgroup1  user91      KKLDJ5463   Sep 13 2018  4:11PM Sep 13 2018  4:16PM
user316     huntgroup1  user5       LLLS45646   Sep 13 2018  4:06PM Sep 13 2018  4:07PM
user11      huntgroup2  user5       OSJEJDJE883 Sep 13 2018  3:58PM Sep 13 2018  4:00PM
user120     huntgroup1  user5       SEES45646   Sep 13 2018  4:04PM Sep 13 2018  4:07PM
user449     huntgroup1  user91      SHEJJSMM27  Sep 13 2018  4:00PM Sep 13 2018  4:02PM
user128     huntgroup1  user46      USJEHHS45461    Sep 13 2018  4:02PM Sep 13 2018  4:10PM

I can achieve this with the below SQL but naturally this won't work because it is dependent on using MIN and MAX(Recipient) so it won't work. 

 SELECT 
    MIN(Caller) Caller, 
    MIN(Recipient) HuntGroup, 
    MAX(Recipient) Recipient, 
    MIN(CorrelationCode) CorrelationCode, 
    MIN(StartTime) StartTime, 
    MAX(EndTime) EndTime 
FROM 
(
    SELECT 
         1 AS 'NumAnswers', Caller, Recipient, CorrelationCode, StartTime, EndTime
    FROM #phone_calls
) c 
GROUP BY c.CorrelationCode
HAVING SUM(NumAnswers) > 1

I have also played around with ;WITH cte but I am not experienced enough to get both values from it

;WITH cte AS 
(
  SELECT Caller, Recipient AS 'HuntGroup', Recipient, CorrelationCode, StartTime, EndTime,
         ROW_NUMBER() OVER (PARTITION BY CorrelationCode ORDER BY Recipient) rn
  FROM #phone_calls
)

SELECT * FROM cte
WHERE rn = 2

So, if there is any TSQL gurus out there that can offer a solution, it would be appreciated.

Upvotes: 2

Views: 55

Answers (1)

Ronen Ariely
Ronen Ariely

Reputation: 2434

Good day,

Please check if one of these solutions fit your need (I use table phone_calls instead of #phone_calls):

-- Option 1
;with MyCTE as (
    select 
        [Caller], Recipient, CorrelationCode, StartTime, EndTime
        ,RN = ROW_NUMBER() over (partition by [Caller], CorrelationCode order by StartTime, EndTime)
    from phone_calls
)
,MyCTE2 as (
    select RN, [Caller], Recipient as HuntGroup, null as Recipient, CorrelationCode, StartTime, null as EndTime
    from MyCTE
    where RN = 1
    union all
    select RN, [Caller], null as HuntGroup, Recipient, CorrelationCode, null as StartTime, EndTime
    from MyCTE
    where RN = 2
)
select [Caller],MAX(HuntGroup),MAX(Recipient),CorrelationCode,MAX(StartTime),MAX(EndTime)
from MyCTE2
group by [Caller], CorrelationCode
having max(RN) > 1
GO


-- Option 2:
;with MyCTE as (
    select 
        [Caller], Recipient, CorrelationCode, StartTime, EndTime
        ,RN = ROW_NUMBER() over (partition by [Caller], CorrelationCode order by StartTime, EndTime)
    from phone_calls
)
,MyCTE1 as (
    select RN, [Caller], Recipient, CorrelationCode, StartTime, EndTime
    from MyCTE
    where RN = 1
)
,MyCTE2 as (
    select RN, [Caller], Recipient, CorrelationCode, StartTime, EndTime
    from MyCTE
    where RN = 2
)
SELECT 
    t1.Caller, t1.Recipient as HuntGroup, t2.Recipient, t1.CorrelationCode, 
    t1.StartTime, t2.EndTime
from MyCTE1 t1
LEFT JOIN MyCTE2 t2 ON t1.Caller = t2.Caller and t1.CorrelationCode = t2.CorrelationCode
where not t2.Caller IS NULL

Upvotes: 2

Related Questions