Inbound /Outbound Date difference calculation

karthickpbi 

So I'm not finished, but am getting close.

As you will see, I really need an answer to the duplicate timestamps.

These present a significant problem.

Scenario A  = CASE ID 102

1) Create the following "Order" column.

It is imperative that this column groups by both [CASE ID] & [Direction].

The purpose of this column is to label in order (from 1 to N) the lowest timestamp to the highest timestamp within each grouping of CASE ID & Direction.

NOTE: In this case, I am isolating CASE ID 102 to simplify the example.

2) Create a new table called "Inbound" as follows, including only the Inbound rows.

NOTE: I renamed column "Created On" to "Date In". 

3) Create a new table called "Outbound" as follows, including only the Outbound rows.

4) In the Inbound table, create the following columns:

- Date Out

NOTE: THIS IS WHERE THE MAGIC HAPPENS!  WE'RE LINKING [Date In] TO [Date Out] BASED ON [Order]!

- Difference DateDiff (in minutes)

- Difference Operator (in HH:MM:SS)

NOTES: I provided Difference in 2 different ways.  Use whichever you like.

Scenario B  = CASE ID 101

- As you can see in Line 10 & 11, there are 2 identical rows for CASE 101 & Direction Outbound.  As a result, both rows get assigned Order #2.  For this reason, no Order #1 gets created for 101 Outbound, and therefore, cannot match up correctly with Order #1 for 101 Inbound.  The end result, is incorrect matching & incorrect calculations.

- There is a similar problem with Line 4 & 6.  There are 2 identical rows for CASE 101 & Direction Inbound.  I don't understand how Order selected Row 6 as "Order 1" & Row 4 as "Order 2".  But this really doesn't matter since we don't know which one should come first anyway.  So it could be right or wrong, but we have no way to know.

Finally, as you can tell, I have also not yet programmed in the rule about [Date In] needing to be LESS THAN [Date Out] for it to be valid.  If you get stuck on this one, let me know and I will come back to you.

Before worrying about that though, you either need to:

1) Remove identical duplicate rows (OR)

2) Clearly specify how to handle identical duplicate rows, if they are in fact, legitimate.

Hope what I have provided so far is helpful to you!

Regards,

Nathan