Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.