SQL report questions for getdate
Created: 06 Feb 2013 | 4 comments
I have written a report to show which tickets that haven't been edited(modified) in 30 days. It works great but every monday I have to manually change the date modified. In helpdesk I had one that used datediff and getdate > 30. The problem is I can't figure out who to make it happen in Servicedesk. Can anyone give me some direction. Here is the 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],
Process.SessionID as [Process.SessionID],
Process.ProcessViewerPageID as [Process.ProcessViewerPageID],
Task0.TaskID as [Task.TaskID],
Task0.RespondDisplayFormat as [Task.RespondDisplayFormat],
[dbo].GetTaskAssignments(Task0.TaskID) as [Task.TaskAssignments],
Task0.FormWidth as [Task.FormWidth],
Task0.FormHeight as [Task.FormHeight],
TaskResponse3.URL as [Task.ResponseURL],
TaskResponse3.Title as [Task.ResponseTitle],
TaskResponse3.Description as [Task.ResponseDescription],
TaskResponse3.TaskResponseID as [Task.TaskResponseID],
LastModified4.LastModified as [LastModified.LastModified]
from
ReportProcess as Process with (NOLOCK) inner join
Task as Task0 with (NOLOCK) on
((Task0.SessionID = Process.SessionID)) inner join
TaskAssignment as TaskAssignment1 with (NOLOCK) on
((TaskAssignment1.TaskID = Task0.TaskID)) left outer join
[Group] as Group1 with (NOLOCK) on
((Group1.GroupID = TaskAssignment1.ReferenceID)) left join
TaskResponse as TaskResponse3 with (NOLOCK) on
((TaskResponse3.TaskID = Task0.TaskID and
(select count(*) c from TaskResponse where TaskResponse.TaskID = Task0.TaskID) < 2)) inner join
LastModified as LastModified4 with (NOLOCK) on
((LastModified4.LastModified <= '12/7/2012 12:00:00 AM' and
LastModified4.SessionID = Process.SessionID))
where
(Task0.DoNotShowInTaskList = 'False' and
Task0.IsCompleted = 'False')
order by
[LastModified.LastModified] asc
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],
Process.SessionID as [Process.SessionID],
Process.ProcessViewerPageID as [Process.ProcessViewerPageID],
Task0.TaskID as [Task.TaskID],
Task0.RespondDisplayFormat as [Task.RespondDisplayFormat],
[dbo].GetTaskAssignments(Task0.TaskID) as [Task.TaskAssignments],
Task0.FormWidth as [Task.FormWidth],
Task0.FormHeight as [Task.FormHeight],
TaskResponse3.URL as [Task.ResponseURL],
TaskResponse3.Title as [Task.ResponseTitle],
TaskResponse3.Description as [Task.ResponseDescription],
TaskResponse3.TaskResponseID as [Task.TaskResponseID],
LastModified4.LastModified as [LastModified.LastModified]
from
ReportProcess as Process with (NOLOCK) inner join
Task as Task0 with (NOLOCK) on
((Task0.SessionID = Process.SessionID)) inner join
TaskAssignment as TaskAssignment1 with (NOLOCK) on
((TaskAssignment1.TaskID = Task0.TaskID)) left outer join
[Group] as Group1 with (NOLOCK) on
((Group1.GroupID = TaskAssignment1.ReferenceID)) left join
TaskResponse as TaskResponse3 with (NOLOCK) on
((TaskResponse3.TaskID = Task0.TaskID and
(select count(*) c from TaskResponse where TaskResponse.TaskID = Task0.TaskID) < 2)) inner join
LastModified as LastModified4 with (NOLOCK) on
((LastModified4.LastModified <= '12/7/2012 12:00:00 AM' and
LastModified4.SessionID = Process.SessionID))
where
(Task0.DoNotShowInTaskList = 'False' and
Task0.IsCompleted = 'False')
order by
[LastModified.LastModified] asc
Thank You
Jen
Discussion Filed Under:
Comments 4 Comments • Jump to latest comment
On the left side of the report builder when you select the Last Modified date to appear click the blue arrow. This will give you some options to configure the time frame.
If you aren't sure take a look at some of the out of the box reports that reference time frames for examples of how this is done.
To get things that haven't changed in 30 days, your SQL should look like the following:
((LastModified4.LastModified <= '12/7/2012 12:00:00 AM' and
should change to this:
((DATEDIFF(day, LastModified4.LastModified, GETDATE()) > 30 and
The above basically means "LastModDate is bigger than 30 days"
What you could do is get the SQL changed above, then make a report using the changed SQL (after selecting Data Source, specify SQL query on the left of the Report Designer, then copy/paste the SQL into the editor)
Unfortunately the draw back with doing direct SQL queries is the inability to incorporate the link interaction.
Thank so much, that worked great. I wasn't that worried about it not having the link interaction because I just export it to excel and send it to the Managers and Directors.
Would you like to reply?
Login or Register to post your comment.