ServiceDesk

 View Only

  • 1.  SQL report questions for getdate

    Posted Feb 06, 2013 04:13 PM
    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


  • 2.  RE: SQL report questions for getdate

    Broadcom Employee
    Posted Feb 06, 2013 05:55 PM

    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.



  • 3.  RE: SQL report questions for getdate

    Posted Feb 07, 2013 07:20 AM

    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)



  • 4.  RE: SQL report questions for getdate

    Broadcom Employee
    Posted Feb 07, 2013 09:21 AM

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



  • 5.  RE: SQL report questions for getdate

    Posted Feb 07, 2013 12:02 PM
    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.