ServiceDesk

Can somebody help me finalize this report?  I'm being asked to supply a report that shows incidents that were created this year between Friday's at 4pm thru Monday at 7am.  Here is my current SQL:

select distinct

Process.ReportLogProcessID as [Process.ReportLogProcessID],

Process.ReportProcessID as [Process.ReportProcessID],

Process.ProcessStarted as [Process.ProcessStarted],

Process.ProcessTitle as [Process.ProcessTitle],

Process.Result as [Process.Status],

DATEPART(hh, Process.ProcessStarted) as [Process.ProcessStartedHour],

DATENAME(dw, Process.ProcessStarted) as [Process.ProcessStartedDayOfWeek],

Process.SessionID as [Process.SessionID],

Process.ProcessViewerPageID as [Process.ProcessViewerPageID],

ServiceDeskIncidentManagement0.process_id as [Incident.ID],

ServiceDeskIncidentManagement0.priority as [Incident.Priority],

ServiceDeskIncidentManagement0.classification_category_01 as [Incident.Category1],

(SELECT TOP 1 [PrimaryEmail] FROM [User] WHERE ServiceDeskIncidentManagement0.resolved_by_user_id = [UserID]) as [Incident.ResolvedByUser],

User1.PrimaryEmail as [UserTable.PrimaryEmail],

User1.FirstName as [UserTable.FirstName],

User1.LastName as [UserTable.LastName],

ReportProcessReference5.[Name] as [ReportProcessReference.ReferenceName.Location]

from

ReportProcess as Process inner join

ServiceDeskIncidentManagement as ServiceDeskIncidentManagement0 with (NOLOCK) on

((ServiceDeskIncidentManagement0.process_id = Process.ReportProcessID)) inner join

ReportProcessContact as ReportProcessContact1 on

((ReportProcessContact1.SessionID = Process.SessionID and

ReportProcessContact1.IsPrimary = 'True')) left outer join

[User] as User1 on

((ReportProcessContact1.ReferenceID = User1.UserID)) left outer join

[Group] as Group1 on

((ReportProcessContact1.ReferenceID = Group1.GroupID and

ReportProcessContact1.ReferenceType = 2)) left outer join

UserAddress as UserAddress4 on

((User1.UserID = UserAddress4.UserID)) left outer join

ReportProcessReference as ReportProcessReference5 on

((ReportProcessReference5.SessionID = Process.SessionID and

(ReportProcessReference5.ReferenceType = 'Location'))) left outer join

ReportProcessHistory as ReportProcessHistory6 on

((ReportProcessHistory6.SessionID = Process.SessionID))

where

('3DCA0263-D5EF-48ae-BF96-A161989DFB8E' = '3DCA0263-D5EF-48ae-BF96-A161989DFB8E' and

Process.ProcessStarted > '1/1/2012 12:00:00 AM' and

Process.ProcessStarted <= '12/31/2012 11:59:59 PM' and

(ServiceDeskIncidentManagement0.resolved_by_user_id = '6fd81737-304a-4b85-aaf8-50e3a77f5531' or

ServiceDeskIncidentManagement0.resolved_by_user_id = '22e9d1d6-e1b7-4586-b660-01c5cc01b1eb' or

ServiceDeskIncidentManagement0.resolved_by_user_id = '0922686f-33c2-4f8e-9991-e0bc7f0aae2e' or

ServiceDeskIncidentManagement0.resolved_by_user_id = 'fb4c6258-b485-41a9-b634-597611761c6c' or

ServiceDeskIncidentManagement0.resolved_by_user_id = 'f2861566-391d-4907-8a60-e8f164552041' or

ServiceDeskIncidentManagement0.resolved_by_user_id = '4219fa95-2db5-4a04-ac4c-919e696ef909' or

ServiceDeskIncidentManagement0.resolved_by_user_id = '09c943ff-63f1-439a-803c-c3a0521450be'))

order by

[Process.ReportProcessID] asc

Looks like you're almost there.  Just add this to your where clause:

and ((DATENAME(dw, Process.ProcessStarted) = 'Saturday' or DATENAME(dw, Process.ProcessStarted) = 'Sunday') or (DATENAME(dw, Process.ProcessStarted) = 'Friday' and DATENAME(hh, Process.ProcessStarted) > 15) or (DATENAME(dw, Process.ProcessStarted) = 'Monday' and DATENAME(hh, Process.ProcessStarted) < 7))

Basically, include the tickets that were started on (Saturday or Sunday) or ( Friday after 4:00) or (Monday before 7)

Thanks Joshua.  I didn't know how to write that part.