Retrieving values from table for calculation

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

General SQL Server Forums

New to SQL Server Programming

Retrieving values from table for calculation

Author

Topic

ppig
Starting Member

4 Posts

Posted - 2010-08-05 : 05:51:04

Task:

My database includes all details related to Customer Support Calls.
For each Support Call, all activities related to that call are stored in an Activity table,
e.g. assignments, changes of status (New, Assigned, Investigation, Resolved...), etc.
For Support Calls with associated Service Level Agreements, the Activity table also includes Pending information.
For instance, when a Support Call changes status to Pending, the SLA clock should stop until such times as the Call is Closed or the Pending status is removed.
Sometimes a Support Call may enter and exit Pending Status on more than one ocasion.
My task is to determine the total time that an SLA-related ticket is in a non-Pending status.

I assume that this will involve retrieving (using a FETCH cursor?), from the Activity table, the time(s) when a Support Call enters and exits Pending status.
These times would be stored in a 2-column temporary table for later processing. This processing would involve:
- subtracting each Enter Pending Status time from each Exit Pending Status time
- adding these Pending Status time values to give a Total Pending time
- subtracting this Total Pending time from the total lifetime of the ticket to give an overall Non-Pending time.

Example Activity table (all dates are in dd/mm/yyyy format):

Call ID - Activity - Date

SC001 - New - 01/01/2010 10:00:00
SC001 - Assigned - 01/01/2010 12:00:00
SC001 - Investigation - 01/01/2010 12:01:00
SC001 - Enter Pending - 02/01/2010 10:00:00
SC001 - Exit Pending - 02/01/2010 14:00:00
SC001 - New Assignee - 03/01/2010 09:00:00
SC001 - Enter Pending - 06/01/2010 09:00:00
SC001 - Exit Pending - 06/01/2010 15:00:00
SC001 - Enter Pending - 09/01/2010 10:00:00
SC001 - Exit Pending - 09/01/2010 12:00:00
SC001 - Resolution - 09/01/2010 16:00:00
SC001 - Closed - 10/01/2010 14:00:00

Here the total time in Pending status is 4 + 6 + 2 = 12 hours
Total lifetime of the ticket = (9days x 24) + 4 = 220 hours
Total non-Pending time = 220 - 12 = 208 hours

As a relative SQL newbie I need help...! Any suggestions?

Sachin.Nand

2937 Posts

Posted - 2010-08-05 : 06:03:15

I cannot understand this part

Total lifetime of the ticket = (9days x 24) + 4 = 220 hours

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

ppig
Starting Member

4 Posts

Posted - 2010-08-05 : 08:05:14

Hi,

Sorry, small calculation error there...

The total lifetime of the Support Call is 9 days and 6 hours (from 10:00 on 01/01/2010 to 14:00 on 10/01/2010. Therefore the lifetime in hours is (9 * 24) + 6 = 222 hours.

ewomack
Starting Member

33 Posts

Posted - 2010-08-05 : 08:45:40

I'm not sure I understand what data you actually have - do you have the actual dates/times that a ticket enters and exits pending status and the dates/times it exits? How is this stored? If you do have all of that information then you probably don't need a cursor, a simple select statement would fetch the times you need (you may have to do some calculations on the data). But I'm not sure what data you do have, so it's hard to know if this would work.

Ed Womack
www.getmilked.com

Sachin.Nand

2937 Posts

Posted - 2010-08-05 : 08:50:02

[code]
SET dateformat dmy

DECLARE @tbl AS TABLE(
callid VARCHAR(20),
activity VARCHAR(20),
DATE DATETIME
)

INSERT INTO @tbl
SELECT 'SC001',
'New',
'01/01/2010 10:00:00'
UNION ALL
SELECT 'SC001',
'Assigned',
'01/01/2010 12:00:00'
UNION ALL
SELECT 'SC001',
'Investigation',
'01/01/2010 12:01:00'
UNION ALL
SELECT 'SC001',
'Enter Pending',
'02/01/2010 10:00:00'
UNION ALL
SELECT 'SC001',
'Exit Pending',
'02/01/2010 14:00:00'
UNION ALL
SELECT 'SC001',
'New Assignee',
'03/01/2010 09:00:00'
UNION ALL
SELECT 'SC001',
'Enter Pending',
'06/01/2010 09:00:00'
UNION ALL
SELECT 'SC001',
'Exit Pending',
'06/01/2010 15:00:00'
UNION ALL
SELECT 'SC001',
'Enter Pending',
'09/01/2010 10:00:00'
UNION ALL
SELECT 'SC001',
'Exit Pending',
'09/01/2010 12:00:00'
UNION ALL
SELECT 'SC001',
'Resolution',
'09/01/2010 16:00:00'
UNION ALL
SELECT 'SC001',
'Closed',
'10/01/2010 14:00:00'

SELECT *
FROM @tbl

SELECT *,
totallifetime - totaltime AS totalnonpendingtime
FROM (SELECT Sum(Datediff(hh,t1.DATE,t.DATE)) AS totaltime,
Sum(Datediff(hh,t2.DATE,t1.DATE)) AS totallifetime
FROM @tbl t1
OUTER APPLY (SELECT TOP 1 *
FROM @tbl t2
WHERE t1.activity = 'Enter Pending'
AND t1.DATE < t2.DATE
AND t1.callid = t2.callid) t
CROSS APPLY (SELECT TOP 1 *
FROM @tbl t3
WHERE t1.callid = t3.callid
AND t1.DATE > t3.DATE
ORDER BY DATE DESC) t2) t
[/code]
Also your initial requiremtent was write.The total life time comes to 220.

[code]
SELECT DATEDIFF(hh,'2010-01-01 10:00:00.000','2010-01-10 14:00:00.000')

It comes to 220 hours
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-08-05 : 11:58:43

Read the part on transition constraints at the article below.. There is no need for cursors or any procedural code. Also, please start using ISO-8601 date formats. Dialect is a bitch to read, to port and it is going away as Microsoft gets closer to Standards.

http://www.simple-talk.com/sql/t-sql-programming/constraint-yourself!/

--CELKO--
Joe Celko, SQL Guru

ppig
Starting Member

4 Posts

Posted - 2010-08-06 : 11:56:02

Idera, many thanks for your response. I think I can definitely use your code with some modifications (and once I read up on OUTER APPLY and CROSS APPLY operators to better understand how they work here).

jcelko, thank you also for your recommendations and suggestions.

ppig
Starting Member

4 Posts

Posted - 2010-08-11 : 12:28:26

Idera, an issue has come to light since I modified and tested your code.
In some cases the Support Call is still open and in a Pending state, therefore no final Exit Pending record exists. In such cases, the current time should be used to determine the length (in time) of the latest Pending state, i.e Current Time - last Enter pending time.
I've not been able to determine a satisfactory method of resolving this problem. Any help would be greatly appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-11 : 12:52:50

for that just change CROSS to OUTER APPLY and in select use ISNULL to check for NULL and use GETDATE() in that case

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources