Get Customer Name on the Max Date based on the active record

[malimohammed]

Hello Guys,

I'm working on a requirement which seems straight forward but I'm unable to find any solution.

I have two tables 

a. Call Details: Contains call records with columns for A_Party(caller) and B_Party(receiver), Call Date

b. Customer Details: Contains Customer information such as name, MobileNo:, Date Inactive, Status.

The above two tables are connected using the MobileNo Key. The customer table contains multiple records for each Mobile Number with different customers. For Example:

Customer Details Table:

MobileNo:	CustomerName	ActivityDate	Status
12345	John	01/01/2018	Active
9876	Bill	01/02/2018	Expired
12345	Dan	09/04/2017	Deactivated
12345	Ronn	12/03/2016	Expired

Call Details Table:

Call_ID	A_Party	B_party(MobileNo:)	CallDatetime	Location
1	34567	12345	25/02/2018 hh:mm:ss	Geo Code
2	23489	12345	13/03/2017 hh:mm:ss	Geo Code
3	45678	12345	23/02/2016 hh:mm:ss	Geo Code

The above tables is joined on MobileNo: and B_Party(Mobile No:). We are interested to get the receiver details which is customer name depending upon this activity date in the customer details table.

Now the requirement is to get a table as below:

The customer name should be evaluated based on the Activity Date & Call Date. If the call date is greater than the activity date then the active customer should be evaluated as: if the call date between the period of the active status of the customer. 

Resultant Table:

Call_ID	A_Party	B_Party	Call Date	CustomerName
1	34567	12345	25/02/2018	John
2	23489	12345	13/03/2017	Dan
3	45678	12345	23/02/2016	Ronn

Currently, my key is MobileNo: for this reason for each call record, I'm getting all customer names associated with the mobile no:. Is there any way to get a unique customer name as stated in the resultant table.

Is there any possibility to do this requirement on the front end as Call Details contains 560Million records and Customer Table contains 16Millions records any joins or generate missing dates might be resource intensive as the relationship is many to many between the tables.

Any suggestions would be appreciated.

[Vegar]

You should consider a way to idenitfy a valid interval for which each customer row is valid. 

I do not match your deisired output completely, but you'll get the idea from my attached qvw.