Comparing two data sets A and B and retrieving 6 month backdated data from dataset A
Good day,
I'm comparing two data sets Repossessions and Accounts.
I want to compare the Account numbers from Repossessions for the current month and look it up from the Accounts on the current month to 6 months back.
Meaning if the date 2016/07/06 on the Repossession data set and its account number matches the account number on the Accounts data set within the period of 2016/02/06 and 2016/07/06, then count those account numbers.
Below is what I tried doing, but it doesn't give the desired results:
Temp: LOADACCOUNT_NUMBER
FROM $(vAccounts)\ACCOUNTS_*.QVD(QVD) wheredate(OPEN_DATE, 'YYYY/MM/DD')<=Today() anddate(OPEN_DATE, 'YYYY/MM/DD')>=AddMonths(date(OPEN_DATE, 'YYYY/MM/DD'),-6);