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

[Scott Gillespie]

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	Holidays
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

[Katerina Rudkovskaya _Stiltsoft_ · Accepted solution]

Hi @Scott Gillespie,

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



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



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



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

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'



Hope this helps your case.

[Scott Gillespie]

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'

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'

[Scott Gillespie]

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'



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.

[Scott Gillespie]

Thanks again, Katerina, for this and all your previous expert help.  I did have the fields explicitly stated in both areas.  I thought there might be a more concise way to set the code for their display but looks like this is it.