Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
CK_WAKE
Creator
Creator

Dynamic date in the WHERE clause

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;
Labels (5)
1 Solution

Accepted Solutions
steeefan
Luminary
Luminary

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;

View solution in original post

4 Replies
steeefan
Luminary
Luminary

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

CK_WAKE
Creator
Creator
Author

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

steeefan
Luminary
Luminary

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
Creator
Creator
Author

Thanks for the help, it worked.