CODEWITHSUNDEEP
sqlcalendardynamics-crmdynamics-crm-2011
Philip Rich
Philip Rich

Reputation: 637

CRM 2011 - Set/Retrieve work hours programmatically

I am attempting to retrieve a resources work hours to perform some logic I require. I understand that the CRM scheduling engine is a little clunky around such things, but I assumed that I would be able to find out how the working hours were stored in the DB eventually...

So a resource has associated calendars and those calendars have associated calendar rules and inner calendars etc. It is possible to look at the start/end and frequency of aforementioned calendar rules and query their codes to work out whether a resource is 'working' during a given period. However, I have not been able to find the actual working hours, the 9-5 shall we say in any field in the DB.

I even tried some SQL profiling while I was creating a new schedule for a resource via the UI, but the results don't show any work hours passing to SQL. For those with the patience the intercepted SQL statement is below:-

EXEC Sp_executesql
  N'update [CalendarRuleBase] set [ModifiedBy]=@ModifiedBy0, [EffectiveIntervalEnd]=@EffectiveIntervalEnd0, [Description]=@Description0, [ModifiedOn]=@ModifiedOn0, [GroupDesignator]=@GroupDesignator0, [IsSelected]=@IsSelected0, [InnerCalendarId]=@InnerCalendarId0, [TimeZoneCode]=@TimeZoneCode0, [CalendarId]=@CalendarId0, [IsVaried]=@IsVaried0, [Rank]=@Rank0, [ModifiedOnBehalfBy]=NULL, [Duration]=@Duration0, [StartTime]=@StartTime0, [Pattern]=@Pattern0 where ([CalendarRuleId] = @CalendarRuleId0)',
  N'@ModifiedBy0 uniqueidentifier,@EffectiveIntervalEnd0 datetime,@Description0 ntext,@ModifiedOn0 datetime,@GroupDesignator0 ntext,@IsSelected0 bit,@InnerCalendarId0 uniqueidentifier,@TimeZoneCode0 int,@CalendarId0 uniqueidentifier,@IsVaried0 bit,@Rank0 int,@Duration0 int,@StartTime0 datetime,@Pattern0 ntext,@CalendarRuleId0 uniqueidentifier',
  @ModifiedBy0='EB04662A-5B38-E111-9889-00155D79A113',
  @EffectiveIntervalEnd0='2012-01-13 00:00:00',
  @Description0=N'Weekly Single Rule',
  @ModifiedOn0='2012-03-12 16:02:08',
  @GroupDesignator0=N'FC5769FC-4DE9-445d-8F4E-6E9869E60857',
  @IsSelected0=1,
  @InnerCalendarId0='3C806E79-7A49-4E8D-B97E-5ED26700EB14',
  @TimeZoneCode0=85,
  @CalendarId0='E48B1ABF-329F-425F-85DA-3FFCBB77F885',
  @IsVaried0=0,
  @Rank0=2,
  @Duration0=1440,
  @StartTime0='2000-01-01 00:00:00',
  @Pattern0=N'FREQ=WEEKLY;INTERVAL=1;BYDAY=SU,MO,TU,WE,TH,FR,SA',
  @CalendarRuleId0='0A00DFCF-7D0A-4EE3-91B3-DADFCC33781D'

The key parts in the statement are the setting of the pattern:-

    @Pattern0=N'FREQ=WEEKLY;INTERVAL=1;BYDAY=SU,MO,TU,WE,TH,FR,SA'

However, as mentioned, no indication of the work hours set.

Am I thinking about this incorrectly or is CRM doing something interesting around these work hours?

Any thoughts greatly appreciated, thanks.

Upvotes: 2

Views: 2711

Answers (1)

Jason Lattimer
Jason Lattimer

Reputation: 2848

If you look in the CalendarRuleBase table you should see a record with the data you gathered in your trace. You should also see another record created approximately the same time and it will have a CalendarId that equals the InnerCalendarId of the data from the trace. In this record there is a value - Offset which appears to represent the number of minutes past midnight for the start time. There is another value - Duration which appears to be the number of minutes of the shift.

I created work hours from 8-5. My offset was 480 (480/60 = 8) 8 AM start time and the duration was 540 (540/60 = 9) for a 9 hour shift.

Upvotes: 1

Related Questions