Not sure if this is the right place to post a question, apologies if so.
My query is as follows:
I have a call tracking database with two smalldatetime fields. I would like to run some reports on this database (via SQL 2005 Reporting Services) using MS SQL 2005 / Query Analyser.
What I'm trying to extract is all the calls that haven't met a specific service level agreement (eg: all calls with a time difference of more than 4 hours between the two smalldatetime fields).
The logic holds true from Monday to Sunday, so no need to exclude weekends, etc.
Is there a simple query to achieve this?
Detailed examples of what I need is shown below (Business hours 8:30 AM - 6:00 PM):
what you need is the DATEDIFF() function that gives the difference between two dates. Use the DATEDIFF() funciton with 'minute' flag that gives you the difference between two dates is minutes. See the examples given here: http://syntaxhelp.com/SQLServer/DATEDIFF
Posts
Congradulations all the winners and thank you Jacob to introduce us good challenges.
ReplyDelete--Ritesh Shah
Not sure if this is the right place to post a question, apologies if so.
ReplyDeleteMy query is as follows:
I have a call tracking database with two smalldatetime fields. I would like to run some reports on this database (via SQL 2005 Reporting Services) using MS SQL 2005 / Query Analyser.
What I'm trying to extract is all the calls that haven't met a specific service level agreement (eg: all calls with a time difference of more than 4 hours between the two smalldatetime fields).
The logic holds true from Monday to Sunday, so no need to exclude weekends, etc.
Is there a simple query to achieve this?
Detailed examples of what I need is shown below (Business hours 8:30 AM - 6:00 PM):
Example1:
smalldatetime1 = 16/11/2009 9:00:00
smalldatetime2 = 17/11/2009 14:00:00
Result (business hours) = 14 hours and 30 mins
Example2:
smalldatetime1 = 16/11/2009 9:00:00
smalldatetime2 = 16/11/2009 12:00:00
Result (business hours) = 3 hours
Any help would be much appreciated.
Thanks heaps,
Sujit.
what you need is the DATEDIFF() function that gives the difference between two dates. Use the DATEDIFF() funciton with 'minute' flag that gives you the difference between two dates is minutes.
ReplyDeleteSee the examples given here: http://syntaxhelp.com/SQLServer/DATEDIFF