I have 3 tables with sprint info including sprint start and end dates (T1), resource name list (T2) with other data fields like Role, etc., and holiday information by resource (T3). I need to find any holidays for a given resource that fall within the sprint start and end dates. Ideally, the result will be an updated T2 table listing each resource with a calculated column showing one or more holidays for that resource, if any.
Resource Holidays This Sprint Role
Name 1 08/14/23 Dev
Name 2 08/14/23, 08/15/23 Dev
Name 3 Lead
Name 4 08/15/23 QA
Edit: I figured out the solution with the updated SQL below, which puts multiple dates on same row, separated by commas. This works well enough, but I still have question on the separator--can I separate using line break/carriage return?
Earlier reading indicated this is not supported, but this Stiltsoft page suggests it is possible (see Adding line breaks section): https://docs.stiltsoft.com/tfac/cloud/advanced-table-cells-formatting-58426218.html I tried FORMATWIKI and FORMATMARKDOWN to use a line feed, but it doesn't work.
SELECT
T1.'Engagement',
T2.'Resource',
T2.'Role',
(SELECT GROUP_CONCAT(FORMATDATE(T3.'Holiday Converted Date', '%d %b %Y'))
FROM T3 WHERE T3.'Resource' = T2.'Resource'
AND T3.'Holiday Converted Date' BETWEEN T1.'Sprint Start Date' AND T1.'Sprint End Date' )
AS 'Holidays This Sprint',
T2.'Planned Dev Days',
T2.'Nights and WEs (Days)',
T2.'Holidays (Days)',
T2.'Actual Dev Days',
T2.'Comments'
FROM T1 JOIN T2 ON T2.'Engagement' = T1.'Engagement'
ORDER BY T2.'Resource'
