CREATE VIEW [dbo].[CurrentAndPriorApprovers_View]
AS
    SELECT
        Form.FormId,
        Form.TypeId,
        Form.EmployeeIndex,
        Form.NonEmployeeName,
        CurrentApprover.CurrentApproverId,
        WalshGroup.dbo.uf_LookUpUserName(CurrentApprover.CurrentApproverId) AS CurrentApproverName,
        ISNULL(PriorApprover.PriorApproverId, Form.CreatedById) AS PriorApproverId,
        WalshGroup.dbo.uf_LookUpUserName(ISNULL(PriorApprover.PriorApproverId,
                                                    Form.CreatedById)) AS PriorApproverName,
        ISNULL(PriorApprovalWhen, Form.CreatedDate) AS PriorApprovalWhen,
        CurrentApprover.CurrentRank
    FROM
        Form
        LEFT OUTER JOIN ( SELECT
                            FormId,
                            UserId AS CurrentApproverId,
                            [Rank] AS CurrentRank
                          FROM
                            ( SELECT
                                FormId,
                                UserId,
                                ROW_NUMBER() OVER ( PARTITION BY FormId ORDER BY Rank ) AS RowNum,
                                [Rank]
                              FROM
                                [eForms].[dbo].[RoutePlan]
                              WHERE
                                StatusDate IS NULL AND
                                Deleted = 0
                            ) AS CA
                          WHERE
                            CA.RowNum = 1
                        ) AS CurrentApprover
            ON dbo.Form.FormId = CurrentApprover.FormId
        LEFT OUTER JOIN ( SELECT
                            FormId,
                            UserId AS PriorApproverId,
                            StatusDate AS PriorApprovalWhen
                          FROM
                            ( SELECT
                                FormId,
                                UserId,
                                StatusDate,
                                ROW_NUMBER() OVER ( PARTITION BY FormId ORDER BY Rank DESC ) AS RowNum
                              FROM
                                [eForms].[dbo].[RoutePlan]
                              WHERE
                                StatusDate IS NOT NULL AND
                                Deleted = 0
                            ) AS PA
                          WHERE
                            PA.RowNum = 1
                        ) AS PriorApprover
            ON form.FormId = PriorApprover.FormId