Query Help (again!) - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Query Help (again!)

Author

Topic

Jay87
Starting Member

41 Posts

Posted - 2010-03-03 : 05:33:00

The following code counts how many SLA (time we have to respond to a call) met this week but the query is slow to run....

SELECT COUNT(c.id) as slaweek FROM faultlog as c INNER JOIN contact ON c.contid=contact.id WHERE logged>=dateadd(week,datediff(week,0,getdate()),0) AND logged<dateadd(week,datediff(week,0,getdate())+1,0) AND response>datediff(hour,logged,(SELECT TOP 1 date FROM notes WHERE id=c.id AND id_prefix=c.id_prefix ORDER BY date ASC))

The following code is faster and shows all calls that have 15 mins left before the SLA runs out (a warning to prompt us):

Select logged,(faultlog.id_prefix+faultlog.id) as jobid,response from faultlog left outer join notes ON faultlog.id=notes.id AND faultlog.id_prefix=notes.id_prefix INNER JOIN contact ON contact.id=faultlog.contid WHERE notes.date IS NULL AND faultlog.status<1 AND faultlog.description != '' AND response>0 AND DATEDIFF(Minute,DATEADD(Hour,response,logged),GetDate())>-15

Is it possible to modify this code so it does what the first code does (counts sla's met this week)....

help plz

Jay87
Starting Member

41 Posts

Posted - 2010-03-03 : 06:29:41

If that doesn't make sense, just ask and i will try and clarify what i mean.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-03 : 07:15:42

quote:
just ask and i will try and clarify what i mean

Sorry but no! That's like ping pong...

Give table structure, example data and wanted output and we can help a bit faster.

No, you're never too old to Yak'n'Roll if you're too young to die.

Jay87
Starting Member

41 Posts

Posted - 2010-03-03 : 07:56:18

Ok, i'll try and be clear but its quite complicated and hard to explain....

There is a faultlog table with:

id (varchar4) i.e. 1 , 2, 3
id_prefix (varchar 5) i.e. 2009, 2010 (the year it was logged!)
logged (datetime) i.e. when the call was logged
Company (varchar254) i.e. company name
enduser (varcha255) i.e. the name of the user from the company
description (varchar MAX) i.e. description of the problem
priority (int) i.e. 1= high, 2 = routine
status (int) i.e. 1= logged, 2 = awaiting customer, 3= closed
closed (datetime) i.e. when the call was closed

notes table:

id (varchar4) i.e. 1 , 2, 3
id_prefix (varchar 5) i.e. 2009, 2010 (the year it was logged!)
date (datetime) i.e. date of the note added
description (varchar MAX) i.e. the note added (this would be an update to the problem logged)

Interestingly i am note sure where the 'response' field is in the tables used in the above query.... but i am sure it is just a number i.e. 1 (this is how long in hours we have to add our first note) to meet the SLA

Basically what i want to to is to create a stats page to show a % of SLA Met / Missed over certain periods of time (i.e. today, this week, this month, last month. this year, last year) the original query i posted works but for the last year stat takes about 45 secs to load at which point the stat page times out!

The query needs to compare the time of the top note to the 'response' to count how many sla have been met /missed.

This is how it was origanally done it but is painfully slow when looking at this years and last years data:

 SELECT COUNT(c.id) as slaweek FROM faultlog as c INNER JOIN contact ON c.contid=contact.id WHERE logged>=dateadd(week,datediff(week,0,getdate()),0) AND logged<dateadd(week,datediff(week,0,getdate())+1,0) AND response>datediff(hour,logged,(SELECT TOP 1 date FROM notes WHERE id=c.id AND id_prefix=c.id_prefix ORDER BY date ASC))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources