Table Transformer SQL - displaying multiple matches

[Scott Gillespie]

I have 2 tables, T1 with 'Sprint Start Date' and 'Sprint End Date' and T2 with 'Holiday Short Date' and 'Holiday' fields. I want to compare the 2 tables, and find any holidays in T2 that fall between the Sprint Start Date and Sprint End Date.

If there is a match, a table should be displayed with the heading "Reminder: Supplier Holidays This Sprint" and a data field showing the holiday date "Holiday Short Date" and the holiday name 'Holiday'.

I thought this would be fairly simple but struggled to get correct results in every case. The following code was adjusted based on AI input, and worked almost correctly, except for 2 issues:

1. If more than one holiday found, commas appeared before every line after the first line.
2. If no holidays found, the text message "No holidays" was not displayed--the data cell was blank. However, if multiple holidays were found, they would be listed correctly on separate lines, but with the leading comma.

Oddly, the GROUP_CONCAT function was accepted even though it's not listed as a supported function for Table Transformer.

Original Solution

SELECT
CASE
WHEN COUNT(*) = 0 THEN "No holidays"
ELSE
FORMATWIKI(GROUP_CONCAT(CONCAT(FORMATDATE(T2.'Holiday Short Date', '%b %d, %Y') + ": " + T2.'Holiday' + "\n")))
END AS 'Reminder: Supplier Holidays This Sprint'
FROM T1
LEFT JOIN T2 ON T2.'Holiday Short Date' >= T1.'Sprint Start Date'
AND T2.'Holiday Short Date' <= T1.'Sprint End Date'
GROUP BY 'Reminder: Supplier Holidays This Sprint'

The alternate solution below resolved the comma issue and correctly displayed "No holiday" message, but then if more than one holiday match was found, only the first holiday would be displayed.

Alternate Solution

SELECT
CASE
WHEN (SELECT COUNT(*) FROM T2 WHERE T2.'Holiday Short Date' >= T1.'Sprint Start Date' AND T2.'Holiday Short Date' <= T1.'Sprint End Date') = 0 THEN "No holidays"
ELSE
FORMATWIKI(REPLACE((SELECT FORMATDATE(T2.'Holiday Short Date', '%b %d, %Y') + ": " + T2.'Holiday' + CHAR(10)
FROM T2
WHERE T2.'Holiday Short Date' >= T1.'Sprint Start Date'
AND T2.'Holiday Short Date' <= T1.'Sprint End Date'
ORDER BY T2.'Holiday Short Date')))
END AS 'Reminder: Supplier Holidays This Sprint'
FROM T1

I'm sure I'm hitting all around the solution, but cannot get it right. Other research I do suggests using various functions not supported by Table Transformer such as XML PATH, STUFF, and STRING_AGG. So, time to come back to the experts here for guidance. Thanks in advance for any help you can provide.

[Stiltsoft support · Accepted solution]

Hi @Scott Gillespie ,

It seems that the question is about the Table Filter, Charts & Spreadsheets for Confluence app (that is our plugin) and its Table Transformer macro.

If it is so and I've got your case correctly, you may try the following query:

SELECT 'Sprint',
CASE
WHEN (SELECT COUNT(*) FROM T2 WHERE T2.'Holiday Short Date' >= T1.'Sprint Start Date' AND T2.'Holiday Short Date' <= T1.'Sprint End Date') = 0 THEN "No holidays"
ELSE
FORMATWIKI((SELECT CONCAT_VIEW_AGGR(FORMATDATE(T2.'Holiday Short Date', '%b %d, %Y') + ": " + T2.'Holiday' + ", ")
FROM T2
WHERE T2.'Holiday Short Date' >= T1.'Sprint Start Date'
AND T2.'Holiday Short Date' <= T1.'Sprint End Date'
ORDER BY T2.'Holiday Short Date'))
END AS 'Reminder: Supplier Holidays This Sprint'
FROM T1

Hope it helps.