Weld logo
clickup logo
Clickup

Monthly Time Tracking and Billing Report

Generates a monthly time tracking and billing report using data from ClickUp. It calculates the billable and non-billable time for each user, as well as the total time spent. The report also includes the billable amount, calculated based on a hypothetical rate of $50 per hour.
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;
Example of output from model:

+---------------------+---------+--------------+---------------+-------------------+------------+
|        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.

Ready to start modeling your own clickup data?

Get started building your data warehouse with clickup and connect all your apps and databases.

clickup logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.