[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

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
0
Clif
Asked:
Clif
  • 2
  • 2
1 Solution
 
HainKurtSr. System AnalystCommented:
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

0
 
ClifAuthor Commented:
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.  :)

0
 
HainKurtSr. System AnalystCommented:
is this what you want? (with more sample data added)

Vendor      Invoice      Sequence      Route_ID      Status      rn      Vendor      Invoice      Sequence      Route_ID
ABC      123      003      JA      A      1      ABC      123      003      TJ
ABC      124      003      JA      A      1      ABC      124      003      TJ
ABC      125      002      BC      A      1      ABC      125      003      CD
ABC      987      002      GWB      R      1      ABC      987      001      GWB
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
union select 'ABC' Vendor, '987' Invoice, '001' Sequence, 'GWB' Route_ID
union select 'ABC' Vendor, '987' Invoice, '002' Sequence, 'BO' 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
union select 'ABC' Vendor, '987' Invoice, '001' Sequence, 'RL' Route_ID, 'U' Status
union select 'ABC' Vendor, '987' Invoice, '002' Sequence, 'GWB' Route_ID, 'R' Status
union select 'ABC' Vendor, '987' Invoice, '003' Sequence, 'RL' Route_ID, 'A' Status
)
select * from (
select * from ( 
select rh.*, ROW_NUMBER() over (partition by rh.Vendor, rh.Invoice order by rh.Sequence desc) rn from Appr_Route_History rh left join Appr_Route_Process rp on rh.Vendor=rp.Vendor and rh.Invoice=rp.Invoice and rh.Route_ID=rp.Route_ID where rp.Route_ID is not null
) 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

0
 
ClifAuthor Commented:
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.  :)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now