Question numbers match their folders: folder 01 contains 01-01, 01-02 and so on. The starter dashboard uses 01-05, 01-08 and 01-02; process filter values come from 01-20. OBP identities and SQL filenames remain stable.
These English-language SQL variants preserve the source calculations and inclusion rules from the 8 November 2026 snapshot. Output aliases and display-category literals are translated into English. Replace {{schema}} with your authorized schema and run one question at a time. Dashboard and viewer filters are separate.
04-01 · Reported hours by starting week
Sum eligible closed time reports by the week in which reporting started.
Inspection hint: Duration is milliseconds. Includes durations greater than zero and up to 12 hours with an end date. Start is not separately required; parallel timers are not merged.
SELECT date_trunc('week',start) AS "Report-start week",sum(duration)/3600000.0 AS "Reported hours" FROM {{schema}}.timelogreport WHERE deletedat IS NULL AND "end" IS NOT NULL AND duration>0 AND duration<=43200000 GROUP BY 1 ORDER BY 1;04-02 · Reported hours by chapter
Compare eligible closed reported hours by chapter name.
Inspection hint: Groups by chapter name without a process identifier. Same-named chapters across processes can merge; missing names get the SQL fallback label.
SELECT coalesce(chaptername,'(no chapter)') AS "Chapter",sum(duration)/3600000.0 AS "Reported hours" FROM {{schema}}.timelogreport WHERE deletedat IS NULL AND "end" IS NOT NULL AND duration>0 AND duration<=43200000 GROUP BY 1 ORDER BY 2 DESC;04-03 · Reported hours by work order and SKU
Compare eligible closed report counts and hours for each order-number × SKU combination.
Inspection hint: Both measures use reports with duration greater than zero and up to 12 hours. Missing order or SKU can produce blank groups.
SELECT workordernumber AS "Order",sku AS "SKU",sum(duration)/3600000.0 AS "Reported hours",count(*) AS "Reports" FROM {{schema}}.timelogreport WHERE deletedat IS NULL AND "end" IS NOT NULL AND duration>0 AND duration<=43200000 GROUP BY 1,2 ORDER BY 3 DESC;04-04 · Time reports by record type
See all report counts by record type alongside hours from eligible closed reports.
Inspection hint: Counts include every report; hours include only closed durations greater than zero and up to 12 hours. Their populations differ.
SELECT recordtype AS "Record type",count(*) AS "Reports",sum(CASE WHEN "end" IS NOT NULL AND duration>0 AND duration<=43200000 THEN duration/3600000.0 END) AS "Valid hours up to12 hours" FROM {{schema}}.timelogreport WHERE deletedat IS NULL GROUP BY 1;04-05 · Time reports by recorded work type
See report counts and eligible closed hours by recorded work classification.
Inspection hint: The source does not decide which organizational work types are productive. Count and hour populations differ as in 04-04.
SELECT type AS "Recorded work type",count(*) AS "Reports",sum(CASE WHEN "end" IS NOT NULL AND duration>0 AND duration<=43200000 THEN duration/3600000.0 END) AS "Valid hours up to12 hours" FROM {{schema}}.timelogreport WHERE deletedat IS NULL GROUP BY 1 ORDER BY 2 DESC;04-06 · Time-report checks
Find how reports distribute across open timers, missing duration, non-positive duration, over 12 hours and closed up to 12 hours.
Inspection hint: The first matching CASE branch wins. Missing end is classified as open before duration is checked.
SELECT CASE WHEN "end" IS NULL THEN 'Open timer' WHEN duration IS NULL THEN 'Missing duration' WHEN duration<=0 THEN 'Nonpositive duration' WHEN duration>43200000 THEN 'Over 12 hours' ELSE 'Closed up to 12 hours' END AS "Report check category",count(*) AS "Reports" FROM {{schema}}.timelogreport WHERE deletedat IS NULL GROUP BY 1 ORDER BY 2 DESC;04-07 · Open timers
See the total number of reports without an end timestamp.
Inspection hint: This is an overall measure with no worker or period dimension. An open timer does not prove current attendance or activity.
SELECT count(*) AS "Open timers" FROM {{schema}}.timelogreport WHERE deletedat IS NULL AND "end" IS NULL;04-08 · Closed reports longer than 12 hours
See the total number of closed time reports whose stored duration exceeds 12 hours.
Inspection hint: The fixed threshold is a package rule, not an organization-specific shift rule. A long report is not automatically wrong.
SELECT count(*) AS "Reports over12 hours" FROM {{schema}}.timelogreport WHERE deletedat IS NULL AND "end" IS NOT NULL AND duration>43200000;04-09 · Reports whose end time changed
Count reports whose current end differs from the first stored end.
Inspection hint: This is not a full audit trail and does not identify the editor or the reason.
SELECT count(*) AS "Reports changed after closing" FROM {{schema}}.timelogreport WHERE deletedat IS NULL AND "end" IS NOT NULL AND firstend IS NOT NULL AND "end"<>firstend;04-10 · Time reports by approval status
Count time reports by recorded manager approval status.
Inspection hint: NULL is displayed as NOT_NEEDED. This does not prove a manager-approval process is configured or completed.
SELECT coalesce(managerapprovalstatus,'NOT_NEEDED') AS "Approval status",count(*) AS "Reports" FROM {{schema}}.timelogreport WHERE deletedat IS NULL GROUP BY 1 ORDER BY 2 DESC;05-01 · Saved form records by form
Count saved form records by form-definition name.
Inspection hint: The soft-delete filter applies to formdata; the joined form definition is not filtered the same way. A form is not necessarily a quality inspection.
SELECT coalesce(f.name,'(undefined form)') AS "Form",count(*) AS "Records" FROM {{schema}}.formdata d LEFT JOIN {{schema}}.form f ON f._id=d.formid WHERE d.deletedat IS NULL GROUP BY 1 ORDER BY 2 DESC;05-02 · Saved form records by original status
Count saved form records by form name and their raw saved status.
Inspection hint: Status meaning is organization-specific. Missing status is not automatically open. This does not inspect form-field answers.
SELECT coalesce(f.name,'(undefined form)') AS "Form",d.status AS "Original status",count(*) AS "Records" FROM {{schema}}.formdata d LEFT JOIN {{schema}}.form f ON f._id=d.formid WHERE d.deletedat IS NULL GROUP BY 1,2 ORDER BY 3 DESC;05-03 · Workflow versions: details
Inspect stored workflow definitions and their version classification.
Inspection hint: Current released takes precedence over latest. Latest does not necessarily mean released; product revisions are a different concept.
SELECT name AS "Process",normalizedversion AS "Workflow version",CASE WHEN currentreleasedversion THEN 'Current released version' WHEN latestversion THEN 'Latest version' ELSE 'Historical' END AS "Version status" FROM {{schema}}.workflow WHERE deletedat IS NULL ORDER BY name,normalizedversion DESC;05-04 · Workflow-version counts by process name
Compare version counts and counts marked currently released for each workflow name.
Inspection hint: Groups by name rather than workflow identity. It does not guarantee exactly one currently released version.
SELECT name AS "Process",count(*) AS "Versions",count_if(currentreleasedversion=true) AS "Current released versions" FROM {{schema}}.workflow WHERE deletedat IS NULL GROUP BY 1 ORDER BY 2 DESC;06-06 · Run-link coverage in time reports
Group time reports by no linked runs, one linked run or multiple linked runs.
Inspection hint: NULL sessionids becomes an empty array. Array cardinality is not necessarily a distinct-run count; links are not validated and hours are not divided between runs.
SELECT CASE WHEN cardinality(coalesce(sessionids,ARRAY[]))=0 THEN 'No run' WHEN cardinality(sessionids)=1 THEN 'Single run' ELSE 'Multiple runs' END AS "Report run assignment",count(*) AS "Reports" FROM {{schema}}.timelogreport WHERE deletedat IS NULL GROUP BY 1;