Question Details

No question body available.

Tags

sql dateinterval hana-sql-script amdp

Answers (4)

Accepted Answer Available
Accepted Answer
July 4, 2025 Score: 3 Rep: 7,653 Quality: High Completeness: 80%

LAG() won't work if let's say interval B is included in interval A, e.g A: 0101-0131, B: 0115-0118, C: 0120-0228: C's LAG() is B, but there's a hole between both, while, had C looked for A, it would have seen they overlap.

What I generally use is slice each interval into subintervals (cut on each start or end of an overlap with another interval), then ensure that at least one interval covers each slice of your period of interest.

So for your group 3:

groupid validfrom validto 1 → 12 13 → 15 16 → 20 21 → 30
3 20250201 20250215 X X
3 20250213 20250220 X X
3 20250221 20250230 X
3 20250221 20250230 1 2 1 1 ← count() over the group

Small detail: we have to introduce our looked up interval's ends (0201 and 0230) into each group, so that in case a group's first interval starts on 0210 we count an empty slice from 0201 to 0209 (and get a row with a COUNT of 0 for this slice) instead of having no slice to count (and get no row for this slice, thus forget to signal it).
To alleviate our algorithm, we introduce it as an interval of the group, so that it gets sliced like any other interval of DBGROUP.
Thus we finally won't look for a count of at least 1, but a count of at least 2 intervals (our reference one, and a true interval from DBGROUP) to overlap over each slice.

WITH
  -- What period are we interested in?
  fromto AS
  (
    SELECT
      CAST('20250201' AS DATE) validfrom,
      CAST('20250228' AS DATE) validto
    FROM dummy
  ),
  -- Groups over that period:
  gr AS
  (
    SELECT
      groupid,
      GREATEST(i.validfrom, ft.validfrom) validfrom,
      LEAST(i.validto, ft.validto) validto
    FROM DBGROUP i
    -- Just keep the values that overlap our period of interest:
    JOIN fromto ft ON i.validfrom = ft.validfrom
    -- Have our period of interest get a fictive presence in each group:
    UNION ALL
    SELECT DISTINCT groupid, ft.validfrom, ft.validto FROM fromto ft, DBGROUP
  ),
  cuts AS
  (
    SELECT groupid, validfrom AS d FROM gr
    UNION -- Not UNION ALL: we want only one row for identical dates.
    SELECT groupid, adddays(validto, 1) FROM gr -- An interval ending on day D covers until the next interval starting on day D+1
  ),
  slices AS
  (
    SELECT gr.groupid, cuts.d, COUNT(1) noverlaps
    FROM cuts JOIN gr ON gr.groupid = cuts.groupid AND cuts.d >= validfrom AND cuts.d < validto
    GROUP BY gr.groupid, cuts.d
  )
SELECT groupid
FROM slices
GROUP BY groupid
HAVING MIN(noverlaps) > 1;

And we get the expected:

groupid
1
2
3
4

(with a PostgreSQL equivalent fiddle to see it run, add more corner cases, and select from the intermediate CTEs to get a glance into the steps)

July 7, 2025 Score: 2 Rep: 8,669 Quality: Low Completeness: 80%

Groups, covering target interval, can be found by recursive query.

Anchor part is rows, covering the beginning ("istart") of the target interval.
End of anchor interval is end ("r.validto") of this rows.

In recursive part we move forward "r.validto" (t.validto->r.validto) , adding rows, covering end of ("r.validto") current interval (r.validfrom->r.validto).
End of recursion is when last validto is greater or equal r.iend.

With main query, we get groupid's, overlapping target interval.

WITH RECURSIVE 
  params as(select cast('20250201' as date) istart, cast('20250228' as date) iend)
,r as(
  select distinct 0 lvl,groupid, validfrom, validto, istart, iend
  from dbgroup t,params p
  where validfrom=istart
  union all
  select lvl+1 lvl,r.groupid, r.validfrom, t.validto, istart, iend
  from r inner join dbgroup t on t.groupid=r.groupid 
    and t.validfromr.validto
  where r.validto=iend
order by groupid;
group_id
1
2
3
4

fiddle

I can't test recursive query in HANA. But still, an example might be useful. This includes the use of hierarchical HANA functions.

July 7, 2025 Score: 1 Rep: 5,541 Quality: Low Completeness: 80%

An alternative to the slicing is the merging of intervals: it may have the advantage of less full scans of DBGROUP.

https://dbfiddle.uk/7JQmtgem

WITH tocover(testid, datefrom, dateto) AS (
    SELECT 1, date '20250201', date '20250228' UNION ALL
    SELECT 2, date '20250131', date '20250228'
)
SELECT 
    testid, groupid
FROM (
    SELECT 
        groupid,
        -- ideally we should use LASTVALUE(newperioddatefrom IGNORE NULLS) OVER(PARTITION BY groupid ORDER BY validfrom) validfrom,
        -- but Postgresql doesn't support "IGNORE NULLS"
        MAX(newperioddatefrom) OVER(PARTITION BY groupid ORDER BY validfrom) validfrom,
        validto
    FROM (
        SELECT 
            groupid,
            validfrom,
            validto,
            -- LNNVL: returns TRUE if the condition is FALSE or UNKNOWN and FALSE if the condition is TRUE
            CASE WHEN COALESCE(NOT(validfrom - 
                MAX(validto) OVER(PARTITION BY groupid ORDER BY validfrom
                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
                ) = GREATEST(d.validfrom, c.datefrom)
    ) d
) mg
-- be sure they fully cover the requirement
JOIN tocover c
    ON GREATEST(mg.validfrom, c.datefrom) = c.datefrom
        AND
        LEAST(mg.validto, c.dateto) = c.dateto
GROUP BY testid, groupid
ORDER BY testid, groupid
;
July 7, 2025 Score: 1 Rep: 59 Quality: Low Completeness: 100%

I took a different approach on this interesting problem. We have 2 parts to this problem :

  1. Is to identify continuous interval in a group and create a custom partition to keep continuous interval together

  2. And on the continuous interval (that we got from custom partition) find the groups that falls into the continuous interval

1st part is the complex one, what I am essentially doing is looking at the all the previous validto for the group and selecting max from that, this will give me the max validto after which my next interval could start and taking the difference from current row's validfrom , so basically, current row validfrom - max(all previous validto) , this will give me 3 possible values:

  1. a -ve value : Indicating intervals are overlapping and should be in continuation

  2. a +ve value < 2 : Indicating current interval just started 1 day after previous max interval, so in continuation

  3. a +ve value >= 2 : Indicating current interval is starting 2 or more days after previous max interval

Using above 3 points we can create a custom partition inside each group for continuous interval. Below is the code snippet for looking back:

select *, 
max(validto) over (partition by groupid order by validfrom rows between unbounded preceding and 1 preceding) as precedingdate,
validfrom - max(validto) over (partition by groupid order by validfrom rows between unbounded preceding and 1 preceding) as diff from groupintervals

Code snippet for creating custom partition:

select  *, 
sum(case when coalesce(diff, 0)