asked on

Need a Record Depending on the Value in a Field

I know the title isn't very clear and concise, but I don't know how to explain it in a few words.

I need a way to figure out where in an approval process for a PO has stopped.

I have two tables:

Table 1: Appr_Route_Process

Vendor  Invoice  Sequence  Route_ID
ABC     123      001       GW
ABC     123      002       JA
ABC     123      003       TJ

Open in new window

Table 2: Appr_Route_History

Vendor  Invoice  Sequence  Route_ID  Status
ABC     123      001       KJ        U
ABC     123      002       GW        A
ABC     123      003       JA        A

Open in new window

It would be presumed that any Route_ID in Table 2 that does not exist in Table 1 is the source and, as such, will never be part of the actual approval process.

Now, from the example above,
Since the last step in Table 2 (sequence 003) is an approval (Status = 'A'), I need the next record (Route_ID) in the process (Table 1).
In other words the record:
ABC 123 003 TJ

On the other hand, if the last record in the history (Table 2) were a rejection (Status = 'R'), then I need the record in the process (Table 1) that preceeds the Rounte_Id.
In other words, the record:
ABC 123 001 GW

One last kink...
In the case where the last record in the history (Table 2) is an approval (Status = 'A'), but the Route_ID is not in the process (Table1), then I need the first record from the process.

Hopefully I explained it well enough.

TIA

HainKurt

try this

I get this for sample data below

Vendor      Invoice      Sequence      Route_ID      Status      rn      Vendor      Invoice      Sequence      Route_ID
ABC      123      003      JA      A      1      ABC      123      003      TJ
ABC      124      004      XT      R      1      ABC      124      001      GW
ABC      125      002      BC      A      1      ABC      125      003      CD

with 
Appr_Route_Process as(
      select 'ABC' Vendor, '123' Invoice, '001' Sequence, 'GW' Route_ID
union select 'ABC' Vendor, '123' Invoice, '002' Sequence, 'JA' Route_ID
union select 'ABC' Vendor, '123' Invoice, '003' Sequence, 'TJ' Route_ID
union select 'ABC' Vendor, '124' Invoice, '001' Sequence, 'GW' Route_ID
union select 'ABC' Vendor, '124' Invoice, '002' Sequence, 'JA' Route_ID
union select 'ABC' Vendor, '124' Invoice, '003' Sequence, 'TJ' Route_ID
union select 'ABC' Vendor, '125' Invoice, '001' Sequence, 'AB' Route_ID
union select 'ABC' Vendor, '125' Invoice, '002' Sequence, 'BC' Route_ID
union select 'ABC' Vendor, '125' Invoice, '003' Sequence, 'CD' Route_ID
),
Appr_Route_History as (
      select 'ABC' Vendor, '123' Invoice, '001' Sequence, 'KJ' Route_ID, 'U' Status
union select 'ABC' Vendor, '123' Invoice, '002' Sequence, 'GW' Route_ID, 'A' Status
union select 'ABC' Vendor, '123' Invoice, '003' Sequence, 'JA' Route_ID, 'A' Status
union select 'ABC' Vendor, '124' Invoice, '001' Sequence, 'KJ' Route_ID, 'A' Status
union select 'ABC' Vendor, '124' Invoice, '002' Sequence, 'GW' Route_ID, 'A' Status
union select 'ABC' Vendor, '124' Invoice, '003' Sequence, 'JA' Route_ID, 'A' Status
union select 'ABC' Vendor, '124' Invoice, '004' Sequence, 'XT' Route_ID, 'R' Status
union select 'ABC' Vendor, '125' Invoice, '001' Sequence, 'AB' Route_ID, 'U' Status
union select 'ABC' Vendor, '125' Invoice, '002' Sequence, 'BC' Route_ID, 'A' Status
)
select * from (
select * from ( select *, ROW_NUMBER() over (partition by Vendor, Invoice order by Sequence desc) rn from Appr_Route_History) x where rn=1
) a left join Appr_Route_Process b on a.Vendor=b.Vendor and a.Invoice=b.invoice and 
(
( a.Status='A' and b.Sequence =
(select top 1 sequence from Appr_Route_Process d where d.Vendor=a.Vendor and d.Invoice=a.invoice and Sequence > (select sequence from Appr_Route_Process c where c.Vendor=a.Vendor and c.Invoice=a.invoice and c.Route_ID=a.Route_ID)))
or 
(a.Status='R' and b.Sequence='001')
)

Open in new window

Clif

ASKER

I'm sorry, but it's not coming out right. Well, the record are returning correctly per the sample, but I have a piece of live data which os coming out wrong.

Here is the live data:
Table 1: Appr_Route_Process

Vendor  Invoice  Sequence  Route_ID
XYZ     987      001       GWB
XYZ     987      002       BO

Open in new window

Table 2: Appr_Route_History

Vendor  Invoice  Sequence  Route_ID  Status
XYZ     987      001       RL        U
XYZ     987      002       GWB       R
XYZ     987      003       RL        A

Open in new window

Here's the explanation (as I understand it)...

Any Route_ID that appears in the history, but not in the process is ignored. So Sequence = '003' is ignored, that would make the rejection (Sequence='002') the one to go to. Since the Route_ID (GWB) is also the first in the process, there is nothing before so that's the record (Sequence='001') that is returned.

I presume the data I would be looking for are the fields that come after the "rn" field. In my example, when I ran the test, these fields are NULL.

I do apologize for the confusion, but it is equally confusing to me. :)

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Clif

ASKER

Oh Heck!

My live "Sample" was just approved by "GWB", so I can't use it as a test anymore. Two weeks it's been sitting in the queue and "GWB" never touched it, now that I was using it as sample data, he approves it! :-P

On the good news front, your example did return the correct record (that "BO" was the next approver that hasn't yet approved).

But your example worked with the original sample data, so I'm going to presume it would have worked with the live data.

Thanks for the help. :)