Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Do you still need the last 5 years next year or would that then be 6 years?
Hi, I would like to keep only last 5 years.
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;
Thanks for the help, it worked.