Discussions

Hi! I cannot produce a reliable report using the API to match the data in the schedules (actuals section at the bottom)

I am using this code and it does not match my UI. What base tables and timezones should I be using?

with table_base as (

SELECT tp.id
, tp.*
-- , clocked_in
-- , clocked_out
-- , hourly_wage
, d.name
, TIMESTAMP_DIFF(clocked_out, clocked_in, MINUTE) AS minutes_worked
, TIMESTAMP_DIFF(clocked_out, clocked_in, MINUTE) / 60 * hourly_wage / 100 as labor_cost

FROM sevenshifts.time_punch tp
LEFT JOIN sevenshifts.location l
on tp.location_id = l.id
LEFT JOIN sevenshifts.department d
on tp.department_id = d.id
WHERE (DATE(clocked_in, 'America/New_York') = '2026-07-14')
OR DATE(clocked_out, 'America/New_York') = '2026-07-14' )

SELECT * from table_base
WHERE name like '%Parking%'