2 Replies Latest reply on Jun 11, 2018 9:20 AM by Baytex Energy

    How do I query SQL for all OPEN RFC tickets that have been fully approval?

    Baytex Energy

      Howdy

       

      I will start with the fact that i am NOT a SQL expert :-(

       

      I have looked at the two canned RFC reports but i cannot identify how to query the database to pull all OPEN Request for Change tickets that have been fully approved?

      I see the following in the DB:

      dbo.ChangeRequest
                  ChangeRequest.ChangeRequestStatusId <-- from the canned report i see value of 1 & 2 (// 1 = Pending Approval, 2 = Pending Information) but this isnt what i need. I need all OPEN tickets that are fully approved and NOT CLOSED


      or Should i be querying the DecisionStatus.DecisionStatusID? if so, what value do i need to locate for fully approved and not closed?

       

      Then I believe would need to pull the TASKS.WO_NUM and TASKS.TASK  from the associated RFC for the Powershell email to testers (see below)

       

      The backstory on my question:
      Basically, i need to pull all the open RFC tickets and present them to a Powershell Script.
      The Powershell script will then trigger an email to RFC Testers with the WOID and Summary asking them for a status update.

       

      Many thanks
      Jeff

        • 1. Re: How do I query SQL for all OPEN RFC tickets that have been fully approval?
          David DeKeizer

          Assuming version 11.4. here is a place to start from:

           

          ========================

           

          Select tk.WOID, tk.TASK, Case When IsNull(tk.CLSDDATE,GetDate()+1) > GetDate() then 'OPEN' else 'CLOSED' end as State

          , cr.Instructions, cr.DueDate, crs.ChangeRequestStatusName

          , dec.ReviewerFULLNAME, usr.EMAILADDR, dec.DecisionDate

           

          from TASKS tk

           

          inner join ChangeRequest cr on (tk.WOID = cr.WOID)

          inner join ChangeRequestStatus crs on (cr.ChangeRequestStatusId = crs.ChangeRequestStatusId)

          inner join Decision dec on (cr.ChangeRequestId = dec.ChangeRequestId)

          inner join TIUSER usr on (dec.ReviewerUSERID = usr.USERID)

           

          =======================

           

          This will result in a listing of all Work Orders with a CFR and the people whose input was requested. You can then filter on the task state, the CFR status, and whether there was a response.

           

          If you only wanted the assigned technician's email, cut out the Decision table/fields and change the join for TIUser to

           

                   inner join TIUSER usr on (tk.RESPONS = usr.FULLNAME)

           

           

          I hope this helps!

          • 2. Re: How do I query SQL for all OPEN RFC tickets that have been fully approval?
            Baytex Energy

            Thank you very much
            Let me try this and see what results i get
            Cheers
            JEff