I want to join unique customer ids with their spends.
Problem: The account_no can be assigned to a different customer (A,B,C etc) at different point in time (assume an employee A leaving the organization and his account no is assigned to B).
Eg from the tables below: If a new customer, say B, is assigned an account number 123 (which was earlier assigned to A) on 1 Mar2015 then spend post 1 Mar2015 must fall in Bs remit til his account 123 is assigned to the next customer.
Tables to Join: Table 1 and Table 2
Desired Result: Table 3
Table 1: Assignment of account to different customers at different dates
Customer_ID Account_no Date
A 123 1Feb2015
B 123 1Mar2015
A 124 5Mar2015
C 123 1May2015
B 124 20May2015
Table 2: Daily level spend info
Account_no Spend Date
123 100 2Feb2015
123 200 2Mar2015
124 200 7Mar2015
123 200 17May2015
124 150 25May2015
Desired Output:
Customer_ID Spend
A 100+200=300
B 150+200=350
C 200
The account No can be assigned twice, thrice or more to a different customer in case it is closed by multiple customers.
How do I aggregate the spend customer Id wise?
Best Answer
You need a kind of best-match join like @TypoCubeᵀᴹ's approach, but depending on the actual data (i.e. number of rows per Account_no) this might create a huge intermediate result before aggregation (and needs a uniqueness contraint on
table2(Account_no, Date)
).The same logic logic can be achieved by a different approach using
UNION ALL
andLAST_VALUE
: