title: DB2 SQL : LISTAGG considering priority and deletion indicator
body: <p>I have a dataset like below (the dataset is ordered by timestamp). In the below dataset for the combination of EMP_ID &amp; DEPT_ID different courses can be assigned. We have to find out all courses valid for a date range for EMP_ID &amp; DEPT_ID combination.</p>
  <p>We have consider F_PRIORITY (first priority) and S_PRIORITY (second priority) attributes:</p>
  <ul>
  <li>&quot;Critical &gt; High &gt; Medium &gt; Low&quot; valid for first priority.</li>
  <li>If for two records on same date (YYYYMMDD), if the F_PRIORITY value is same, then S_PRIORITY need to be considered. S_PRIORITY always have integer number and highest value gets the priority.</li>
  <li>If for any date (YYYYMMDD) we found both F_PRIORITY and S_PRIORITY are same then multiple course needs to be combined with comma separator.</li>
  <li>At any point if any course found as DELETED (DELETED=1) and if that course is considered based on priority value then that need to be deleted from the next effective start date.</li>
  <li>If a course is found for any date for a combination of EMP_ID and DEPT_ID with high priority then the same course should flow in all the next available dates. If same priority course found in next available date for the combination of EMP_ID and DEPT_ID then both the courses should be present with comma separated. If the priority of the course is higher in next available date then it will replace the previous date's course.</li>
  </ul>
  <p>I want to calculate all these in DB2 database. If simple query can't help, then please let me know if any function can help.</p>
  <p>Source Dataset:</p>
  <pre><code>D_DATE     EMP_ID  DEPT_ID  COURSE_ID F_PRIORITY  S_PRIORITY DELETED  D_TIMESTAMP
  20190419   E1      D1       C1        Low         2          0        2019-04-19 12:10:18
  20190521   E1      D1       C2        Medium      2          0        2019-05-21 12:10:18
  20190521   E1      D1       C3        High        4          0        2019-05-21 14:10:18
  20190521   E1      D1       C4        High        4          0        2019-05-21 14:11:18
  20190621   E1      D1       C3        High        4          1        2019-06-21 15:10:18
  20190621   E1      D1       C5        High        4          0        2019-06-21 16:10:18
  20191021   E1      D1       C6        High        8          0        2019-06-21 16:10:18
  20200121   E1      D1       C7        Critical    4          0        2019-05-21 14:10:18
  20200121   E1      D1       C8        Critical    4          0        2019-05-21 14:11:18
  
  20190419   E2      Null     C1        Low         2          0        2019-04-19 12:10:18
  20190521   E2      Null     C2        Medium      2          0        2019-05-21 12:10:18
  20190521   E2      Null     C3        High        4          0        2019-05-21 14:10:18
  20190521   E2      Null     C4        High        4          0        2019-05-21 14:11:18
  20190531   E2      Null     C2        Medium      2          1        2019-05-31 15:01:18
  
  20190621   E2      D2       C3        High        4          0        2019-06-21 15:10:18
  20190621   E2      D2       C5        High        6          0        2019-06-21 16:10:18
  20190721   E2      D2       C5        High        6          1        2019-06-21 15:10:18
  20191021   E2      D2       C7        Low         8          0        2019-06-21 16:10:18
  </code></pre>
  <p>Expected Output:</p>
  <pre><code>EMP_ID  DEPT_ID  COURSE_DTL  S_DATE      E_DATE
  E1      D1       C1          20190419    20190520
  E1      D1       C3,C4       20190521    20190620 (C2 not considered as Medium)
  E1      D1       C4,C5       20190621    20191020 (C3 Deleted, so removed)
  E1      D1       C6          20191021    20200120 (C6 takes priority as S_PRIORITY=8)
  E1      D1       C7,C8       20200121    99991231 (C7, C8 F_PRIORITY=Critical; so takes priority)
  
  E2      Null     C1          20190419    20190520
  E2      Null     C3,C4       20190521    99991231 (addition of C2 does not impact as F_PRIORITY is less than C3 and C4. Also DELETED of C2 does not impact as C2 was not there in the consideration from Date=20190521)
  
  E2      D2       C5          20190621    20190720
  E2      D2       Null        20190721    20191020
  E2      D2       C7          20191021    99991231
  </code></pre>