Click here to Skip to main content
15,921,716 members
Home / Discussions / Database
   

Database

 
AnswerRe: Check if email for each specific recipient was sent or not sql server 2008 Pin
Wendelius19-Oct-14 0:41
mentorWendelius19-Oct-14 0:41 
GeneralRe: Check if email for each specific recipient was sent or not sql server 2008 Pin
bjay tiamsic19-Oct-14 17:58
bjay tiamsic19-Oct-14 17:58 
GeneralRe: Check if email for each specific recipient was sent or not sql server 2008 Pin
Wendelius19-Oct-14 18:20
mentorWendelius19-Oct-14 18:20 
SuggestionRe: Check if email for each specific recipient was sent or not sql server 2008 Pin
Richard Deeming20-Oct-14 2:16
mveRichard Deeming20-Oct-14 2:16 
GeneralRe: Check if email for each specific recipient was sent or not sql server 2008 Pin
Wendelius20-Oct-14 17:23
mentorWendelius20-Oct-14 17:23 
QuestionSQL report to get average amount of time for helpdesk closures Pin
Danpeking17-Oct-14 3:29
Danpeking17-Oct-14 3:29 
AnswerRe: SQL report to get average amount of time for helpdesk closures Pin
GuyThiebaut17-Oct-14 3:45
professionalGuyThiebaut17-Oct-14 3:45 
AnswerRe: SQL report to get average amount of time for helpdesk closures Pin
Wendelius17-Oct-14 3:47
mentorWendelius17-Oct-14 3:47 
This can be done in various ways but one way could be to use a small scalar query in the select statement. Something like:
SQL
SELECT User AS OpenedBy,
       AuditDatetime AS StartTime,
       DATEDIFF(hh, AuditDatetime, ( SELECT MIN(AuditDatetime)
                                     FROM TheTable tt2
                                     WHERE tt2.AuditDatetime > tt.AuditDatetime)
               )AS DurationInHours
FROM TheTable tt
WHERE tt.Task = 'Opens job'

Using a query like the above you can get the open/close pair data and you can use that as an inline view. So something like

SQL
SELECT AVG(DurationInHours)
FROM (SELECT User AS OpenedBy,
         AuditDatetime AS StartTime,
         DATEDIFF(hh, AuditDatetime, ( SELECT MIN(AuditDatetime)
                                     FROM TheTable tt2
                                     WHERE tt2.AuditDatetime > tt.AuditDatetime)
               )AS DurationInHours
      FROM TheTable tt
      WHERE tt.Task = 'Opens job') iv


Probably I made a ton of typos but hope you get the idea Smile | :)

[EDIT]
Just noticed that GuyThiebaut wrote almost same kind of answer at the same time. If the value of TaskId can be used to connect the open/close operations then his approach is better.

The reason I used the date field is that in your example data the same TaskId was opened twice so my interpretation was that the TaskId isn't unique among tasks:


User TaskID Task AuditDatetime
Bob 2 Opens job 1/8/2014 11:14:29
Bob 5 Closes job 2/8/2014 10:12:26
Joe 2 Opens job 2/8/2014 11:01:45
Bob 5 Closes job 3/8/2014 11:44:22

GeneralRe: SQL report to get average amount of time for helpdesk closures Pin
GuyThiebaut17-Oct-14 4:16
professionalGuyThiebaut17-Oct-14 4:16 
GeneralRe: SQL report to get average amount of time for helpdesk closures Pin
Wendelius17-Oct-14 4:25
mentorWendelius17-Oct-14 4:25 
GeneralRe: SQL report to get average amount of time for helpdesk closures Pin
Danpeking17-Oct-14 6:03
Danpeking17-Oct-14 6:03 
AnswerRe: SQL report to get average amount of time for helpdesk closures Pin
Wendelius19-Oct-14 0:35
mentorWendelius19-Oct-14 0:35 
GeneralRe: SQL report to get average amount of time for helpdesk closures Pin
Danpeking9-Nov-14 22:27
Danpeking9-Nov-14 22:27 
GeneralRe: SQL report to get average amount of time for helpdesk closures Pin
Wendelius10-Nov-14 8:33
mentorWendelius10-Nov-14 8:33 
QuestionRoadmap to learn SSIS Pin
arunUB16-Oct-14 15:47
arunUB16-Oct-14 15:47 
AnswerRe: Roadmap to learn SSIS Pin
Peter Leow16-Oct-14 16:05
professionalPeter Leow16-Oct-14 16:05 
AnswerRe: Roadmap to learn SSIS Pin
Shweta N Mishra20-Oct-14 2:31
professionalShweta N Mishra20-Oct-14 2:31 
QuestionCreate 2 sets of results using old school foxpro or dbase III dbf files Pin
jkirkerx16-Oct-14 10:49
professionaljkirkerx16-Oct-14 10:49 
AnswerRe: Create 2 sets of results using old school foxpro or dbase III dbf files Pin
David Mujica17-Oct-14 2:54
David Mujica17-Oct-14 2:54 
Questiongood book to clear the concept of Sql,Dynamic Procedures and User defined Functions Pin
johnpol1215-Oct-14 0:04
johnpol1215-Oct-14 0:04 
AnswerRe: good book to clear the concept of Sql,Dynamic Procedures and User defined Functions Pin
ZurdoDev15-Oct-14 5:45
professionalZurdoDev15-Oct-14 5:45 
QuestionConvert row to columns in SQL View or Procedure Pin
Member 1115418814-Oct-14 19:52
Member 1115418814-Oct-14 19:52 
AnswerRe: Convert row to columns in SQL View or Procedure Pin
Mycroft Holmes14-Oct-14 21:09
professionalMycroft Holmes14-Oct-14 21:09 
QuestionJoin a 3rd table, in which multiple records with the same id exist, but I just need 1 of them Pin
jkirkerx14-Oct-14 9:14
professionaljkirkerx14-Oct-14 9:14 
GeneralRe: Join a 3rd table, in which multiple records with the same id exist, but I just need 1 of them Pin
Kornfeld Eliyahu Peter14-Oct-14 9:23
professionalKornfeld Eliyahu Peter14-Oct-14 9:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.