Check if date is within date range

[JNWT]

Hi I need some help here.

I have a table A containing start_date and end_date, and a table B containing list of dates. What I want to do is to check between the start_date and end_date of table A, does it contain any of the dates listed in table B. If yes, count the number of dates in B that meet the criteria, else return 0

start_date	end_date	Count the date
20/4/2018	23/4/2018	2
21/4/2018	25/4/2018	3
20/4/2018	21/4/2018	1
25/4/2018	28/4/2018	0

date

20/3/2018

27/3/2018

30/3/2018

21/4/2018

22/4/2018

24/4/2018

How can I get this?

Please help. Thanks!

[ranman256]

make Q1, bring in both tables, do NOT join them
fields: bring in t1.startdate ,Enddate and t2.Date
in criteria under t2.Date, say: between t1.startdate and t1.Enddate
run

make Q2, count items in Q1.

[JNWT]

its working!

Thanks so much for the help!

[isladogs]

Just to say it can be done in one query

I've changed the Date field in T2 to Event_Date as Date is a reserved word

As Ranman said put both tables in a query but don't join them
Add your filter, click the Totals button to create an aggregate query
Add Event Date again and select Count in the Group By row

SELECT Count(T2.Event_Date) AS CountOfEvent_Date, T1.Start_Date, T1.End_Date
FROM T1, T2
WHERE (((T2.Event_Date) Between [Start_Date] And [End_Date]))
GROUP BY T1.Start_Date, T1.End_Date;