Solved

SQL 2005 - Getting minimum using GROUP BY

Posted on 2010-09-01
4
523 Views
Last Modified: 2012-05-10
My favorite friend - the activity log.

When a ticket is transferred to a group I need to know how long before it was acknowledged.  These are stored in the activity log.  The transfer is type 'TR' and the acknowledge is type 'ST'.  This can happen multiple times on a ticket, so I only care about the first acknowledgement after the transfer.

I can get the list of tickets and all the acknowledgements that occurred after the transfer, but I only want the first one.  I thought I could do a GROUP BY and then a HAVING where row number = MIN(row_number), but that doesn't work.  I know this is a simple solution but I've been staring at it too long and my deadline in upon me.

So, in the sample, I should only have one result for 'cr:1044861103', which would be rn 1.  This will not always be the minimum.  The example of 'cr:1044893316' shows that the 4th occurrence of acknowledging was the only one that occurred _after_ the transfer.


WITH act_log_ack as 

(

SELECT

	call_req_id,

	analyst,

	time_stamp,

	CAST(action_desc AS nvarchar(max)) 'action_desc',

	type,

	row_number() over 

	( partition BY call_req_id

		ORDER BY last_mod_dt ) rn

FROM

	act_log 

WHERE

	DATEADD(ss, time_stamp + -14400, '01-JAN-1970') >= '01/01/2010 12:00 AM'

	AND DATEADD(ss, time_stamp + -14400, '01-JAN-1970') <= '07/31/2010 11:59 PM'

	AND type = 'ST'

	AND action_desc LIKE 'Status changed from ''Open'' %'

),



act_log_tran AS



(

SELECT

	call_req_id,

	analyst,

	time_stamp,

	CAST(action_desc AS nvarchar(max)) 'action_desc',

	type,

	row_number() over 

	( partition BY call_req_id

		ORDER BY last_mod_dt ) rn

FROM

	act_log

WHERE

	DATEADD(ss, time_stamp + -14400, '01-JAN-1970') >= '01/01/2010 12:00 AM'

	AND DATEADD(ss, time_stamp + -14400, '01-JAN-1970') <= '07/31/2010 11:59 PM'

	AND type = 'TR'

	AND action_desc LIKE '% to ''Tier 2''%'

)



SELECT

	A.call_req_id,

	A.time_stamp,

	A.rn

FROM

	act_log_ack A

		INNER JOIN act_log_tran T

			ON A.call_req_id = T.call_req_id

		LEFT JOIN ca_contact ANL

			ON A.analyst = ANL.contact_uuid

WHERE 

	T.rn = 1

	AND A.time_stamp > T.time_stamp

	AND A.analyst IN

	(

	SELECT 

		MEM.contact_uuid

	FROM

		grpmem 

		INNER JOIN ca_contact GRP

			ON grpmem.group_id = GRP.contact_uuid

		INNER JOIN ca_contact MEM

			ON grpmem.member = MEM.contact_uuid

	WHERE

		GRP.last_name = 'Tier 2'

	)

GROUP BY

	A.call_req_id,

	A.time_stamp,

	A.rn

ORDER BY

	1



RESULTS extract



call_req_id	time_stamp	rn

cr:1041066083	1269611394	1

cr:1044845740	1263916396	2

cr:1044846255	1263914579	1

cr:1044861103	1264012249	1 <-

cr:1044861103	1264021382	2 <-

cr:1044861478	1264012015	1

cr:1044862323	1264011596	1

cr:1044862611	1264010784	1

cr:1044862692	1264010537	1

cr:1044862801	1264010246	1

cr:1044863089	1264009815	2

cr:1044863243	1264009049	1

cr:1044863500	1264008896	1

cr:1044893316	1272025369	4 <-

cr:1044905914	1266506905	3

cr:1044914094	1263913411	2

Open in new window

0
Comment
Question by:shadowbreeze
  • 2
  • 2
