Video Screencast Help

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
 
Thank You
Jen

Comments 4 CommentsJump to latest comment

TGiles's picture

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.

reecardo's picture

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)

TGiles's picture

Unfortunately the draw back with doing direct SQL queries is the inability to incorporate the link interaction.

skiierj's picture

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.