I have requirement like based one date filed i need to go back of 2 business working days and need to get the status of that particular date. let me explain with the sample data
Excitement:
| Report_Date | Candidate_ID | Status |
| 3/20/2019 | 12 | Low |
| 3/20/2019 | 13 | Low |
| 3/21/2019 | 12 | Med |
| 3/21/2019 | 13 | Low |
| 3/22/2019 | 12 | Med |
| 3/25/2019 | 12 | High |
| 3/25/2019 | 13 | Med |
| 3/26/2019 | 12 | High |
| 3/26/2019 | 13 | Med |
| 3/27/2019 | 12 | Low |
| 3/28/2019 | 12 | High |
| 3/29/2019 | 12 | High |
Joinerlist:
| Candidate_ID | Strat_Date |
| 12 | 4/1/2019 |
| 13 | 3/28/2019 |
| 14 | 4/2/2019 |
In Excitement table i have data wise candidate excitement upto candidate is going to join. In Joinerlist there may be a chance of who was not there in Excitement table . Here we need to get details of candidates who was their in Excitement table based on the Strat_Date field. Note: From Strat_Date we need to go back to the 2 business working days at that we need to get the details of candidate Status.
Ex: for Canidate -12 Strat_Date was 4/1/2019 so we need to go to the Report_Date =3/28/2019 on this date we need to get Status here it is 'High'
At last we need to get output like this
| Strat_Date | High | Med | Low |
| 4/1/2019 | 1 | 0 | 0 |
| 3/28/2019 | 0 | 1 | 0 |
| 4/2/2019 | 0 | 0 | 0 |
Kindly help me on this issue.
