Dynamic date in the WHERE clause

[CK_WAKE]

Hi There,

I have the following SQL query, which retrieves data for the last 5 years based on the snapshot taken at the start of the financial year. I would like to make it dynamic so that I don't have to modify the code next year.

I would greatly appreciate your guidance on how to achieve this.

SQL_EXTRACT:

SQL

WITH sample_data AS (

    SELECT id,

        1 AS count

    FROM table

    WHERE TO_DATE('2023-07-01', 'YYYY-MM-DD') BETWEEN EFEC_STRT_DT AND EFEC_END_DT

    UNION ALL

    SELECT id,

        1 AS count

    FROM table

    WHERE TO_DATE('2022-07-01', 'YYYY-MM-DD') BETWEEN EFEC_STRT_DT AND EFEC_END_DT

    UNION ALL

    SELECT id,

        1 as count

    FROM table

    WHERE TO_DATE('2021-07-01', 'YYYY-MM-DD') BETWEEN EFEC_STRT_DT AND EFEC_END_DT

    UNION ALL

    SELECT id,

        1 as count

    FROM table

    WHERE TO_DATE('2020-07-01', 'YYYY-MM-DD') BETWEEN EFEC_STRT_DT AND EFEC_END_DT

    SELECT *

    FROM sample_data;

[steeefan]

Do you still need the last 5 years next year or would that then be 6 years?

[CK_WAKE]

Hi, I would like to keep only last 5 years.

[steeefan · Accepted solution]

This script generates the relevant dates for the last 5 years (incl. the current one if on or after 01.07.) and then gets the matching data from the table:

Data:
NOCONCATENATE LOAD * INLINE [
  id
];

// Detect if current date is after of before 01.07. of the year
// Based on that, set max. year
LET vMaxYear = Year(Today()) + (Today() < MakeDate(Year(Today()), 7, 1));
TRACE Max. year is $(vMaxYear);

FOR i = 0 TO 4

  LET vDate = Date(MakeDate($(vMaxYear) - $(i), 7, 1), 'YYYY-MM-DD');
  TRACE $(vDate);

  CONCATENATE (Data) LOAD
    *;
  SELECT
    id,
    1 AS count
  FROM
    table
  WHERE
    TO_DATE('$(vDate)', 'YYYY-MM-DD') BETWEEN EFEC_STRT_DT AND EFEC_END_DT;

NEXT i;

[CK_WAKE]

Thanks for the help, it worked.