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

Question

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.

Answer 1

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