Unfoirtunatekly it doesn't.I need the output to break on every change of callid and useridThe code from ViaskH groups all the same userids together and gives me first/last times in general without a break.

Unfoirtunatekly it doesn't.I need the output to break on every change of callid and useridThe code from ViaskH groups all the same userids together and gives me first/last times in general without a break.

Rows 1-3 should be one row with the start time from row 1 and the end time from row 3Rows 4-6 should be one row with the start time from row 4 and the end time from row 6Rows 7-9 are fine as they are as they are a single row each.

Rows 1-3 should be one row with the start time from row 1 and the end time from row 3Rows 4-6 should be one row with the start time from row 4 and the end time from row 6Rows 7-9 are fine as they are as they are a single row each.

That bit works.I've changed the code to use my actual data as follows:-

SELECT CallId,UserId,MIN(CallEventModifiedAt) AS Start_Time,MAX(CallEventModifiedAt) AS End_Time
FROM #original_crystal t
OUTER APPLY (SELECT MIN(CallEventModifiedAt) AS MinDate
FROM #original_crystal
WHERE CallEventModifiedAt> t.CallEventModifiedAt
AND UserId <> t.UserId
)t1
GROUP BY CallId,UserId,MinDate
ORDER BY callid,Start_Time

That bit works.I've changed the code to use my actual data as follows:-

SELECT CallId,UserId,MIN(CallEventModifiedAt) AS Start_Time,MAX(CallEventModifiedAt) AS End_Time
FROM #original_crystal t
OUTER APPLY (SELECT MIN(CallEventModifiedAt) AS MinDate
FROM #original_crystal
WHERE CallEventModifiedAt> t.CallEventModifiedAt
AND UserId <> t.UserId
)t1
GROUP BY CallId,UserId,MinDate
ORDER BY callid,Start_Time

Either you provide us proper sample data to exactly replicate your scenario or try to tweak given suggestion to suit your scenario.Nobody will be able to help you out if you cant give us enough information on your exact problem.I'm not gonna waste anymore time on this unless you post proper sample data to EXACTLY REPLICATE YOUR SCENARIO

The discussion has progressed way beyond where I was involved, so if what I am saying is irrelevant, please ignore: The original query I posted should work. If you group only by two columns it would bunch up the two distinct sections of 4. If you group by the third column as well (GroupId), it should separate out the two sections of 4.

I've extracted on call to (hopefully) give all the info you you need.I've filtered the results temporarily for testing purposes as there are approx 25000 different call IDs with half a million records in total.

;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY CallId ORDER BY CallEventModifiedAt)
- ROW_NUMBER() OVER (PARTITION BY CallId,UserId ORDER BY CallEventModifiedAt) AS GroupID
FROM
@TheTable
)
SELECT CallId,
UserId,
MIN(CallEventModifiedAt) AS StartTime,
MAX(CallEventModifiedAt) AS EndTime
FROM CTE
GROUP BY
CallId,
UserId,
GroupId
ORDER BY CallId, StartTime;
CROSS CHECK FOR SINGLE CALLID ONCE... You will get idea...
--Method2 is:
SELECT CallId,UserId,MIN(CallEventModifiedAt) AS Start_Time,MAX(CallEventModifiedAt) AS End_Time
FROM @TheTable t
OUTER APPLY (SELECT MIN(CallEventModifiedAt) AS MinDate
FROM @TheTable
WHERE CallEventModifiedAt> t.CallEventModifiedAt
AND UserId <> t.UserId
AND CallId = t.CallId -- EDIT
)t1
GROUP BY CallId,UserId,MinDate
ORDER BY CallID, Start_Time

SELECT
CallId
,UserId
,max(CallEventModifiedAt) AS Start_Time
,DispositionId
FROM #original_crystal t
OUTER APPLY (
SELECT
max(CallEventModifiedAt) AS MinDate
FROM
#original_crystal
WHERE
CallEventModifiedAt> t.CallEventModifiedAt
and CallId=t.CallId
and DispositionId is not null
)t1
where
DispositionId is not null
GROUP BY
CallId
,UserId
,DispositionId
ORDER BY
CallId
,max(CallEventModifiedAt)
,DispositionId

But it's not giving me the right output.This is the output I get for the call specified above:-

select
t2.CallId
,t2.UserId
,t2.Start_Time
,dispositionID
from
(SELECT
CallId
,UserId
,max(CallEventModifiedAt) AS Start_Time
FROM #original_crystal t
OUTER APPLY (
SELECT
max(CallEventModifiedAt) AS MaxDate
FROM
#original_crystal
WHERE
CallEventModifiedAt> t.CallEventModifiedAt
and CallId=t.CallId
and DispositionId is not null
)t1
where
DispositionId is not null
GROUP BY
CallId
,UserId
) t2
inner join
#original_crystal o
on o.CallId=t2.CallId
and o.UserId=t2.UserId
and t2.Start_Time=o.CallEventModifiedAt
where
o.DispositionId is not null
order by
o.CallId
,t2.Start_Time

It's not the tidiest code in the world and I'm still checking to make sure it works (it seems to for the couple of callid's I've checked).

With the above data, I need to get an output which shows:-User 10681 at 2013-01-27 02:19:57.617 with a DispositionID of 33User 10681 at 2013-01-27 02:32:46.937 with a DispositionID of 56 (there's a diffeent user between these two entries)User 4467 at 2013-01-27 02:43:44.980 with a DispositionID of 332User 5095 at 2013-01-27 02:43:44.980 with a DispositionID of 760

I thought I'd got this cracked but forgot to take into account mutliple users.

If it makes it easier, you can use the CallEventTypeID field as this will always contain an ID of 26 for the change of DispositionID. I still need the last one for each user-block, even if the same user has multiple entires with a different user in between them.