SQL Query to pull pending requests by 2nd level Approver

[nalinih]

We have few items that have 2 approvers (sequential), noticing some are pending for 2nd level approver.

Would it be possible to get a SQL query to pull a report that list the order#, Request, Title, user for the items that are pending for 2nd level approver  (we have the name for 'Processor')

Not finding an easy way to get this.

Thanks

[jdempsey]

When you say "2 approvers (sequential)", is that two separate levels with one approver in each level (using "pool within level"), or is that a single level with two approvers (using "linear")?  I've written some SOAP web services to get the approval level and workflow group name, so I can probably mold those queries into what you're looking for, depending on how the workflow is structured.

[nalinih]

If Level 1 approves, then the request goes to Level 2 (if Level 1 rejects, it will not go to Level 2)

---

[nalinih]

to clarify : There is only one approver in each level (not linear)

If Level 1 approves, then the request goes to Level 2 (if Level 1 rejects, it will not go to Level 2)

-----

[TeriStevenson]

Jim probably has something much fancier that provides better data 🙂 but we have 6 approval levels and I had to write something for the team who needs to monitor where a request is, who last approved and what level it currently is at.  I took out all of the custom tables I use in this query but this may help you.  Where this line is REPLACE (REPLACE (WD_PackageRequests.UniqueUserName,'XXX\', ''), 'Admin', '') 'LAN ID' - you may not need to do a replace - I had to because some users log in with more then their 4 character ID and I only wanted the 4 character.

---------

SELECT DISTINCT Convert( varchar(10), WD_PackageRequests.DateTime, 101 ) AS 'Submit Date',
WD_PackageRequests.OrderNo 'Order Number',
REPLACE (REPLACE (WD_PackageRequests.UniqueUserName,'XXX\', ''), 'Admin', '') 'LAN ID',
WD_User.FullName 'Requester',
Case WD_PackageRequests.Authorized When 0 Then 'Pending' When 1 Then 'Approved' When 2 Then 'Rejected' When 3 Then 'Approved' End 'Approval Status',
Case WD_PackageRequests.Deleted When '0' Then 'Active' When '1' Then 'Canceled' END 'Request Status',
Case When WD_PackageRequests.LastTime IS NULL
Then ''
Else Convert( varchar(10), WD_PackageRequests.LastTime, 101 )
End 'Last Date Approved/Rejected',
ISNULL (WD_PackageRequests.Processor, '') 'Last Approver',
Case Convert( varchar(10), ISNULL( WD_PackageRequests.GroupIndex_FK, '' ))
When '0' Then 'Completed' When '1' THEN 'Level 1' When '2' THEN 'Level 2' When '3' THEN 'Level 3' When '4' THEN 'Level 4' When '5' Then 'Level 5' When '6' Then 'Level 6'
Else Convert( varchar(10), WD_PackageRequests.GroupIndex_FK ) End 'Approvals',
WD_WebPackages.PackageTitle as 'Request'

FROM WD_PackageRequests
INNER JOIN WD_WebPackages ON WD_PackageRequests.PackageID_FK = WD_WebPackages.PackageID
INNER JOIN WD_User ON WD_PackageRequests.UniqueUserName=WD_User.UniqueName

WHERE (WD_webpackages.packageid='3094'
OR WD_webpackages.packageid='3134'
OR WD_webpackages.packageid='3116'
OR WD_webpackages.packageid='3115')
and wd_packagerequests.Authorized in (0,1,2,3)
and ((DateTime>= '04/01/2019 00:00:00' And DateTime<='01/01/2025 00:00:00' AND LastTime Is Null)
OR (DateTime>='04/01/2019 00:00:00' And DateTime<='01/01/2025 00:00:00' AND LastTime>='04/01/2019 00:00:00' And LastTime<='01/01/2025 00:00:00')
OR (DateTime>='04/01/2019 00:00:00' AND LastTime>= '04/01/2019 00:00:00' And LastTime<='01/01/2025 00:00:00'))

[jdempsey]

I definitely wouldn't say "fancier" -  just different (i.e. written for a different purpose).   🙂

Here are a couple web methods I wrote that you may find helpful.  The first one returns the workflow group name for the current workflow level for a single request.  The second returns the current workflow level (which is probably more what you want) for a single request.

[WebMethod]
public string GetApprovalGroup(int ap_request_id)
{
    string CurrentApprovalGroup = "";
    string query = string.Format("SELECT CASE WHEN aptm.[ID_FK]=0 THEN 'Manager' ELSE ag.[GroupName] END AS [GroupName] FROM [WD_ApprovalProcess] ap JOIN [WD_ApprovalProcessTemplateMembers] aptm ON ap.[ApprovalID_FK] = aptm.[ApprovalTemplateMemberID] LEFT JOIN [WD_ApprovalGroup] ag ON aptm.[ID_FK] = ag.[ApprovalID] WHERE [RequestID_FK] = {0} AND [IsCurrentApprover] = 1", ap_request_id);

    SqlUtilities sql = new SqlUtilities(ESDConfig.getConnString());

    try
    {
        CurrentApprovalGroup = sql.ExecuteSQLAndReturnSingleValue(query).ToString();
    }
    catch (Exception ex)
    {
        CurrentApprovalGroup = string.Format("Failed to get current approval group. Error: {0}", ex.Message);
    }

    return CurrentApprovalGroup;
}

[WebMethod]
public int GetApprovalLevel(int ap_request_id)
{
    int CurrentApprovalLevel = 0;
    string query = string.Format("SELECT TOP (1) [GroupIndex] FROM [AppPortal].[dbo].[WD_ApprovalProcess] WHERE RequestID_FK = {0} AND IsCurrentApprover = 1", ap_request_id);

    SqlUtilities sql = new SqlUtilities(ESDConfig.getConnString());

    try
    {
        CurrentApprovalLevel = sql.ExecuteSQLAndReturnSingleValue(query).ToDbInt();
    }
    catch (Exception ex)
    {
        CurrentApprovalLevel = -1;
        Log.l1("Failed to get current approval level", logfile);
        Log.l1(ex.Message, logfile);
    }

    return CurrentApprovalLevel;
}

You could join either one of these SQL queries with the WD_PackageRequests table using RequestID_FK to get the current workflow group or current workflow level for all open requests.  Such a query might look something like this...

SELECT 'FLX-' + CAST(pr.[OrderNo] as varchar) as [OrderID]
      ,pr.[RequestID]
	  ,wp.[PackageTitle]
	  ,CASE WHEN aptm.[ID_FK]=0 THEN 'Manager' ELSE ag.[GroupName] END AS [CurrentApprovalGroup]
      ,pr.[UniqueRequesterName] as [RequestedBy]
      ,pr.[UniqueUserName] as [RequestedFor]
      ,pr.[MachineName] as [TargetDevice (software requests only)]
  FROM [AppPortal].[dbo].[WD_PackageRequests] pr
  JOIN [AppPortal].[dbo].[WD_WebPackages] wp ON pr.PackageID_FK = wp.PackageID
  JOIN [AppPortal].[dbo].[WD_ApprovalProcess] ap ON pr.RequestID = ap.RequestID_FK AND ap.IsCurrentApprover = 1 AND ap.GroupIndex = 2
  JOIN [AppPortal].[dbo].[WD_ApprovalProcessTemplateMembers] aptm ON ap.[ApprovalID_FK] = aptm.[ApprovalTemplateMemberID]
  LEFT JOIN [AppPortal].[dbo].[WD_ApprovalGroup] ag ON aptm.[ID_FK] = ag.[ApprovalID]
 WHERE pr.Deleted = 0

I hope that helps.