I have data that looks as follows.
| PersoonID | AttributeID | StartDate | EndDate | Verhouding |
|---|---|---|---|---|
| 16525 | 4 | 2023-01-01 | 2023-02-01 | 32 |
| 16573 | 5 | 2023-04-01 | 2023-03-01 | 78 |
| 16500 | 1 | 2023-01-01 | 2023-07-01 | 34 |
| 16500 | 1 | 2023-01-01 | 2023-05-01 | 0 |
| 16356 | 1 | 2023-01-01 | 2023-05-01 | 0 |
I want to tag every field which has a 'Verhouding' other than zero. This can easily be done by using a CASE WHEN, Like so:
SELECT table.*,
CASE WHEN Verhouding != 0 THEN 1 ELSE 0
END AS verhouding_flag
FROM table
The expected result would look as follows:
| PersonID | AttributeID | StartDate | EndDate | Verhouding | verhouding_flag |
|---|---|---|---|---|---|
| 16525 | 4 | 2023-01-01 | 2023-05-01 | 32 | 1 |
| 16573 | 5 | 2023-04-01 | 2023-05-01 | 78 | 1 |
| 16500 | 1 | 2022-05-01 | 2023-05-01 | 34 | 1 |
| 16500 | 1 | 2023-05-01 | 2023-05-01 | 0 | 0 |
| 16356 | 1 | 2023-01-01 | 2023-05-01 | 0 | 0 |
However, in some special cases, there is a Person which has a Verhouding of 0 AND a verhouding other than zero. In my example, PersonID 16500 is such a case. In such a case, I also want the verhouding_flag to be 1, when Verhouding is 0. So expected result would be this:
| PersoonID | PersoonID | StartDate | EndDate | Verhouding | verhouding_flag |
|---|---|---|---|---|---|
| 16525 | 4 | 2023-01-01 | 2023-05-01 | 32 | 1 |
| 16573 | 5 | 2023-04-01 | 2023-05-01 | 78 | 1 |
| 16500 | 1 | 2022-05-01 | 2023-05-01 | 34 | 1 |
| 16500 | 1 | 2023-05-01 | 2023-05-01 | 0 | 1 |
| 16356 | 1 | 2023-01-01 | 2023-05-01 | 0 | 0 |
I could find those special cases by for example Grouping on PersoonID, AttributeID and StartDate, and see which groups have a size larger than 1 (Which means they have multiple entries for the 'Verhouding' column).
However, I don't know how to combine a solution like that with a CASE WHEN? Any help would be appreciated.
You can use max() window function here:
select t.*,
Max(case when Verhouding != 0 then 1 else 0 end) over(partition by PersoonID) verhouding_flag
from t;