[SOLVED] Keep only groups in a SQL view that have at least one bigger value then a certain condition

Keep only groups in a SQL view that have at least one bigger value then a certain condition

I have SQL view data_start with 3 columns

CALMONTH, COSTCENTER, Kontonummer

Sample data:

CALMONTH COSTCENTER Kontonummer
-------------------------------
202301      A           AA
202302      A           AB
202101      B           BA
202012      B           BB
202204      C           CA
202312      C           CB
...

How can I keep only the rows per COSTCENTER group, which CALMONTH maximum is greater than a specific value.

So for example I only want to keep the COSTCENTER where at least one CALMONTH is greater than 202201.

The output should be:

CALMONTH COSTCENTER Kontonummer
-------------------------------
202301      A           AA
202302      A           AB
202204      C           CA
202312      C           CB
...

I tried this SQL statement:

SELECT 
    "CALMONTH", "COSTCENTER", "Kontonummer"
GROUP BY
    ("COSTCENTER") %>%
    SERIES_FILTER(max("CALMONTH") > 202201) %>%,
FROM "data_start"

But this results in an error:

Error Extraneous BY, expecting ‘,’, FROM

The SQL is in the SAP HANA CLOUD and called SQLSCRIPT, which has the syntax from mysql.

Solution

This can be done using group by and having :

First identify COSTCENTER having CALMONTH maximum is greater than 202201 :

  select COSTCENTER
  from data_start
  group by COSTCENTER
  having max(CALMONTH) > 202201

Then join this dataset with the table to get the expected output :

select t.*
from data_start t
inner join (
  select COSTCENTER
  from data_start
  group by COSTCENTER
  having max(CALMONTH) > 202201
) as s on s.COSTCENTER = t.COSTCENTER;

Demo on mysql here