Example SQL Queries (when running on an external database)
Last modified on 20 June 2016 02:28 PM
|
|
Example SQL Queries (when running on an external database)When running PaperCut on one of the supported external databases SQL queries may be run to find specific data or produce reports beyond the many standard reports available. Below is a collection of SQL select statements that may assist when writing a custom query. Any Supported External Database (standard SQL)Print logsExtended print log details, one line per job.
SELECT u.user_id, u.user_name, u.full_name AS user_full_name, p.server_name AS printer_server_name, p.printer_name, p.display_name AS printer_display_name, p.location AS printer_location, pul.printer_usage_log_id, pul.usage_date, pul.usage_day, pul.used_by_user_id, pul.charged_to_account_id, pul.usage_cost, pul.usage_allowed, pul.printer_id, pul.job_id, pul.document_name, pul.client_machine, pul.total_pages, pul.total_sheets, pul.copies, pul.paper_size, pul.paper_height_mm, pul.paper_width_mm, pul.printer_language, pul.document_size_kb, pul.denied_reason, pul.duplex, pul.gray_scale, pul.printed, pul.cancelled, pul.refunded, pul.assoc_with_account_id, pul.total_color_pages, pul.color_pages_estimated, pul.job_type, pul.invoiced, pul.job_comment, pul.protocol, pul.original_usage_cost, pul.refund_status, pul.refund_request_id FROM tbl_printer_usage_log pul JOIN tbl_printer p ON pul.printer_id = p.printer_id JOIN tbl_user u ON pul.used_by_user_id = u.user_id; Database Specific (uses specific functions)Summary by month by departmentThe number of pages printed by each department each month.
Microsoft SQL ServerSELECT u.department "Department", Month(pul.usage_date) "Month", SUM(pul.usage_cost) "Cost", COUNT(pul) "Jobs", SUM(pul.total_sheets) "Sheets", SUM(pul.total_pages) "Pages", SUM(pul.total_color_pages) "Color Pages", SUM(pul.total_pages - pul.total_color_pages) "Grayscale Pages" FROM tbl_printer_usage_log pul JOIN tbl_user u ON pul.used_by_user_id = u.user_id GROUP BY u.department, Month(pul.usage_date) ORDER BY u.department, Month(pul.usage_date); PostgreSQLSELECT u.department "Department", date_part('month', pul.usage_date) "Month", SUM(pul.usage_cost) "Cost", COUNT(pul) "Jobs", SUM(pul.total_sheets) "Sheets", SUM(pul.total_pages) "Pages", SUM(pul.total_color_pages) "Color Pages", SUM(pul.total_pages - pul.total_color_pages) "Grayscale Pages" FROM tbl_printer_usage_log pul JOIN tbl_user u ON pul.used_by_user_id = u.user_id GROUP BY u.department, date_part('month', pul.usage_date) ORDER BY u.department, date_part('month', pul.usage_date); | |
|