Database

SELECT mailitem_id,
       recipients,
       subject,
       sent_status
FROM sysmail_allitems

SELECT ...
FROM sysmail_allitems sai,
     student s
WHERE LOWER(sai.recipients) = LOWER(s.email)

SELECT ...
FROM sysmail_allitems sai,
     student s
WHERE CHARINDEX(LOWER(s.email), LOWER(sai.recipients)) > 0 

SELECT 
    ...
FROM 
    sysmail_allitems sai
    INNER JOIN student s
    ON sai.recipients = s.email

Richard Deeming wrote:

It's usually cleaner to use the ANSI JOIN syntax

Richard Deeming wrote:

most databases don't use a case-sensitive collation, so there shouldn't be any need to use the LOWER function here.

select avg(diff)
from
(
select datediff(d,h1.auditdatetime,h2.auditdatetime) diff
from helpdesk h1
join helpdesk h2
on h1.taskid = h2.taskid
and h2.task = 'closes job'
where h1.task = 'opens job'
)x

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'

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

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

SELECT iv.JobId,
       iv.OpenedBy,
       iv.ClosedBy,
       iv.OpenTime,
       iv.CloseTime,
       DATEDIFF(hh, iv.OpenTime, iv.CloseTime) AS DurationInHours
FROM (
   SELECT DISTINCT
          tt.JobId AS JobId,
          tt_open.User AS OpenedBy,
          tt_close.User AS ClosedBy,
          MIN(tt_open.AuditDatetime) OVER (PARTITION BY tt_open.JobId ORDER BY tt_open.AuditDatetime) AS OpenTime,
          MAX(tt_close.AuditDatetime) OVER (PARTITION BY tt_close.JobId ORDER BY tt_close.AuditDatetime) AS CloseTime
FROM TheTable tt_open LEFT OUTER JOIN TheTable tt_close
     ON tt_open.JobId = tt_close.JobId
WHERE tt_open.TaskId  IN (1,2) -- both values seem to exist for open
AND   tt_close.TaskId = 5) iv