How to compare 2 rows and bring the result of the desired row

34 Views

I have following tables

Table1:

Id Order_nm User Next_loc Last_loc Date

-------------------------------------------------------------------------------

101 order1 user1 Inbox null 1/15/14

101 order1 user2 loc_1 Inbox 1/18/14

101 order1 user3 loc_2 loc_1 1/21/14

101 order1 user2 inbox loc_2 1/21/14

101 order1 user5 loc_2 Inbox 1/22/14

101 order1 user4 Inbox loc_2 1/25/14

101 order1 user5 loc_3 Inbox 1/31/14

Table2: duplicate of table 1

Id Order_nm User Next_loc Last_loc Date

-------------------------------------------------------------------------------

101 order1 user1 Inbox null 1/15/14

101 order1 user2 loc_1 Inbox 1/18/14

101 order1 user3 loc_2 loc_1 1/21/14

101 order1 user2 inbox loc_2 1/21/14

101 order1 user5 loc_2 Inbox 1/22/14

101 order1 user4 Inbox loc_2 1/25/14

101 order1 user5 loc_3 Inbox 1/31/14

I am joining both tables as table1.id = table2.id and table1.last_loc = table2.next_loc

My requirement: When I run a report for user2, user5 I expect the following

User2

Order_nm User Order_receive_dt Order_routed_dt Days_held Description

----------------------------------------------------------------------------------------------------------------------

Order1 user2 1/15/14 1/18/14 3 Moved to loc_1

Order1 user2 1/21/14 1/21/14 1 Moved to Inbox

User5

Order_nm User Order_receive_dt Order_routed_dt Days_held Description
----------------------------------------------------------------------------------------------------------------

Order1 user5 1/21/14 1/22/14 1 Moved to loc_2
Order1 user5 1/25/14 1/31/14 6 Moved to loc_3

For user 5 since there are 2 rows in table, we get 2 rows.

1. Line 5,Table 1 last_loc is compared to line 4,table2 next_loc

2. Line 7,Table 1 last_loc is compared to line 6,table2 next_loc

My current output:For User5 - Line 5,Table 1 last_loc is compared not only to line 4,table2 next_loc but also to all the possible column4 in table2.

User5

Order_nm User Order_receive_dt Order_routed_dt Days_held Description
--------------------------------------------------------------------------------------------------------------------
Order1 user5 1/21/14 1/22/14 1 Moved to loc_2
Order1 user5 1/15/14 1/22/14 7 Moved to loc_2
Order1 user5 1/21/14 1/31/14 6 Moved to loc_3
Order1 user5 1/21/14 1/31/14 10 Moved to loc_3
Order1 user5 1/15/14 1/31/14 16 Moved to loc_3

Can anybody help me with this. Can this be achieved at the report level?

Appreciate any help.

-Tulasi