Occurences between 2 dates

[Bernie]

I have a start and end date in one table, another table with a list of dates
in the month.
I need to know how many instances the item was active on a given date in
another table such as
1/1/09 - 5
1/3/09 - 4
1/6/09 - 1
Start End
1/1/2009 1/2/2009
1/1/2009 1/3/2009
1/1/2009 1/6/2009
1/1/2009 1/5/2009
1/1/2009 1/3/2009

Tried this in the query
iif(count( ([MinMax]![MinOfARR_FLT_ORIG_DT] >= [Calendar]![Date]) and
([MinMax]![MaxOfARR_FLT_ORIG_DT] <= [Calendar]![Date] ))
No luck

Thanks Bernie

 

[Dale Fye]

Bernie,

1. When you post here , please provide more information than I have two
tables with dates. Give us the table names and the appropriate field names.
It prevents us from having to make assumptions. It means that it will take
you longer to write your question, but will make it significantly easier on
us, and usually will result in fewer back-and-forth posts for clarification.

2. Assuming Table1 is your table of dates, and it contains a field
[SomeDate], and Table2 is the table that contains the [StartDate] and
[EndDate] fields, then the following should work:

SELECT Table1.SomeDate, Count(Table2.StartDate) as Frequency
FROM Table1, Table2
WHERE Table1.SomeDate >= Table2.StartDate
AND Table1.SomeDate <= Table2.EndDate
GROUP BY Table1.SomeDate

Note that this will not give you those dates where there are no matches in
Table2 for a date in Table1.

Also, if there are any NULL values in StartDate or EndDate in Table 2, then
I think those records will not be counted.

HTH
Dale

 

[Bernie]

Dale,
Thanks
Table 1 = "Calendar" (includes all days of the year)
Table 2 = "Events" with Start & End date fields for each event. This table
won't necessarily have every day in "Calendar".

Need results to show all of "Calendar" Table records with count of Events
open on any day

Tried the following
SELECT Calendar.Date, Count(Start_Date) AS Frequency
FROM Events, Calendar
WHERE (((Events.Start_Date)>= Calendar.Date) AND ((Events.End_Date)<=
Calendar.Date))
GROUP BY Calendar.Date
HAVING (((Calendar.Date) Between #12/1/2008# And #12/31/2008#));


Returns nothing, Suggestion??

Thanks Bernie

Bernie,

1. When you post here , please provide more information than I have two
tables with dates. Give us the table names and the appropriate field names.
It prevents us from having to make assumptions. It means that it will take
you longer to write your question, but will make it significantly easier on
us, and usually will result in fewer back-and-forth posts for clarification.

2. Assuming Table1 is your table of dates, and it contains a field
[SomeDate], and Table2 is the table that contains the [StartDate] and
[EndDate] fields, then the following should work:

SELECT Table1.SomeDate, Count(Table2.StartDate) as Frequency
FROM Table1, Table2
WHERE Table1.SomeDate >= Table2.StartDate
AND Table1.SomeDate <= Table2.EndDate
GROUP BY Table1.SomeDate

Note that this will not give you those dates where there are no matches in
Table2 for a date in Table1.

Also, if there are any NULL values in StartDate or EndDate in Table 2, then
I think those records will not be counted.

HTH
Dale

I have a start and end date in one table, another table with a list of
dates
in the month.
I need to know how many instances the item was active on a given date in
another table such as
1/1/09 - 5
1/3/09 - 4
1/6/09 - 1
Start End
1/1/2009 1/2/2009
1/1/2009 1/3/2009
1/1/2009 1/6/2009
1/1/2009 1/5/2009
1/1/2009 1/3/2009

Tried this in the query
iif(count( ([MinMax]![MinOfARR_FLT_ORIG_DT] >= [Calendar]![Date]) and
([MinMax]![MaxOfARR_FLT_ORIG_DT] <= [Calendar]![Date] ))
No luck

Thanks Bernie

 

[John Spencer]

I might try the following

SELECT Calendar.Date, Count(*) as EventCount
FROM Calendar LEFT JOIN Events
ON Calendar.Date >= Events.StartDate
AND Calendar.Date <= Events.EndDate
WHERE Calendar.Date Between #2008-12-01# and #2008-12-31#
GROUP BY Calendar.Date


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Dale,
Thanks
Table 1 = "Calendar" (includes all days of the year)
Table 2 = "Events" with Start & End date fields for each event. This table
won't necessarily have every day in "Calendar".

Need results to show all of "Calendar" Table records with count of Events
open on any day

Tried the following
SELECT Calendar.Date, Count(Start_Date) AS Frequency
FROM Events, Calendar
WHERE (((Events.Start_Date)>= Calendar.Date) AND ((Events.End_Date)<=
Calendar.Date))
GROUP BY Calendar.Date
HAVING (((Calendar.Date) Between #12/1/2008# And #12/31/2008#));


Returns nothing, Suggestion??

Thanks Bernie

Bernie,

1. When you post here , please provide more information than I have two
tables with dates. Give us the table names and the appropriate field names.
It prevents us from having to make assumptions. It means that it will take
you longer to write your question, but will make it significantly easier on
us, and usually will result in fewer back-and-forth posts for clarification.

2. Assuming Table1 is your table of dates, and it contains a field
[SomeDate], and Table2 is the table that contains the [StartDate] and
[EndDate] fields, then the following should work:

SELECT Table1.SomeDate, Count(Table2.StartDate) as Frequency
FROM Table1, Table2
WHERE Table1.SomeDate >= Table2.StartDate
AND Table1.SomeDate <= Table2.EndDate
GROUP BY Table1.SomeDate

Note that this will not give you those dates where there are no matches in
Table2 for a date in Table1.

Also, if there are any NULL values in StartDate or EndDate in Table 2, then
I think those records will not be counted.

HTH
Dale

I have a start and end date in one table, another table with a list of
dates
in the month.
I need to know how many instances the item was active on a given date in
another table such as
1/1/09 - 5
1/3/09 - 4
1/6/09 - 1
Start End
1/1/2009 1/2/2009
1/1/2009 1/3/2009
1/1/2009 1/6/2009
1/1/2009 1/5/2009
1/1/2009 1/3/2009

Tried this in the query
iif(count( ([MinMax]![MinOfARR_FLT_ORIG_DT] >= [Calendar]![Date]) and
([MinMax]![MaxOfARR_FLT_ORIG_DT] <= [Calendar]![Date] ))
No luck

Thanks Bernie

 

[Dale Fye]

John, I think the WHERE clause will negate the left join, and I don't think
it is needed, given the nonequi-join.

SELECT Calendar.Date, Count(Events.StartDate) as EventCount
FROM Calendar
LEFT JOIN Events
ON Calendar.Date >= Event.StartDate
AND Calendar.Date <= Event.EndDate
GROUP BY Calendar.Date

Bernie,

You need the left join to ensure that you get all of the values from
Calendar, and only those from Events that meet the join clause criteria.

In order to create this type of join, you will have to use the SQL view, and
cannot actually view the query in the query grid. What I usually do is
start in the query grid with both the tables, then create a left join from
Calendar.Date to Event.StartDate and a left join from Calendar.Date to
Event.EndDate.

Finally, go to the SQL view, and change the equals sign in the two join
clauses to the way that you want them. Assuming you have 3 dates in
Calendar (1/1/09, 1/2/09, and 1/3/09) and have three records in Events, with
start/end dates of (12/31/08 - 1/2/09, 1/1/09-1/2/09, 1/1/09-1/1/09), what
this query does is creates as record set that looks something like:

Calendar.Date EventID EventDate
1/1/09 1 1/1/09
1/2/09 1 1/2/09
1/1/09 2 1/1/09
1/2/09 2 1/2/09
1/1/09 3 1/1/09
1/3/09 NULL NULL

And then, it will do the counts to return:

Calendar.Date EventCount
1/1/09 3
1/2/09 2
1/3/09 0

HTH
Dale

I might try the following

SELECT Calendar.Date, Count(*) as EventCount
FROM Calendar LEFT JOIN Events
ON Calendar.Date >= Events.StartDate
AND Calendar.Date <= Events.EndDate
WHERE Calendar.Date Between #2008-12-01# and #2008-12-31#
GROUP BY Calendar.Date


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Dale,
Thanks
Table 1 = "Calendar" (includes all days of the year)
Table 2 = "Events" with Start & End date fields for each event. This
table won't necessarily have every day in "Calendar".

Need results to show all of "Calendar" Table records with count of Events
open on any day

Tried the following
SELECT Calendar.Date, Count(Start_Date) AS Frequency
FROM Events, Calendar
WHERE (((Events.Start_Date)>= Calendar.Date) AND ((Events.End_Date)<=
Calendar.Date))
GROUP BY Calendar.Date
HAVING (((Calendar.Date) Between #12/1/2008# And #12/31/2008#));


Returns nothing, Suggestion??

Thanks Bernie

Bernie,

1. When you post here , please provide more information than I have two
tables with dates. Give us the table names and the appropriate field
names. It prevents us from having to make assumptions. It means that it
will take you longer to write your question, but will make it
significantly easier on us, and usually will result in fewer
back-and-forth posts for clarification.

2. Assuming Table1 is your table of dates, and it contains a field
[SomeDate], and Table2 is the table that contains the [StartDate] and
[EndDate] fields, then the following should work:

SELECT Table1.SomeDate, Count(Table2.StartDate) as Frequency
FROM Table1, Table2
WHERE Table1.SomeDate >= Table2.StartDate
AND Table1.SomeDate <= Table2.EndDate
GROUP BY Table1.SomeDate

Note that this will not give you those dates where there are no matches
in Table2 for a date in Table1.

Also, if there are any NULL values in StartDate or EndDate in Table 2,
then I think those records will not be counted.

HTH
Dale

I have a start and end date in one table, another table with a list of
dates
in the month.
I need to know how many instances the item was active on a given date
in
another table such as
1/1/09 - 5
1/3/09 - 4
1/6/09 - 1
Start End
1/1/2009 1/2/2009
1/1/2009 1/3/2009
1/1/2009 1/6/2009
1/1/2009 1/5/2009
1/1/2009 1/3/2009

Tried this in the query
iif(count( ([MinMax]![MinOfARR_FLT_ORIG_DT] >= [Calendar]![Date]) and
([MinMax]![MaxOfARR_FLT_ORIG_DT] <= [Calendar]![Date] ))
No luck

Thanks Bernie

 

[John Spencer]

No the where clause will not negate the left join. It would negate the
left join if the where criteria was being applied to the Events table.
As it is written, it should return one record for each date in the
specified range and a count of zero or more for ranges in Events table.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Bernie]

It works guy's
Thanks for the help

No the where clause will not negate the left join. It would negate the
left join if the where criteria was being applied to the Events table.
As it is written, it should return one record for each date in the
specified range and a count of zero or more for ranges in Events table.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Dale Fye]

John,

My bad! I wasn't paying close enough attention to the field you were using
in the Where clause.

Dale