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

Eishle May 15, 2022

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

1 answer

1 accepted

4 votes
Answer accepted
Katerina Kovriga {Stiltsoft}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 16, 2022

Hi @Eishle ,

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

Mon 6-1.png

Mon 6-2.png

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

Mon 6-3.png

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 May 16, 2022

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}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 16, 2022

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}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 16, 2022

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 May 16, 2022

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.

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
TAGS
AUG Leaders

Atlassian Community Events