ReportSQL - Timekeeping

Time Spent in hh:mm format

* text formula field under Timekeeping

(select to_char(to_timestamp(%tableAlias%.duration * 60), 'MI:SS'))

For using timeslip Date of Service in a crosstab or summation

* Add a date formula field

* this may be unnecessary now; or may still be

%tableAlias%.service_date::date

Get value of timeslip for sites with user hourly rate tables

%tableAlias%.duration * (select hourly_rate from user_hourly_rate where user_id = %tableAlias%.user_id and %tableAlias%.service_date >= start_date and (%tableAlias%.service_date<=end_date OR end_date IS NULL))

Number of days since most recent timeslip

(select now()::date - (select max(service_date) from service where module = 'matter' and module_id = %tableAlias%.id))

Most recent timeslip on System Users report:

* Date formula field from top level

(select max(service_date) from service join users on users.id = service.user_id where service.user_id = %tableAlias%.id)

Sum of Time Spent on selected cases that does not exclude cases with no time in the range

• But the date range is hardcoded, so there's that
• Numeric formula field on Case Data

(select sum(duration) from service where module = 'matter' and module_id = %tableAlias%.id and service_date >= '2022-01-01' and service_date <= '2022-12-31')

Total Time on Outreach (equivalent of Total Time on Case field)

* numeric field on the top level outreach table

(select sum(duration) from service where
module = 'outreach' and module_id = %tableAlias%.id)

Total Time for User on a System User report

* numeric field on the top level table

(select sum(duration) from service where user_id = %tableAlias%.id)

Count of timeslips on a case; if you don't want to pull in Timekeeping and aggregate count the timeslip ID

* numeric field on case table (Case Data, also worked on Timekeeping > Cases)

(select count(id) from service where module = 'matter' and module_id = %tableAlias%.id)

"Staff" time on a case based on the old hidden 'probono' field

(select sum(duration) from service
where (service.probono is false or service.probono is null) and module = 'matter' and module_id = %tableAlias%.id)

The login name of a user in a site specific user type field on timeslips

Ugly use of report-specific column

(select login from users where id = (select time_off_approval_supervisor_name_23 from custom.custom_service where id = %tableAlias%.id))

Round Time up to nearest Quarter

(ceiling(%tableAlias%.duration *4) /4)

Number of Timeslips on a Case with a Specific Activity Code

• Numeric field on Case Data
• Get the ID(s) from the Lookup list

(select count(*) from service where service.module_id = %tableAlias%.id and service.activity_code = 3327926)

Time Spent on each Case in Previous Month

• Numeric formula field on Case Data

(select sum(duration) from service where service_date >= date_trunc('month', current_date - interval '1 month') and service_date < date_trunc('month', current_date) and module = 'matter' and module_id = %tableAlias%.id)

Case has Time in Previous Calendar Quarter

• Boolean formula field on Case Data
• NB: Untested what this will do when run in January, February, or March -- Should Work (tm)

case when
 (select count(*) from service where service.module = 'matter'
and service.module_id = %tableAlias%.id
and service_date >= date_trunc('quarter', now()) - interval '3 months'
and service_date < date_trunc('quarter', now()))
> 0
then true
else false
end

Most recent date of service on a case for current primary assignment

• Date Formula field on Case Data

SELECT max(service_date)
FROM service
JOIN matter ON service.module_id = matter.id AND service.MODULE = 'matter'
JOIN ASSIGNMENT ON matter.id = "assignment".module_id AND "assignment"."module" = 'matter'
WHERE
(assignment_type = 616 AND ASSIGNMENT.date_end IS NULL)
AND service.user_id = "assignment".assigned_to
AND matter.id = %tableAlias%.id