If you want to see the non-production time of your staff members you can do that with our SQL integration.You can insert the SQL-Query below and insert it into your data visualization software or your business intelligence tool. Then, you can measure the project time vs. attendance time.

Here you can learn how to add the SQL integration: Learn more

Please note that:
1. Attendance time module must be active learn more
2. Attendance time is must not be synced with project time learn more

If you are using Google Data Studio you can see here an example where to insert the SQL-Query:

MySQL Query:
Please note the last two columns of the SQL-Query where you can add filter by dates and filter by user id. Also it might be that some tools have problems to read comments on the SQL query. Please try to delete them if an error occurs.

SELECT 
	calendar.dt,
	u.first_name,
	u.last_name,
	ifnull(at_time.work_hours + at_time.break,0) as attendance_total_time,
	ifnull(at_time.work_hours,0) as attendance_work_time,
	ifnull(at_time.break,0) as attendance_break_time,
	ifnull((select sum(hours) from zst_time_sheet_tbl where log_date = calendar.dt and user_id = u.id),0) as total_project_hours,
	ifnull((select sum(hours) from zst_time_sheet_tbl where log_date = calendar.dt and user_id = u.id and document_status_id = 29),0) as non_billable_hours,
	ifnull((select sum(hours) from zst_time_sheet_tbl where log_date = calendar.dt and user_id = u.id and document_status_id <> 29),0) as billable_hours
FROM 
	zst_calendar_tbl as calendar 
	cross join zst_user_tbl as u
	left join zst_attendance_time_accumulation_tbl as at_time on
		at_time.user_id = u.id and at_time.dt = calendar.dt
where
	u.vendor_id is null
	and u.customer_id is null
	and calendar.dt between '2020-03-16' and '2020-03-22' -- filter by dates
	and u.id = 1 -- filter by user_id

Widgets

On the list view you see following data as widgets:

• Working time: On the left side Attendance time (clock-in till clock-out) minus breaks, and on the right side your working plan for the selected week
• Time Off: Absences, leave types and vacation for the selected week.
• Flex-Time Balance: Your current flex-time balance. This value is always the total value.

Date Selection

Switch to other weeks by either use the arrow controls besides the time period, or click on the time period to open the date picker. if click on today you get to the current week.

Table

On the table you can see for each day your working time, break time and if a time record is not payable (outside of your work time rules, learn more here). Also, you see time off per day. Days that are nor declared as working days on your work time rules are shown with opacity.

When adding a time record you have to add clock-in and clock-out time. Notes/comments are optional. By default any time record is on-site, you can change to off-site if needed.

On the line-end you can add short breaks (if active), add/edit notes or deleted the time record.

Bulk Actions

When selecting more than one day, you can delete or add attendance time for several days.

If there are overlapping time entries with existing records you have to either remove the existing time record or change your input.

Change User

As an admin you can change the user on the attendance list view. This allows you to read, create, update and delete time records for other users.

Mark Time Record as Payable

Time records outside of a user’s work time rule are not payable by default and are not calculated to the flex-time balance. As a admin you can mark those time records as payable. Click on time that is not payable, after on “mark as payable”.

After a time record was marked as payable there’s a hint. You can unmark it if needed.