Monthly Time Tracking and Billing Report
1with
2 MonthlyTimeSummary as (
3 select
4 date_trunc(timestamp_millis(cast(start as int)), month) as month
5 , user_id
6 , user_username
7 , sum(
8 case
9 when billable then duration
10 else 0
11 end
12 ) as billable_time
13 , sum(
14 case
15 when not billable then duration
16 else 0
17 end
18 ) as non_billable_time
19 , sum(duration) as total_time
20 from
21 {{raw.clickup.time}}
22 group by
23 month
24 , user_id
25 , user_username
26 )
27select
28 month
29 , user_id
30 , user_username
31 , billable_time
32 , non_billable_time
33 , total_time
34 , billable_time * 50 as billable_amount -- Hypothetical rate of $50 per hour, adjust as needed
35from
36 MonthlyTimeSummary
37order by
38 month desc
39 , user_id;
+---------------------+---------+--------------+---------------+-------------------+------------+
| month | user_id | user_username| billable_time | non_billable_time | total_time |
+---------------------+---------+--------------+---------------+-------------------+------------+
| 2022-07-01 00:00:00 | 123 | johnsmith | 40.5 | 15.5 | 56.0 |
| 2022-07-01 00:00:00 | 456 | janedoe | 25.0 | 10.0 | 35.0 |
+---------------------+---------+--------------+---------------+-------------------+------------+
The Monthly Time Tracking and Billing Report SQL template is designed to generate a summary of time tracking and billing data on a monthly basis. This template integrates with ClickUp, a project management tool, to extract relevant data. The SQL code begins by creating a temporary table called "MonthlyTimeSummary" using a common table expression (CTE). This table includes aggregated information such as the month, user ID, username, billable time, non-billable time, and total time for each user. The duration is calculated based on whether the time entry is billable or not. The final SELECT statement retrieves the data from the "MonthlyTimeSummary" table and calculates the billable amount by multiplying the billable time by a hypothetical rate of $50 per hour. This rate can be adjusted as needed. The output of this SQL provides insights into the monthly time tracking and billing activities of users. It allows you to analyze the billable and non-billable time spent by each user, the total time worked, and the corresponding billable amount. This report can be useful for tracking project costs, evaluating employee productivity, and generating invoices based on billable hours.
