I would like your help to identify the best way to do a search for which codes were active on a given date.
I suppose you want to analyze the active codes on the date 31/08/2002.
The rule I understand to be the correct one would be:
- Adhesion is less than the date of analysis (31/08/2002)
- (Exclusion is null or Exclusion > date of analysis (31/08/2002))
So I have the following example data:
| Code |
Adhesion |
Exclusion |
| 2679273 |
05/11/2007 |
null |
| 2621160 |
18/12/2002 |
13/06/2005 |
| 2621194 |
18/12/2002 |
13/06/2005 |
| 2621216 |
18/12/2002 |
13/06/2005 |
| 2620848 |
05/12/2002 |
11/06/2010 |
| 2620775 |
04/12/2002 |
18/11/2003 |
| 2620821 |
27/11/2002 |
15/12/2005 |
| 2620805 |
26/11/2002 |
null |
| 2619670 |
04/11/2002 |
null |
| 2619700 |
04/11/2002 |
null |
| 2619653 |
22/10/2002 |
23/01/2003 |
| 2618266 |
29/08/2002 |
null |
| 2616999 |
01/08/2002 |
15/01/2019 |
| 2617030 |
01/08/2002 |
20/08/2002 |
| 2617057 |
05/08/2002 |
12/09/2016 |
| 2617073 |
01/08/2002 |
10/11/2004 |
| 2617081 |
10/08/2002 |
10/11/2004 |
| 2617103 |
01/08/2002 |
10/11/2004 |
| 2615003 |
10/07/2002 |
null |
The codes in bold fit the rule, so it would have 7 active codes until the date of 31/08/2002.
if I were to build this in SQL, it would look something like this:
where
to_date(Adhesion)<= to_date('31/08/2021')
and (Exclusionis null or to_date(Exclusion)> to_date('31/08/2021') )
How could I write this into an SetAnalysis ?
PS: Translate on Google