4 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 33579341
Try this code.  It changes your SELECT into another WITH statement, and uses it to filter itself.
WITH act_log_ack as 

(

SELECT

	call_req_id,

	analyst,

	time_stamp,

	CAST(action_desc AS nvarchar(max)) 'action_desc',

	type,

	row_number() over 

	( partition BY call_req_id

		ORDER BY last_mod_dt ) rn

FROM

	act_log 

WHERE

	DATEADD(ss, time_stamp + -14400, '01-JAN-1970') >= '01/01/2010 12:00 AM'

	AND DATEADD(ss, time_stamp + -14400, '01-JAN-1970') <= '07/31/2010 11:59 PM'

	AND type = 'ST'

	AND action_desc LIKE 'Status changed from ''Open'' %'

),



act_log_tran AS



(

SELECT

	call_req_id,

	analyst,

	time_stamp,

	CAST(action_desc AS nvarchar(max)) 'action_desc',

	type,

	row_number() over 

	( partition BY call_req_id

		ORDER BY last_mod_dt ) rn

FROM

	act_log

WHERE

	DATEADD(ss, time_stamp + -14400, '01-JAN-1970') >= '01/01/2010 12:00 AM'

	AND DATEADD(ss, time_stamp + -14400, '01-JAN-1970') <= '07/31/2010 11:59 PM'

	AND type = 'TR'

	AND action_desc LIKE '% to ''Tier 2''%'

),



act_log_result AS (



SELECT

	A.call_req_id,

	A.time_stamp,

	A.rn

FROM

	act_log_ack A

		INNER JOIN act_log_tran T

			ON A.call_req_id = T.call_req_id

		LEFT JOIN ca_contact ANL

			ON A.analyst = ANL.contact_uuid

WHERE 

	T.rn = 1

	AND A.time_stamp > T.time_stamp

	AND A.analyst IN

	(

	SELECT 

		MEM.contact_uuid

	FROM

		grpmem 

		INNER JOIN ca_contact GRP

			ON grpmem.group_id = GRP.contact_uuid

		INNER JOIN ca_contact MEM

			ON grpmem.member = MEM.contact_uuid

	WHERE

		GRP.last_name = 'Tier 2'

	)

GROUP BY

	A.call_req_id,

	A.time_stamp,

	A.rn

ORDER BY

	1

)



SELECT 

	A.call_req_id,

	A.time_stamp,

	A.rn

FROM act_log_result A

INNER JOIN (

	SELECT call_req_id, 

		MIN(time_stamp) min_ts

	FROM act_log_result

	GROUP BY call_req_id) Filt

	ON A.call_req_id = Filt.call_req_id

Open in new window

0
 

Author Comment

by:shadowbreeze
ID: 33579701
No joy.  But what you suggested makes sense.  I know it's a really obvious set grouping, but my brain is mush.


call_req_id        time_stamp      rn  

cr:1041066083	1269611394	1

cr:1044845740	1263916396	2

cr:1044846255	1263914579	1

cr:1044861103	1264021382	2 <-

cr:1044861103	1264012249	1 <-

cr:1044861478	1264012015	1

cr:1044862323	1264011596	1

cr:1044862611	1264010784	1

cr:1044862692	1264010537	1

cr:1044862801	1264010246	1

cr:1044863089	1264009815	2

cr:1044863243	1264009049	1

cr:1044863500	1264008896	1

cr:1044893316	1272025369	4

cr:1044905914	1266506905	3

Open in new window

0
 
LVL 32

Accepted Solution

by:
bhess1 earned 500 total points
ID: 33580144
AAAH!  I missed a JOIN clause item.  Add the line below at the bottom of the query.  It should be:

      ON A.call_req_id = Filt.call_req_id
      AND a.time_stamp = min_ts      ---<<<--- Add this

0
 

Author Closing Comment

by:shadowbreeze
ID: 33580299
Works perfectly.  Excellent.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now