Need To Return Calendar Events For Current Day

[simonbingham]

Hello,

I have a calendar which includes seperate start and end date fields. I need to write a query that will return events for each day in a month, but I need to be mindful of the fact that some events span multiple days. Here is my query so far which isn't working quite right.

<cfquery name="qTodayEvents" datasource="#getDatasource()#">
SELECT eventID, title, summary, code, description
FROM events INNER JOIN eventTypes ON events.typeID=eventTypes.typeID
WHERE deleted=0
AND
(
  startDate >= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#todayStartTime#" />
  AND startDate <= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#todayEndTime#" />
) OR (
  endDate >= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#todayStartTime#" />
  AND endDate <= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#todayEndTime#" />
) OR (
  startDate <= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#todayStartTime#" />
  AND endDate >= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#todayEndTime#" />
)                               
</cfquery>

The query is located inside a loop which loops through the days in a given month.

Does anyone know what I am doing wrong? Any assistance would be appreciated.

Many thanks,

Simon

[Dan_Bracuk]

Running a query inside a loop is inefficient.  You can impove speed, and probably simplify things by running one query that gets all the data you need and then process it with cf.  The query would be something like this:

date1 = 1st day of the month you want.

date2 = 1st day of the following month

select startdate, enddate, other stuff

from your tables

where (startdate <= #date1# and enddate > #date1#)

or

(startdate >= #date1# and enddate < #date2#)

or

(startdate >= #date1# and startdate < #date2#)

Then you can do a couple of nested loops, one for the days of the month and one for the query, to get your events for each day.

[simonbingham]

Hi Dan,

Thanks for your reponse. Your point about my loop being inefficient is certainly valid so I will amend it.

What parameters would you suggest I use for returning events for a specific day in the calendar? This is where I am struggling.

Thank you for your help.

Simon

[Dan_Bracuk]

You have to do nested loops - one for the days of the month, and once for the query.  It probably does not matter which is nested inside of what.  You want your query ordered by EndDate.  Then you can do something like this.

ControlDate = Date1;

MaxDate = arraymax(yourquery["enddate"]);

<cfloop condition = ControlDate lt Date2>

<cfloop from = "1" to = yourquery.recordcount index = 1>

<cfif ControlDate >= StartDate and ControlDate <= EndDate>

your code goes here

<cfelse>

break

</cfif>

<cfloop>

add a day to ControlDate

maybe break if the control date is greater than the max date.

</cfloop>