Cycling Through Logically Grouped Records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cycling Through Logically Grouped Records

Author

Topic

qdaddyo
Starting Member

7 Posts

Posted - 2008-07-07 : 16:27:48

OK, here's some background. The database I'm receiving an extract from is an in-house call-tracking system. Each call that comes in is assigned a ticket number (TICKET_NUMBER). Each ticket, though, may have several iterations (CALL_LEVEL) based on the number of updates a representative places on the ticket. Each iteration is represented by a different row. Any field that is updated on a call iteration is carried over to the next iteration until the ticket is closed. For example, we have ticket 12345 with 4 iterations:

TICKET_NUMBER, CALL_LEVEL
12345, 1
12345, 2
12345, 3
12345, 4

Often, calls are assigned out to different groups to help with the resolution. These groups are listed in the ASSIGNEE column. I use the MODIFIED_DATE as the date the ticket was assigned to the assignee. The RETURN_DATE column stores the date when the issue is returned from the assignee to the representative.

Below is an example:

ID, TICKET_NUMBER, CALL_LEVEL, MODIFIED_DATE, STATUS, ASSIGNEE, RETURN_DATE
100, 12345, 1, 2008-06-27, OPEN, NONE, NULL
101, 12345, 2, 2008-06-29, PENDING, NONE, NULL
102, 12345, 3, 2008-06-30, ASSIGNED, GROUP_A, NULL
103, 12345, 4, 2008-07-01, ASSIGNED, GROUP_A, NULL
104, 12345, 5, 2008-07-03, CLOSED, GROUP_A, 2008-07-03
200, 67890, 1, 2008-06-18, OPEN, NONE, NULL
201, 67890, 2, 2008-06-18, PENDING, GROUP_Z, NULL
202, 67890, 3, 2008-06-19, PENDING, GROUP_Z, NULL
203, 67890, 4, 2008-06-20, PENDING, GROUP_Z, NULL
204, 67890, 5, 2008-06-25, PENDING, GROUP_Z, 2008-06-24
205, 67890, 6, 2008-06-26, CLOSED, GROUP_Z, 2008-06-24

My ultimate goal is to find the difference between the RETURN_DATE and the MODIFIED_DATE. I can get the RETURN_DATE from the final iteration of the call but am having trouble pulling the appropriate MODIFIED_DATE.

What type of query do I use to grab the MODIFIED_DATE from the first instance where a value in the ASSIGNEE column does not equal NONE (in these cases, CALL_LEVELs 3 and 2)? Please note that the STATUS has nothing to do with query.

Thanks for the help!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-07 : 20:02:15

[code]DECLARE @TABLE TABLE
(
ID int,
TICKET_NUMBER int,
CALL_LEVEL int,
MODIFIED_DATE datetime,
STATUS varchar(10),
ASSIGNEE varchar(10),
RETURN_DATE datetime
)
INSERT INTO @TABLE
SELECT 100, 12345, 1, '2008-06-27', 'OPEN', 'NONE', NULL UNION ALL
SELECT 101, 12345, 2, '2008-06-29', 'PENDING', 'NONE', NULL UNION ALL
SELECT 102, 12345, 3, '2008-06-30', 'ASSIGNED', 'GROUP_A', NULL UNION ALL
SELECT 103, 12345, 4, '2008-07-01', 'ASSIGNED', 'GROUP_A', NULL UNION ALL
SELECT 104, 12345, 5, '2008-07-03', 'CLOSED', 'GROUP_A', '2008-07-03' UNION ALL
SELECT 200, 67890, 1, '2008-06-18', 'OPEN', 'NONE', NULL UNION ALL
SELECT 201, 67890, 2, '2008-06-18', 'PENDING', 'GROUP_Z', NULL UNION ALL
SELECT 202, 67890, 3, '2008-06-19', 'PENDING', 'GROUP_Z', NULL UNION ALL
SELECT 203, 67890, 4, '2008-06-20', 'PENDING', 'GROUP_Z', NULL UNION ALL
SELECT 204, 67890, 5, '2008-06-25', 'PENDING', 'GROUP_Z', '2008-06-24' UNION ALL
SELECT 205, 67890, 6, '2008-06-26', 'CLOSED', 'GROUP_Z', '2008-06-24'

SELECT m.TICKET_NUMBER, m.MODIFIED_DATE, r.RETURN_DATE
FROM (
SELECT TICKET_NUMBER, MODIFIED_DATE = MAX(MODIFIED_DATE)
FROM @TABLE m
WHERE ASSIGNEE <> 'NONE'
GROUP BY TICKET_NUMBER

) m
INNER JOIN
(
SELECT TICKET_NUMBER, RETURN_DATE = MAX(RETURN_DATE)
FROM @TABLE m
WHERE RETURN_DATE IS NOT NULL
GROUP BY TICKET_NUMBER
) r ON m.TICKET_NUMBER = r.TICKET_NUMBER

/*
TICKET_NUMBER MODIFIED_DATE RETURN_DATE
------------- -------------- -----------
12345 2008-07-03 2008-07-03
67890 2008-06-26 2008-06-24

(2 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources