Somebody helped me create a report awhile back that shows me incidents created in the off hours and closed by a certian group of individuals. I'm being asked to add the date these incidents were created. Can someone take a look and show me how to include this in the below query?
Thanks!
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
((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)) 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 = '6654e2fd-cf8f-458d-9845-df0efba41703' or
ServiceDeskIncidentManagement0.resolved_by_user_id = '09c943ff-63f1-439a-803c-c3a0521450be'))
order by
[Process.ReportProcessID] asc