I have a table of sprint info for multiple engagements, including the sprint start and end dates as separate fields. I understand how to determine if a date is within those start and end dates--in this case, to show if a holiday falls within a given sprint. I am struggling with how to do the above by accessing a table of holiday dates--I can't figure out how to do the JOIN or use other commands so the comparison will look at every date in both tables. Table 1 - Holidays Short DateHolidayCountry01 Jan 2023 New Year's DayMexico01 Jan 2023 New Year's DayBrazil06 Feb 2023 Constitution DayMexico21 Feb 2023 CarnivalBrazil Table 2 - Sprint Info SprintStart DateEnd DateHolidaysSprint 102 Jan 2023 13 Jan 2023 Sprint 216 Jan 2023 27 Jan 2023 Sprint 330 Jan 2023 10 Feb 2023 Sprint 413 Feb 2023 24 Feb 2024 I would like to compare Table 1 to Table 2 and indicate in Table 2 "Holidays" the number of holidays that occur on or between the Start Date and End Date. A simple count of number of occurrences is the basic need. Extra credit to show how to indicate holiday count by country, resulting in something like this: SprintStart DateEnd DateHolidaysSprint 102 Jan 2023 13 Jan 20231 Mexico 1 Brazil Sprint 216 Jan 2023 27 Jan 2023 0Sprint 330 Jan 2023 10 Feb 20231 Mexico Sprint 413 Feb 2023 24 Feb 20241 Brazil

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Finding if a table of dates is within date range in another table

Scott Gillespie_ibixpx

I have a table of sprint info for multiple engagements, including the sprint start and end dates as separate fields. I understand how to determine if a date is within those start and end dates--in this case, to show if a holiday falls within a given sprint.

I am struggling with how to do the above by accessing a table of holiday dates--I can't figure out how to do the JOIN or use other commands so the comparison will look at every date in both tables.

Table 1 - Holidays

Short Date	Holiday	Country
01 Jan 2023	New Year's Day	Mexico
01 Jan 2023	New Year's Day	Brazil
06 Feb 2023	Constitution Day	Mexico
21 Feb 2023	Carnival	Brazil

Table 2 - Sprint Info

Sprint	Start Date	End Date
Sprint 1	02 Jan 2023	13 Jan 2023
Sprint 2	16 Jan 2023	27 Jan 2023
Sprint 3	30 Jan 2023	10 Feb 2023
Sprint 4	13 Feb 2023	24 Feb 2024

I would like to compare Table 1 to Table 2 and indicate in Table 2 "Holidays" the number of holidays that occur on or between the Start Date and End Date.

A simple count of number of occurrences is the basic need. Extra credit to show how to indicate holiday count by country, resulting in something like this:

Sprint	Start Date	End Date	Holidays
Sprint 1	02 Jan 2023	13 Jan 2023	1 Mexico 1 Brazil
Sprint 2	16 Jan 2023	27 Jan 2023	0
Sprint 3	30 Jan 2023	10 Feb 2023	1 Mexico
Sprint 4	13 Feb 2023	24 Feb 2024	1 Brazil

Accepted answers

Katerina Rudkovskaya _Stiltsoft_

Hi @Scott Gillespie,

We can suggest trying the following SQL query within the Table Transformer macro:

Tue 5-1.png

SELECT *,
(SELECT COUNT(*) FROM T2
WHERE T2.'Holiday' >= T1.'Start date' AND T2.'Holiday' <= T1.'End date')
AS 'Number of Holidays'
FROM T1

Tue 5-2.png

The date format for our example is set as “dd M yy”:

tue 5-3.png

And here is the second more complex variant from our developers:

Tue 6-1.png

SELECT 'Sprint', 'Start date', 'End date',
FORMATWIKI(SUM('Count' + " " + 'Country' + "\n")) AS 'Holidays'
FROM (
SELECT T1.*, T2.'Country', COUNT(T2.'Country') AS 'Count'
FROM T1 LEFT JOIN T2 ON T2.'Holiday' >= T1.'Start date' AND T2.'Holiday' <= T1.'End date'
GROUP BY T1.'Sprint', T2.'Country')
GROUP BY 'Sprint', 'Start date', 'End date'

Tue 6-2.png

Hope this helps your case.

All comments

Katerina Rudkovskaya _Stiltsoft_

Scott Gillespie_ibixpx

This is truly brilliant! I am certain I could have spent days researching and learning and would not have figured out all the pieces to make this work.

Thank you very much!

One last question, if I may. I simplified the sample tables for this inquiry. T1 has several other fields I need to include in the final result, but those fields are empty using your SQL solution. I am sure it has to do with the final SELECT and JOIN statements, but I have been unable to hit on the right change to resolve this. I thought the "SELECT T1.*" meant all fields would be picked up, but that obviously isn't the case. All records do get listed with holiday counts correctly displayed per your solution, but all the other fields are blank.

In my SQL excerpt below, Engagement, Sprint Duration, Ready for Billing?, and Invoice Approval Status are in T1 along with the Sprint, Start date, and End date.

SELECT

'Sprint', 'Engagement', 'Start date', 'End date', 'Sprint Duration',
FORMATWIKI(SUM('Count' + " " + 'Country' + "\n")) AS 'Holidays',
'Ready for Billing?',
'Invoice Approval Status'

Scott Gillespie_ibixpx

Possible Solution: I amended the final GROUP BY statement and now all fields display as desired. However, this seems a bit inelegant and possibly inefficient. I can certainly live with this as it works, but would love to know if there's a better way.

FROM (
SELECT T1.*, T2.'Country', COUNT(T2.'Country') AS 'Count'
FROM T1 LEFT JOIN T2 ON T2.'Holiday' >= T1.'Start date' AND T2.'Holiday' <= T1.'End date'
GROUP BY T1.'Sprint', T2.'Country')
GROUP BY 'Sprint', 'Engagement', 'Start date', 'End date', 'Sprint Duration (Workdays)', 'Ready for Billing?', 'Invoice Approval Status and Comments'

Katerina Rudkovskaya _Stiltsoft_

Yes, you are right - don't forget to list your columns after the GROUP BY function:

SELECT 'Sprint', 'New column 1', 'New column 2', 'Start date', 'End date',
FORMATWIKI(SUM('Count' + " " + 'Country' + "\n")) AS 'Holidays'
FROM (
SELECT T1.*, T2.'Country', COUNT(T2.'Country') AS 'Count'
FROM T1 LEFT JOIN T2 ON
T2.'Holiday' >= T1.'Start date' AND T2.'Holiday' <= T1.'End date'
GROUP BY T1.'Sprint', T2.'Country'
)
GROUP BY 'Sprint', 'New column 1', 'New column 2', 'Start date', 'End date'

Wed 5-1.png

And please note that if you need to share some confidential data, you may always refer to our support portal that is based on Jira Service Desk and that is secure.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

server

confluence-server

filter

table

table-filter

table-filter-and-charts

table-transformer