Monthly Time Tracking and Billing Report

1-- core.clickup.monthly_time_tracking_and_billing_report
2-- Monthly Time Tracking and Billing Report. Generates a monthly time tracking and
3-- billing report using data from ClickUp. It calculates the billable and non-
4-- billable time for each user, as well as the total time spent. The report also
5-- includes the billable amount, calculated based on a hypothetical rate of $50 per
6-- hour.
7--
8-- Grain: month x user, by the time entry's start (UTC). ClickUp records
9-- durations in milliseconds: the *_time columns stay in milliseconds as
10-- ClickUp reports them, the *_hours columns convert them. Running timers carry
11-- a negative duration and are left out until they stop.
12--
13-- From the weld.app template library, corrected. Not reconciled against
14-- ClickUp; check the output against the source before relying on it.
15
16with
17    MonthlyTimeSummary as (
18        select
19            date_trunc(timestamp_millis(cast(start as int)), month) as month
20          , user_id
21          , user_username
22          , sum(
23                case
24                    when billable then duration
25                    else 0
26                end
27            ) as billable_time
28          , sum(
29                case
30                    when not billable then duration
31                    else 0
32                end
33            ) as non_billable_time
34          , sum(duration) as total_time
35        from
36            {{raw.clickup.time}}
37        where
38            duration >= 0
39        group by
40            month