Calculating Business Hours based on the Shift Times

• santoooo

  SSCommitted

  Points: 1999

  More actions

  April 29, 2008 at 2:57 am

  #71493

  HI,

  I want to Calculate Business Hours based on the Shift Timings, and it should not include holidays, and second saturday.

  Shift Timings and Holidays will be taken from the tables.

  please help me to solve this issue.

• G.R.Prithiviraj Kulasingham

  SSCarpal Tunnel

  Points: 4105

  More actions

  April 29, 2008 at 3:17 am

  #808197

  Hi,

  Your post is quite unclear on the details.

  It looks like you haven't desinged the database yet. IF so, give the requirements clearly.

  If you have done the design and your problem is with calculating, provide us with the structure.

  Cheers,
  Prithiviraj Kulasingham

  http://preethiviraj.blogspot.com/

• santoooo

  SSCommitted

  Points: 1999

  More actions

  April 29, 2008 at 4:07 am

  #808215

  Hi Preethiviraj,

  thank you for your reply,

  Actuall My requirement is to Calculate the User Performance i.e how much time he took to complete one task based on Business hours.

  For this i need to Consider,

  User Shift Timings,

  and i need not to inculde

  Sundays,

  Second Saturdays and

  Holidays,

  For Example

  SET @StartDate= '2008-01-11 12:00'

  SET @EndDate= '2008-01-15 16:30'

  SET @ShiftStartTime = '11:00'

  SET @ShiftEndTime = '20:00'

  In the above period(Start Date and EndDate)

  Consider 12th is the Second Saturday,

  13th Sunday and

  14th Monday is holiday.

  15th is Working day.

  so actually the user took

  8 hours on '2008-01-11'

  and 5:30 hours on '2008-01-15'

  totally 13:30 hours

  Like this i need to Calculate the no.of Business hours.

  please help me in this ...!!!

  Thanks in advance

  ---Santosh