SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cursor issues
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 01/28/2013 :  05:39:34  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/28/2013 :  05:44:04  Show Profile  Reply with Quote
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
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 01/28/2013 :  05:58:47  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/28/2013 :  06:05:59  Show Profile  Reply with Quote
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/

Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 01/28/2013 :  06:26:34  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/28/2013 :  06:33:35  Show Profile  Reply with Quote
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/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 01/28/2013 :  06:49:23  Show Profile  Reply with Quote
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
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 01/28/2013 :  06:57:32  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 01/28/2013 :  07:20:08  Show Profile  Reply with Quote
Are you filtering result by passing specific Callid?



--
Chandu
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 01/28/2013 :  07:35:31  Show Profile  Reply with Quote
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
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 01/28/2013 :  07:39:09  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 01/28/2013 :  07:52:30  Show Profile  Reply with Quote
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
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 01/28/2013 :  08:56:06  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 01/28/2013 :  08:56:43  Show Profile  Reply with Quote
Welcome

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/28/2013 :  08:58:50  Show Profile  Reply with Quote
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/

Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 01/30/2013 :  06:39:03  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/30/2013 :  06:50:44  Show Profile  Reply with Quote
whats your rule for selecting a row? is it latest row with valid value for DispositionId?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 01/30/2013 :  07:43:27  Show Profile  Reply with Quote
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)
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 01/30/2013 :  08:45:43  Show Profile  Reply with Quote
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?
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 01/30/2013 :  10:06:51  Show Profile  Reply with Quote
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.

Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000