I have this table (in reality it has more fields but for simplicity, it will demonstrate what I'm after)
| Payment_Type | Person ID | Payment_date | Payment_Amount |
|---|---|---|---|
| Normal | 1 | 2015-01-01 | £1.00 |
| Normal | 1 | 2017-01-01 | £2.00 |
| Reversal | 1 | 2022-01-09 | £3.00 |
| Normal | 2 | 2016-12-29 | £3.00 |
| Reversal | 2 | 2022-01-02 | £4.00 |
I need 2 specific things from this:
I need all entries where there is over 6 years difference between any given payment dates (when its been greater than or equal to 6 years from the date of the latest payment date). I don't need to count them, I just need it to return all the entries that meet this criteria.
I also need it to specify where a normal payment hasn't been made for 6 years or more from todays date but a reversal has however occurred within the last 6 years. (This might need to be a separate query but will take suggestions)
I'm using Data Lake (Hue).
Thank you.
I've tried to run a sub query with join and union but I'm not getting the desired results so will need to start from scratch. Any advice/insight on this is greatly appreciated.
Ideally, query one will show:
| Payment_Type | Person ID | Payment_date | Payment_Amount |
|---|---|---|---|
| Normal | 1 | 2015-01-01 | £1.00 |
| Normal | 1 | 2017-01-01 | £2.00 |
| Normal | 2 | 2016-12-29 | £3.00 |
Query 2 results should show:
| Payment_Type | Person ID | Payment_date | Payment_Amount |
|---|---|---|---|
| Normal | 1 | 2017-01-01 | £2.00 |
| Reversal | 1 | 2022-01-09 | £3.00 |
| Normal | 2 | 2016-12-29 | £3.00 |
| Reversal | 2 | 2022-01-02 | £4.00 |
