| Author |
 Topic  |
|
rmg1
Constraint Violating Yak Guru
253 Posts |
 Posted - 01/28/2013 : 05:39:34
|
Unfoirtunatekly it doesn't.
I need the output to break on every change of callid and userid
The code from ViaskH groups all the same userids together and gives me first/last times in general without a break. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 01/28/2013 : 05:44:04
|
quote:
Originally posted by rmg1
Unfoirtunatekly it doesn't.
I need the output to break on every change of callid and userid
The code from ViaskH groups all the same userids together and gives me first/last times in general without a break.
Thats not true
see illustration for your given data below
DECLARE @TheTable TABLE(CallId int, CallEventID int, StartTime datetime, UserId int, CallEventTypeId int, CallEventModifiedAt datetime)
insert into @TheTable
select 43517507, 1, '25/01/2013 00:05:51', 13222, 11, '25/01/2013 00:05:51' union all
select 43517507 ,2, '25/01/2013 00:05:51', 13222, 5, '25/01/2013 00:05:51' union all
select 43517507 ,7, '25/01/2013 00:05:51', 13222, 25, '25/01/2013 00:10:22' union all
select 43517507 ,12, '25/01/2013 00:05:51', 13222, 6, '25/01/2013 00:13:10' union all
select 43517507 ,13, '25/01/2013 00:05:51', 13222, 23,'25/01/2013 00:13:10' union all
select 43517507 ,14, '25/01/2013 00:05:51', 13176, 24, '25/01/2013 00:14:24' union all
select 43517507, 15, '25/01/2013 00:05:51', 13176, 5, '25/01/2013 00:14:24' union all
select 43517507, 19, '25/01/2013 00:05:51', 13176, 50, '25/01/2013 00:24:50' union all
select 43517507, 20, '25/01/2013 00:05:51', 13176, 26, '25/01/2013 00:27:04' union all
select 43517507, 21, '25/01/2013 00:05:51', 13176, 27, '25/01/2013 00:27:04' union all
select 43517507, 32, '25/01/2013 00:05:51' ,4, 52, '25/01/2013 00:29:00' union all
select 43517507, 33, '25/01/2013 00:05:51', 13176, 6, '25/01/2013 00:29:01' union all
select 43517507, 34, '25/01/2013 00:05:51', 13176, 36, '25/01/2013 00:29:01' union all
select 43517507, 35, '25/01/2013 00:05:51', 4, 53, '25/01/2013 00:35:16'
--This is Visakh's post (01/25/2013 : 03:20:54)
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
)t1
GROUP BY CallId,UserId,MinDate
ORDER BY Start_Time
output
------------------------------
CallId UserId Start_Time End_Time
---------------------------------------------------------------------------------
43517507 13222 2013-01-25 00:05:51.000 2013-01-25 00:13:10.000
43517507 13176 2013-01-25 00:14:24.000 2013-01-25 00:27:04.000
43517507 4 2013-01-25 00:29:00.000 2013-01-25 00:29:00.000
43517507 13176 2013-01-25 00:29:01.000 2013-01-25 00:29:01.000
43517507 4 2013-01-25 00:35:16.000 2013-01-25 00:35:16.000
If you're not getting output correctly then I'm sure you've some other rules which you've not specified here so far
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
Edited by - visakh16 on 01/28/2013 05:44:43 |
|
|
|
rmg1
Constraint Violating Yak Guru
253 Posts |
Posted - 01/28/2013 : 05:58:47
|
Using the SQL code above, I get this output:-
Row CallId UserId Start_Time End_Time
1 43517507 13222 25/01/2013 00:05:51 25/01/2013 00:05:51
2 43517507 13222 25/01/2013 00:10:22 25/01/2013 00:10:22
3 43517507 13222 25/01/2013 00:13:10 25/01/2013 00:13:10
4 43517507 13176 25/01/2013 00:14:24 25/01/2013 00:14:24
5 43517507 13176 25/01/2013 00:24:50 25/01/2013 00:24:50
6 43517507 13176 25/01/2013 00:27:04 25/01/2013 00:27:04
7 43517507 4 25/01/2013 00:29:00 25/01/2013 00:29:00
8 43517507 13176 25/01/2013 00:29:01 25/01/2013 00:29:01
9 43517507 4 25/01/2013 00:35:16 25/01/2013 00:35:16
I've added in row numbers to make the next bit easier to explain.
Rows 1-3 should be one row with the start time from row 1 and the end time from row 3
Rows 4-6 should be one row with the start time from row 4 and the end time from row 6
Rows 7-9 are fine as they are as they are a single row each. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 01/28/2013 : 06:05:59
|
quote:
Originally posted by rmg1
Using the SQL code above, I get this output:-
Row CallId UserId Start_Time End_Time
1 43517507 13222 25/01/2013 00:05:51 25/01/2013 00:05:51
2 43517507 13222 25/01/2013 00:10:22 25/01/2013 00:10:22
3 43517507 13222 25/01/2013 00:13:10 25/01/2013 00:13:10
4 43517507 13176 25/01/2013 00:14:24 25/01/2013 00:14:24
5 43517507 13176 25/01/2013 00:24:50 25/01/2013 00:24:50
6 43517507 13176 25/01/2013 00:27:04 25/01/2013 00:27:04
7 43517507 4 25/01/2013 00:29:00 25/01/2013 00:29:00
8 43517507 13176 25/01/2013 00:29:01 25/01/2013 00:29:01
9 43517507 4 25/01/2013 00:35:16 25/01/2013 00:35:16
I've added in row numbers to make the next bit easier to explain.
Rows 1-3 should be one row with the start time from row 1 and the end time from row 3
Rows 4-6 should be one row with the start time from row 4 and the end time from row 6
Rows 7-9 are fine as they are as they are a single row each.
copy paste the one i posted and execute
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
rmg1
Constraint Violating Yak Guru
253 Posts |
Posted - 01/28/2013 : 06:26:34
|
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
And this is my output for one call :-
CallId UserId Start_Time End_Time
43517507 13222 2013-01-25 00:05:50.967 2013-01-25 00:05:51.060
43517507 13222 2013-01-25 00:10:22.280 2013-01-25 00:10:22.280
43517507 13222 2013-01-25 00:13:09.740 2013-01-25 00:13:09.740
43517507 13176 2013-01-25 00:14:23.960 2013-01-25 00:14:23.960
43517507 13176 2013-01-25 00:24:50.473 2013-01-25 00:24:50.473
43517507 13176 2013-01-25 00:27:03.880 2013-01-25 00:27:03.880
43517507 4 2013-01-25 00:29:00.080 2013-01-25 00:29:00.080
43517507 13176 2013-01-25 00:29:00.810 2013-01-25 00:29:00.810
43517507 4 2013-01-25 00:35:15.920 2013-01-25 00:35:15.920
Any ideas why the difference? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 01/28/2013 : 06:33:35
|
quote:
Originally posted by rmg1
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
And this is my output for one call :-
CallId UserId Start_Time End_Time
43517507 13222 2013-01-25 00:05:50.967 2013-01-25 00:05:51.060
43517507 13222 2013-01-25 00:10:22.280 2013-01-25 00:10:22.280
43517507 13222 2013-01-25 00:13:09.740 2013-01-25 00:13:09.740
43517507 13176 2013-01-25 00:14:23.960 2013-01-25 00:14:23.960
43517507 13176 2013-01-25 00:24:50.473 2013-01-25 00:24:50.473
43517507 13176 2013-01-25 00:27:03.880 2013-01-25 00:27:03.880
43517507 4 2013-01-25 00:29:00.080 2013-01-25 00:29:00.080
43517507 13176 2013-01-25 00:29:00.810 2013-01-25 00:29:00.810
43517507 4 2013-01-25 00:35:15.920 2013-01-25 00:35:15.920
Any ideas why the difference?
How do you think we can assume what your exact data is?
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1711 Posts |
Posted - 01/28/2013 : 06:49:23
|
Post the actual data which you had now for this scenario........
May be why you got that means...
You have different CallIds inbetween same callid like follows..
For example callid 43517506 is placed in between 43517507.
DECLARE @TheTable TABLE(CallId int, CallEventID int, StartTime datetime, UserId int, CallEventTypeId int, CallEventModifiedAt datetime)
insert into @TheTable
select 43517507, 1, '25/01/2013 00:05:51', 13222, 11, '25/01/2013 00:05:51' union all
select 43517507 ,2, '25/01/2013 00:05:51', 13222, 5, '25/01/2013 00:05:51' union all
select 43517506 ,7, '25/01/2013 00:05:51', 13222, 25, '25/01/2013 00:10:22' union all
select 43517507 ,7, '25/01/2013 00:05:51', 13222, 25, '25/01/2013 00:10:22' union all
select 43517507 ,12, '25/01/2013 00:05:51', 13222, 6, '25/01/2013 00:13:10' union all
select 43517507 ,13, '25/01/2013 00:05:51', 13222, 23,'25/01/2013 00:13:10' union all
select 43517507 ,14, '25/01/2013 00:05:51', 13176, 24, '25/01/2013 00:14:24' union all
select 43517507, 15, '25/01/2013 00:05:51', 13176, 5, '25/01/2013 00:14:24' union all
select 43517507, 19, '25/01/2013 00:05:51', 13176, 50, '25/01/2013 00:24:50' union all
select 43517507, 20, '25/01/2013 00:05:51', 13176, 26, '25/01/2013 00:27:04' union all
select 43517507, 21, '25/01/2013 00:05:51', 13176, 27, '25/01/2013 00:27:04' union all
select 43517507, 32, '25/01/2013 00:05:51' ,4, 52, '25/01/2013 00:29:00' union all
select 43517507, 33, '25/01/2013 00:05:51', 13176, 6, '25/01/2013 00:29:01' union all
select 43517507, 34, '25/01/2013 00:05:51', 13176, 36, '25/01/2013 00:29:01' union all
select 43517507, 35, '25/01/2013 00:05:51', 4, 53, '25/01/2013 00:35:16'
--
Chandu |
Edited by - bandi on 01/28/2013 06:55:36 |
|
|
|
rmg1
Constraint Violating Yak Guru
253 Posts |
Posted - 01/28/2013 : 06:57:32
|
This is actual data from my original table:-
CallId CallEventID StartTime UserId CallEventTypeId CallEventModifiedAt CallTypeID CallEventId2 ToCallPriorityId DispositionId Disposition Received Call_Priority PCCallEventID
43517507 1 2013-01-25 00:05:50.967 13222 11 2013-01-25 00:05:50.967 1 7 3 NULL NULL 2013-01-25 00:05:50.967 2 7
43517507 2 2013-01-25 00:05:50.967 13222 5 2013-01-25 00:05:51.060 1 7 3 NULL NULL 2013-01-25 00:05:50.967 2 7
43517507 7 2013-01-25 00:05:50.967 13222 25 2013-01-25 00:10:22.280 1 7 3 NULL NULL 2013-01-25 00:05:50.967 2 7
43517507 12 2013-01-25 00:05:50.967 13222 6 2013-01-25 00:13:09.740 1 7 3 NULL NULL 2013-01-25 00:05:50.967 2 7
43517507 13 2013-01-25 00:05:50.967 13222 23 2013-01-25 00:13:09.740 1 7 3 NULL NULL 2013-01-25 00:05:50.967 2 7
43517507 14 2013-01-25 00:05:50.967 13176 24 2013-01-25 00:14:23.960 1 7 3 NULL NULL 2013-01-25 00:05:50.967 2 7
43517507 15 2013-01-25 00:05:50.967 13176 5 2013-01-25 00:14:23.960 1 7 3 NULL NULL 2013-01-25 00:05:50.967 2 7
43517507 19 2013-01-25 00:05:50.967 13176 50 2013-01-25 00:24:50.473 1 7 3 NULL NULL 2013-01-25 00:05:50.967 2 7
43517507 20 2013-01-25 00:05:50.967 13176 26 2013-01-25 00:27:03.880 1 7 3 333 Refer to Primary Care Service Urgent 2013-01-25 00:05:50.967 2 7
43517507 21 2013-01-25 00:05:50.967 13176 27 2013-01-25 00:27:03.880 1 7 3 NULL NULL 2013-01-25 00:05:50.967 2 7
43517507 32 2013-01-25 00:05:50.967 4 52 2013-01-25 00:29:00.080 1 7 3 NULL NULL 2013-01-25 00:05:50.967 2 7
43517507 33 2013-01-25 00:05:50.967 13176 6 2013-01-25 00:29:00.810 1 7 3 NULL NULL 2013-01-25 00:05:50.967 2 7
43517507 34 2013-01-25 00:05:50.967 13176 36 2013-01-25 00:29:00.810 1 7 3 NULL NULL 2013-01-25 00:05:50.967 2 7
43517507 35 2013-01-25 00:05:50.967 4 53 2013-01-25 00:35:15.920 1 7 3 NULL NULL 2013-01-25 00:05:50.967 2 7
Apologies for the formatting. |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1711 Posts |
Posted - 01/28/2013 : 07:20:08
|
Are you filtering result by passing specific Callid?
--
Chandu |
|
|
|
James K
Flowing Fount of Yak Knowledge
1739 Posts |
Posted - 01/28/2013 : 07:35:31
|
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.FROM CTE
GROUP BY
CallId,
UserId,
GroupId; -- THIS IS REQUIRED |
|
|
|
rmg1
Constraint Violating Yak Guru
253 Posts |
Posted - 01/28/2013 : 07:39:09
|
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. |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1711 Posts |
Posted - 01/28/2013 : 07:52:30
|
Obviously you got the correct answer..
;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
--
Chandu
|
Edited by - bandi on 01/28/2013 07:56:31 |
|
|
|
rmg1
Constraint Violating Yak Guru
253 Posts |
Posted - 01/28/2013 : 08:56:06
|
Thanks for all the help folks, I've used Method 2 (above) and changed the name of the table it gets its data from.
All now appears to be working. |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1711 Posts |
Posted - 01/28/2013 : 08:56:43
|
Welcome
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 01/28/2013 : 08:58:50
|
quote:
Originally posted by rmg1
Thanks for all the help folks, I've used Method 2 (above) and changed the name of the table it gets its data from.
All now appears to be working.
At least next time give a clear representation of your actual data to avoid unnecessary confusions
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
rmg1
Constraint Violating Yak Guru
253 Posts |
Posted - 01/30/2013 : 06:39:03
|
Hi all
I'm back again.
I'm having a similar issue getting the last Disposition ID for a given user.
Here's my base data:-
CallId CallEventID StartTime UserId CallEventTypeId CallEventModifiedAt CallTypeID CallEventId2 ToCallPriorityId DispositionId Disposition Received Call_Priority PCCallEventID
43546818 1 2013-01-27 00:00:43.170 13379 11 2013-01-27 00:00:43.170 13 NULL NULL NULL NULL 2013-01-27 00:00:43.170 NULL NULL
43546818 2 2013-01-27 00:00:43.170 13379 5 2013-01-27 00:00:43.217 13 NULL NULL NULL NULL 2013-01-27 00:00:43.170 NULL NULL
43546818 6 2013-01-27 00:00:43.170 13379 50 2013-01-27 00:02:28.520 13 NULL NULL NULL NULL 2013-01-27 00:00:43.170 NULL NULL
43546818 10 2013-01-27 00:00:43.170 13379 26 2013-01-27 00:14:18.350 13 NULL NULL 594 Speak to Clinician from our service within 30 minutes 2013-01-27 00:00:43.170 NULL NULL
43546818 11 2013-01-27 00:00:43.170 13379 27 2013-01-27 00:14:18.350 13 NULL NULL NULL NULL 2013-01-27 00:00:43.170 NULL NULL
43546818 12 2013-01-27 00:00:43.170 13379 25 2013-01-27 00:14:18.420 13 NULL 3 NULL NULL 2013-01-27 00:00:43.170 2 NULL
43546818 13 2013-01-27 00:00:43.170 13379 113 2013-01-27 00:14:41.133 13 NULL NULL NULL NULL 2013-01-27 00:00:43.170 NULL NULL
43546818 14 2013-01-27 00:00:43.170 13379 26 2013-01-27 00:14:59.793 13 NULL NULL 56 Send to First Advice Queue 2013-01-27 00:00:43.170 NULL NULL
43546818 15 2013-01-27 00:00:43.170 13379 6 2013-01-27 00:17:11.653 13 NULL NULL NULL NULL 2013-01-27 00:00:43.170 NULL NULL
43546818 16 2013-01-27 00:00:43.170 13379 23 2013-01-27 00:17:11.653 13 NULL NULL NULL NULL 2013-01-27 00:00:43.170 NULL NULL
43546818 17 2013-01-27 00:00:43.170 10801 25 2013-01-27 00:17:21.980 13 NULL 2 NULL NULL 2013-01-27 00:00:43.170 1 NULL
43546818 18 2013-01-27 00:00:43.170 12156 24 2013-01-27 00:18:08.257 13 NULL NULL NULL NULL 2013-01-27 00:00:43.170 NULL NULL
43546818 19 2013-01-27 00:00:43.170 12156 5 2013-01-27 00:18:08.257 13 NULL NULL NULL NULL 2013-01-27 00:00:43.170 NULL NULL
43546818 23 2013-01-27 00:00:43.170 12156 26 2013-01-27 00:32:12.310 13 NULL NULL 16 Home Care 2013-01-27 00:00:43.170 NULL NULL
43546818 24 2013-01-27 00:00:43.170 12156 27 2013-01-27 00:32:12.310 13 NULL NULL NULL NULL 2013-01-27 00:00:43.170 NULL NULL
43546818 28 2013-01-27 00:00:43.170 12156 6 2013-01-27 00:37:57.257 13 NULL NULL NULL NULL 2013-01-27 00:00:43.170 NULL NULL
43546818 29 2013-01-27 00:00:43.170 12156 36 2013-01-27 00:37:57.257 13 NULL NULL NULL NULL 2013-01-27 00:00:43.170 NULL NULL
I've tried adjusting the query from visakh16 to this:-
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:-
CallId UserId Start_Time DispositionId
43546818 13379 2013-01-27 00:14:18.350 594
43546818 13379 2013-01-27 00:14:59.793 56
43546818 12156 2013-01-27 00:32:12.310 16
What I want to get is the second item for user 13379 (i.e. Disposition ID 56) and it's giving me both.
The Disposition ID for user 12156 is fine as it's the only one.
I'd appreciate any help you can give. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 01/30/2013 : 06:50:44
|
whats your rule for selecting a row? is it latest row with valid value for DispositionId?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
rmg1
Constraint Violating Yak Guru
253 Posts |
Posted - 01/30/2013 : 07:43:27
|
Yes, I want the last row for each user/callid with a DispositionID that is not null.
In the data I posted above, I'd want DispositionIDs 56 (for user 13379) and 16 (for user 12156) |
|
|
|
rmg1
Constraint Violating Yak Guru
253 Posts |
Posted - 01/30/2013 : 08:45:43
|
Had a bit more a play and come up with this:-
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).
Can anyone suggest any improvements? |
|
|
|
rmg1
Constraint Violating Yak Guru
253 Posts |
Posted - 01/30/2013 : 10:06:51
|
Ignore my post above, this doesn't work properly.
What I need to do is take Method 2 above and grab the last DispositionID for each user as folllows:-
This is a different call ID
CallId CallEventID StartTime UserId CallEventTypeId CallEventModifiedAt CallTypeID CallEventId2 ToCallPriorityId DispositionId Received Call_Priority PCCallEventID
43547654 1 2013-01-27 02:17:01.553 10681 11 2013-01-27 02:17:01.553 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 2 2013-01-27 02:17:01.553 10681 5 2013-01-27 02:17:01.677 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 7 2013-01-27 02:17:01.553 10681 25 2013-01-27 02:19:45.030 1 NULL 2 NULL 2013-01-27 02:17:01.553 1 NULL
43547654 11 2013-01-27 02:17:01.553 10681 26 2013-01-27 02:19:57.617 1 NULL NULL 33 2013-01-27 02:17:01.553 NULL NULL
43547654 12 2013-01-27 02:17:01.553 10681 27 2013-01-27 02:19:57.617 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 13 2013-01-27 02:17:01.553 10681 50 2013-01-27 02:21:19.097 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 14 2013-01-27 02:17:01.553 10681 6 2013-01-27 02:24:48.790 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 15 2013-01-27 02:17:01.553 10681 23 2013-01-27 02:24:48.790 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 16 2013-01-27 02:17:01.553 4860 24 2013-01-27 02:24:50.227 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 17 2013-01-27 02:17:01.553 4860 5 2013-01-27 02:24:50.227 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 18 2013-01-27 02:17:01.553 4860 6 2013-01-27 02:27:58.830 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 19 2013-01-27 02:17:01.553 4860 23 2013-01-27 02:27:58.830 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 20 2013-01-27 02:17:01.553 10681 24 2013-01-27 02:28:08.900 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 21 2013-01-27 02:17:01.553 10681 5 2013-01-27 02:28:08.900 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 22 2013-01-27 02:17:01.553 10681 26 2013-01-27 02:32:46.937 1 NULL NULL 56 2013-01-27 02:17:01.553 NULL NULL
43547654 23 2013-01-27 02:17:01.553 10681 27 2013-01-27 02:32:46.937 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 25 2013-01-27 02:17:01.553 10681 6 2013-01-27 02:34:12.093 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 26 2013-01-27 02:17:01.553 10681 23 2013-01-27 02:34:12.093 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 27 2013-01-27 02:17:01.553 4467 24 2013-01-27 02:37:40.047 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 28 2013-01-27 02:17:01.553 4467 5 2013-01-27 02:37:40.047 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 33 2013-01-27 02:17:01.553 4467 26 2013-01-27 02:43:44.980 1 NULL NULL 332 2013-01-27 02:17:01.553 NULL NULL
43547654 34 2013-01-27 02:17:01.553 4467 27 2013-01-27 02:43:44.980 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 38 2013-01-27 02:17:01.553 4467 6 2013-01-27 02:44:54.057 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 39 2013-01-27 02:17:01.553 4467 36 2013-01-27 02:44:54.057 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 40 2013-01-27 02:17:01.553 4467 5 2013-01-27 02:45:02.953 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 43 2013-01-27 02:17:01.553 4467 38 2013-01-27 02:45:26.490 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 47 2013-01-27 02:17:01.553 4467 38 2013-01-27 02:47:34.657 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 49 2013-01-27 02:17:01.553 4 52 2013-01-27 02:47:39.107 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 50 2013-01-27 02:17:01.553 4 53 2013-01-27 02:47:39.310 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 51 2013-01-27 02:17:01.553 4467 6 2013-01-27 02:47:43.507 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 52 2013-01-27 02:17:01.553 4467 36 2013-01-27 02:47:43.507 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 53 2013-01-27 02:17:01.553 5095 5 2013-01-27 02:56:00.737 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 56 2013-01-27 02:17:01.553 5095 26 2013-01-27 02:57:04.923 1 NULL NULL 760 2013-01-27 02:17:01.553 NULL NULL
43547654 57 2013-01-27 02:17:01.553 5095 27 2013-01-27 02:57:04.923 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 58 2013-01-27 02:17:01.553 5095 6 2013-01-27 02:57:08.060 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
43547654 59 2013-01-27 02:17:01.553 5095 36 2013-01-27 02:57:08.060 1 NULL NULL NULL 2013-01-27 02:17:01.553 NULL NULL
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 33
User 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 332
User 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.
Hope that all makes sense.
|
|
|
|
Topic |
|
|
|
Cursor issues
