I have 3 tables, see below and attached. I need create a table of Alerts (first table) with a Rank and Indicator (If statement). I'm
going to filter on Rank =1. I've been working on this for a while and keep running into issues. The latest attemp is attached but is missing the attempt for Survey2a.
Indicator = IF(AlertDate - Survey1A_Date <= 10, 'Y', 'N') AS Survey1a_Indicator
Indicator = IF(AlertDate - Survey2A_Date <= 10, 'Y', 'N') AS Survey2a_Indicator
Rank = Partition by Memberid, /* I believe I need to inlcude incase there are duplicate entries but name Survey name differes Name*/ ,AlertDate, order by SurveyIndicator desc, Survey1aDate
My output should look like below: All data from the alert table
Output: Maybe add Ranks in script, not sure what would be best. Eventually will filter of Rank = 1
Memberid AlertDate TypeOfAlert Survey1a_Indicator Survey2a_Indicator /*Survey1aRank*//* Survey2aRank*/
1 1/10/2016 Red Y 1
2 1/20/2016 Red Y 1
3 1/11/2016 Red /* Blank:Survey1a date is less than AlertDate*/ 1
4 1/12/2016 Green N 1
5 1/10/2016 Green N 1
5 1/10/2016 Green N 2 (latest survey date)
5 1/30/2016 Green Y 1
5 1/30/2016 Green N 2
6 1/5/2016 Red Y 1
/*Skipping Rows 7-9*/
10 1/10/2016 Green Y 1
10 1/10/2016 Green N 2
10 4/1/2016 Green /* Blank:Survey1a date is less than AlertDate*/ 2
10 4/1/2016 Green Y 1
/* If Memberid on both tables, I need to adjust rank to include if applicable */
11 1/1/2016 Y 1
11 1/1/2016 Y 1
12 /* Represents no Survey data but Alert data*/
13
Alert:
| AlertPK | Memberid | AlertDate | TypeOfAlert |
| 100 | 1 | 1/10/2016 | Red |
| 101 | 2 | 1/20/2016 | Red |
| 102 | 3 | 1/11/2016 | Red |
| 103 | 4 | 1/12/2016 | Green |
| 104 | 5 | 1/30/2016 | Green |
| 105 | 5 | 1/10/2016 | Green |
| 106 | 6 | 1/5/2016 | Red |
| 107 | 7 | 1/6/2016 | Red |
| 108 | 8 | 1/7/2016 | Red |
| 109 | 9 | 1/8/2016 | Green |
| 110 | 10 | 1/10/2016 | Green |
| 111 | 10 | 4/1/2016 | Green |
Survey1a:
| Memberid | Survey1Date | Region | Name |
| 1 | 1/11/2016 | East | Survey1 |
| 2 | 1/22/2016 | East | Survey1 |
| 3 | 1/1/2016 | East | Survey1 |
| 4 | 1/25/2016 | East | Survey1 |
| 5 | 2/1/2016 | East | Survey1 |
| 5 | 4/1/2016 | East | Survey1 |
Survey2a:
| Memberid | Survey2Date | Region | Name |
| 6 | 1/6/2016 | West | Survey2 |
| 7 | 10/1/2015 | West | Survey2 |
| 8 | 1/19/2016 | West | Survey2 |
| 9 | 1/9/2016 | West | Survey2 |
| 10 | 1/11/2016 | West | Survey2 |
| 10 | 4/1/2016 | West | Survey2 |
