How to join two tables based on interval match

[#1]

Dear All,

I have two tables A & B having weight information of two different reader as follows

A:

ID                         FromDateTime          ToDateTime                    Weight

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

RJ09GA3510     15/06/2015 10:15:00      15/06/2015 10:30:00          50

RJ09GA3510     15/06/2015 13:05:00      15/06/2015 13:30:00          59

GJ05TA6152     17/06/2015  17:10:00     17/06/2015  17:35:00          65

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

B:

ID                         DateTime                             Weight

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

RJ09GA3510     15/06/2015 10:18:00                    50.5

RJ09GA3510     15/06/2015 13:08:00                    59.3

GJ05TA6152     17/06/2015  17:19:00                    61.2

Now, there is a requirement to get variance of weight from these two tables, and data need to be shown in following way

ID               A.FromDateTime                    B.DateTime                   A.Weight          B.Weight                 Difference(B-A)

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

RJ09GA3510     15/06/2015 10:15:00         15/06/2015 10:18:00           50                   50.5                          .5

RJ09GA3510     15/06/2015 13:05:00         15/06/2015 13:08:00           59                    59.3                         .3

GJ05TA6152     17/06/2015  17:10:00         17/06/2015  17:19:00          65                    62.2                         2.8

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

Please suggestions to join these two tables to get desired output as per requiredment.

Thanks in Advance

Regards

Hari Prasad

[hic]

You don't need to join. Just load the tables using a standard extended intervalmatch:

Intervals:
Load *, ID &'|'& FromDateTime &'|'& ToDateTime as RangeKey Inline
[ID, FromDateTime,ToDateTime,WeightA
RJ09GA3510, 15/06/2015 10:15:00,15/06/2015 10:30:00,50
RJ09GA3510, 15/06/2015 13:05:00,15/06/2015 13:30:00,59
GJ05TA6152, 17/06/2015 17:10:00,17/06/2015 17:35:00,65];

Weights:
Load *,  ID &'|'& DateTime as DateTimeKey Inline
[ID, DateTime, WeightB
RJ09GA3510, 15/06/2015 10:18:00,50.5
RJ09GA3510, 15/06/2015 13:08:00,59.3
GJ05TA6152, 17/06/2015 17:19:00,61.2];

tmpIntervalMatch:
IntervalMatch (DateTime,ID)
Load FromDateTime,ToDateTime,ID Resident Intervals;

IntervalMatch:
Load
ID &'|'& FromDateTime &'|'& ToDateTime as RangeKey,
ID &'|'& DateTime as DateTimeKey
Resident tmpIntervalMatch;

Drop Table tmpIntervalMatch;
Drop Field ID From Intervals;

and you will be able to do what you want in a chart:

HIC