Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!
Course Of The Month
6 days, 7 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL 2005 - Getting minimum using GROUP BY
Posted on 2010-09-01
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.
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
4 Comments
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
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
INTRODUCTION:
While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface.
Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Suggested Courses
Course of the Month6 days, 7 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
726 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.