Learning Center


learning-center-white

Reporting on "Regular Hours"

Posted by Pacific Timesheet Support on Mar 1, 2010 7:32:00 PM

What to do when regular hours include vacation, sick & other leave

Some organizations have a concept of "regular hours" that is different than the Pacific Timesheet "Regular Time" pay code hours. For instance, your company might consider "regular hours" to be all hours that are non-overtime work hours, plus any leave hours that are not 'Unpaid Time Off'. To report on employee "regular hours" you can create a new Employee Hours report on the Reports tab (click the New link to the right of the Employee Hours report). Then add the following SQL expression report column by clicking the Add SQL Expression column at the end of the columns list:

ISNULL(
  (SELECT SUM(te.hours) FROM t_timeentry as te
  LEFT OUTER JOIN t_user AS tu ON te.userid=tu.id
  LEFT OUTER JOIN t_paycode AS tp ON te.paycodeid=tp.id
  WHERE tu.fullname=t_user.fullname
  AND te.strt>=$P{startDate}
  AND te.strt<$P{finishDate}
  AND te.type='Leave'
  AND tp.name NOT IN ('Unpaid Time Off')),0)
+ISNULL(
  (SUM(t_timeentry.paycode1)),0)

The above expression adds all leave hours except for the one named 'Unpaid Time Off', as well as the regular time hours (t_timeentry.paycode1). You would change the name 'Unpaid Time Off' to whatever pay code name you use for non-paid work hours. The ISNULL function is a standard SQL function that will handle null values by returning zero. Note that the above expression has been tested with SQL Server but could require changes for MySQL or Oracle.


Subscribe Now

 

Recent Posts