Hello Tim,

I am sorry but I don't quite understand about what you really need. Could you be more specific such as what exactly your scenario looks like? 

Regards,
Handy

I need to be able to retrieve a total count of hours that a specific resource has been scheduled for within a specific date range.

Let's say that I have a resource that has three events in the webscheduler that have a duration of 2.5 hours each during the week of 12/26/2010 and 1/1/2011 (total of 7.5 hours). That same resource also has a recurring event which has two events during that same week that have a duration of 3 hours each (total of 6 hours).

I would then query the database to get all hours that the specific resource was scheduled for during the date range of 12/26/2010 and 1/1/2011. The query should return 13.5 hours which is the total of all recurring events and events that was scheduled during that period of time for that specific resource.

I would like a count of total hours and a list of dates and hours that make up the total hour count.

Below is some tsql code that I have to query the Events table. The events table is simple to query but to get the same results from the Recurring Events table it is very complex due to all the options for range of the recurrence.

I want to know if you have some sample code to accomplish this for the Recurring Events or if you can help.

SELECT r.ID, r.ResourceName, c.CategoryName, e.StartTime, e.EndTime, ((CAST(DATEDIFF(MINUTE, e.StartTime, e.EndTime) AS DECIMAL))/60) AS HoursScheduled

FROM dbo.[Events] e        

     INNER JOIN dbo.Resources r ON e.ResourceID = r.ID

     INNER JOIN dbo.Categories c ON e.CategoryID = c.ID

Hello,

I need more time to discuss this scenario with our developer teams. Please bear with me.

Regards,
Handy

Hello Tim,

I think you would not need to calculate the start time and end time. You can use TotalRecurrence to have the total hour.

e.g

SELECT r.ID, r.ResourceName,re.Subject,  re.StartTime, re.EndTime,ri.TotalRecurrences ,((CAST(DATEDIFF(MINUTE, re.StartTime, re.EndTime) AS DECIMAL))/60) AS HoursScheduled, ri.TotalRecurrences *((CAST(DATEDIFF(MINUTE, re.StartTime, re.EndTime) AS DECIMAL))/60) AS TotalHours
FROM dbo.[RecurringEvents] re        
     INNER JOIN dbo.Resources r ON re.ResourceID = r.ID
     
     INNER JOIN dbo.RecurrenceInfo ri ON re.RecurrenceID = ri.ID
     
Where r.ID='1'

Regards,
Handy

This solution will not work for the need that I have. First I need only the total hours between a user specified date range and your solution appears to only calculate a total from beginning to end. Secondly this solution only works if the recurring event has been set to end after a set number of recurrences hence the use of TotalRecurrences field. This would not work for recurring events that have no end or are set to end on a specific date.

The recurring event has many options for setting the scope of the recurring event. Your solution only covers one. For instance if a recurring schedule is altered on one instance the code would need to be aware of that condition.

Do you have other solutions for this recurring events query?

Hello Tim,

Ok, I understand about your explanation.
However, I think it would be very difficult to query this recurring event in your scenario.
To create a recurring event, there are much options such as Daily, Weekly, Monthly, Yearly. Each of them have their own options and also the range options.

In range options there are also No End Date, End After X occurences, and End By.

If user choose After X occurences, you could not query based on EndDate because EndDate is always null.
If user choose End By, you can query based on EndDate, but you also need to calculate the the other options.
e.g Reccurence pattern "Daily". It has two options such as Every X days and Every week days.

If user choose Every X days, maybe you would not difficult to calculate it.
But if user choose every week days, you also need to reduce the calculation with not week days.

Regards,
Handy