Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In one scenario, we have data in following format where the audits are happening in an uneven interval.
Audit_Table:
CustomerID | AuditDate | Status |
12345 | 9/1/2018 | PASS |
12345 | 10/18/2018 | PASS |
12345 | 2/28/2019 | FAIL |
23456 | 12/1/2018 | PASS |
8765 | 9/9/2018 | PASS |
2345 | 2/2/2019 | FAIL |
Sales_Table:
CustomerID | SalesDate | SALE |
12345 | 9/1/2018 | 100 |
12345 | 10/1/2018 | 123 |
12345 | 11/1/2018 | 234 |
12345 | 12/1/2018 | 123 |
12345 | 1/1/2019 | 111 |
12345 | 2/1/2019 | 100 |
23456 | 12/1/2018 | 12 |
8765 | 9/1/2018 | 3 |
8765 | 10/1/2018 | 44 |
2345 | 2/1/2019 | 1234 |
Query: On selection of any intermediate month e.g. Oct’18 , we should able to show below result. i.e. list of customers & sales whose audit happened on that month and if no audit happened in that month then the last available audit details but sales of selected month.
e.g. Expected Output:
Month of selection: Oct’18
CustomerID | AuditDate | Status | Sale |
12345 | 10/18/2018 | PASS | 123 |
8765 | 9/9/2018 | PASS | 44 |
Note: date is in ‘MM/DD/YYYY’ format.
Please advice the best way to model the data to achieve above result.
Thanks in advance.
I suggest you try to define a start and stop time for each audit transaction and thereafter look into the concept of interval matching the sales data.
I suggest you try to define a start and stop time for each audit transaction and thereafter look into the concept of interval matching the sales data.
It worked.. thanks!!