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

We’re using the MySQL Workbench in the lessons.

Customers and Invoices

Learn how to create a custom query to get the customer ID, name, invoice number and invoice amount. Also we sort customers by name and group data by currency.

Push data immediately, filter by date and export to Excel

Learn how to immediately push data into the SQL Warehouse, filter data by date and export everything to Excel. Make sure you watched the video above first, since it uses the query from that lesson.

Build a report with approved timesheets on Google Data Studio

Learn how to build a query to get approved timesheets only, including project name, customer name, task and staff. After that we build a report on Google Data Studio. If you try it by yourself, please make sure that you using approve timesheet feature.