Table Transformer: How to get all rows within a certain time range starting from today?

[Eishle]

Hi,

I have a table with public holidays for the next couple of years. As I want to display future public holidays for a certain time range only, I'm trying (and crying) to create a SELECT statement which returns all dates from today to six months in the future.

With this SELECT I can display all rows from today:

SELECT
CASE
WHEN 6 - WEEKDAY('date') = 2 OR WEEKDAY('date') - 1 = 1 THEN CONCAT('name', " + ", "Brückentag")
ELSE name
END AS 'Feiertag', 'date' AS 'Datum', 'scope' AS 'Geltungsbereich', WEEKDAY('date') AS 'Wochentag'
FROM T1
WHERE CAST('date' AS DATE) > GETDATE()
ORDER BY 'date' ASC

To get just the dates for the coming six months, I tried, amongst others, the following solutions which all returned 0 rows:

• WHERE CAST('date' AS DATE) BETWEEN GETDATE() AND DATEADD(month, -6, getdate())
• WHERE CAST('date' AS DATE) BETWEEN GETDATE() AND DATE_ADD(GETDATE(), INTERVAL 6 MONTH)
• WHERE 'date' < DATEADD(month, 6, GETDATE())
• WHERE CAST('date' AS DATE) < DATEADD(month, 6, GETDATE())

I also tried to find all rows until the end of the current year but this only worked with a hardcoded "2023-01-01".

Any ideas for an nice solution?

Thanks,

Eishle

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @Eishle ,

I can suggest using the Table Filter macro for the case:

Make sure that your date format corresponds the format that you are using in your source table:

You can use various expressions (-6m, 1w, today, etc.) and hide the filtration panel if necessary.

If your original case is a more complex one and you still need help, please raise a support ticket. The portal is confidential, so provide us with the screenshots of the source tables (with visible headers) and macro settings and we'll try to help you.

[Eishle]

Hi @Katerina Kovriga _Stiltsoft_,

Thanks for your quick reply. If I set the range with this macro, would it serve as a "preset" for other page members or would they need to set it by themselves?

The use case is not complicated. That's why I was surprised that my solution didn't work.

At the moment, I use "LIMIT" in combination with "WHERE CAST('date' AS DATE) > GETDATE()" which is not perfect but comes quite close to the wanted effect.

[Katerina Kovriga _Stiltsoft_]

If you save this filter, it will be set for all the page visitors (it works like any other simple dropdown or date range filter).

The "3m" and other dynamic filters were introduced specifically for the cases that are similar to yours (when there are manually created and automatically generated by the Page Properties Report, Jira Issues, etc. tables that need to show up-to-date information without any routine manual updates).

[Katerina Kovriga _Stiltsoft_]

And what concerns the Table Transformer macro, it is based on the AlaSQL library. So some functions may be limited and require a workaround.

[Eishle]

Thanks. I tried it and eventually saw that the table will be wrapped by a filter macro after clicking the buttons in the page view mode.