Query Help - 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

Author

Topic

Jay87
Starting Member

41 Posts

Posted - 2010-03-01 : 09:54:56

The following query shows all the logs today that have meet there SLA (a note has to be added before the agreed 'response' time) i.e. Company x have a SLA of 2 hours and we must respond and add a note to that call before 2 hours to meet that sla...

SELECT * FROM faultlog as c 
INNER JOIN contact ON c.contid=contact.id 
WHERE logged>=dateadd(day,datediff(day,0,getdate()),0) AND 
logged<dateadd(day,datediff(day,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))

basically i want to adjust this query so that is shows all the calls that have no notes and is 15 mins before the 'response' is about to expire! But i am stuck on how to do this...

basically this will act as a warning to us that we are about to miss a SLA (will change colour)

any help would be great?

Hope that makes sense

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-01 : 10:26:51

which is field containing notes info?

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

Jay87
Starting Member

41 Posts

Posted - 2010-03-01 : 10:46:55

[code]SELECT * FROM faultlog as c
INNER JOIN contact ON c.contid=contact.id
WHERE logged>=dateadd(day,datediff(day,0,getdate()),0) AND
logged<dateadd(day,datediff(day,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))[/code]

the bit in red is where it checks the top note (this is when the initial call is logged) so if the 'response' is 2 then a another 'note' has to be added before the 2 hours to meet the SLA....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-01 : 10:53:31

seems like this


SELECT * FROM faultlog as c 
INNER JOIN contact ON c.contid=contact.id 
OUTER APPLY (SELECT TOP 1 date FROM notes WHERE id=c.id AND id_prefix=c.id_prefix ORDER BY date ASC)c1
WHERE logged>=dateadd(day,datediff(day,0,getdate()),0) AND logged<dateadd(day,datediff(day,0,getdate())+1,0) 
AND (response>datediff(hour,logged,c1.date) OR c1.date IS NULL)

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

Jay87
Starting Member

41 Posts

Posted - 2010-03-01 : 10:56:50

Incorrect syntax near 'NULL'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-01 : 11:02:45

I dont think error is in posted query as i'm not getting any syntax error. seems like you're not using it in way given.

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

Jay87
Starting Member

41 Posts

Posted - 2010-03-01 : 11:06:00

how can i be using it wrong? I just copied and pasted it!
am i being thick like normal? lol

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-01 : 11:09:21

i feel you might have missed a bracket

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

Jay87
Starting Member

41 Posts

Posted - 2010-03-01 : 11:20:49

you're right....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-01 : 11:28:29

does that run fine now?

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

Jay87
Starting Member

41 Posts

Posted - 2010-03-01 : 11:34:40

yes, not doing what i want though...

i want it to select all the calls that the TOP NOTE (the note that is first created when a call is created) and that have no other notes (as if there is more than 1 note that means we have responded)... and are 15 mins away from the 'response' time...

If that makes sense?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-01 : 11:44:09

[code]SELECT * FROM faultlog as c
INNER JOIN contact ON c.contid=contact.id
INNER JOIN (SELECT id,id_prefix,MIN(date) AS first FROM notes GROUP BY id,id_prefix HAVING MIN(date)=MAX(date))c1
ON c1.id=c.id AND c1.id_prefix=c.id_prefix
WHERE logged>=dateadd(day,datediff(day,0,getdate()),0) AND logged<dateadd(day,datediff(day,0,getdate())+1,0)
AND response>datediff(hour,15,c1.first)
[/code]

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

Jay87
Starting Member

41 Posts

Posted - 2010-03-02 : 04:44:09

The query runs fine mate...

But when i open a new call that has a 'response' of 1 then change the 15 bit to 55 and leave it for 10 mins so it should select that call... Nothing shows up

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 08:43:58

quote:
Originally posted by Jay87

The query runs fine mate...

But when i open a new call that has a 'response' of 1 then change the 15 bit to 55 and leave it for 10 mins so it should select that call... Nothing shows up

do you mean this?


SELECT * FROM faultlog as c 
INNER JOIN contact ON c.contid=contact.id 
INNER JOIN (SELECT id,id_prefix,MIN(date) AS first FROM notes  GROUP BY id,id_prefix HAVING MIN(date)=MAX(date))c1
ON c1.id=c.id AND c1.id_prefix=c.id_prefix
WHERE logged>=dateadd(day,datediff(day,0,getdate()),0) AND logged<dateadd(day,datediff(day,0,getdate())+1,0) 
AND response>dateadd(minute,-55,c1.first)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources