Timetrack Times Tabelle

SELECT 
    data_project_label,
    data_activity_label,
    COUNT(*) as anzahl_buchungen,
    ROUND(SUM(data_timetrack_time) / 60.0, 2) as stunden_gesamt,
    ROUND(SUM(data_timetrack_cost_internal), 2) as kosten_intern,
    ROUND(SUM(data_timetrack_cost_client), 2) as kosten_kunde
FROM timetrack_times
WHERE data_timetrack_date >= '2025-01-01'
  AND data_timetrack_date <= '2025-03-31'
GROUP BY data_project_label, data_activity_label
ORDER BY stunden_gesamt DESC;

SELECT 
    data_person_label,
    data_person_team_label,
    COUNT(DISTINCT data_timetrack_date) as arbeitstage,
    COUNT(*) as anzahl_buchungen,
    ROUND(SUM(data_timetrack_time) / 60.0, 2) as stunden_gesamt,
    ROUND(AVG(data_timetrack_time) / 60.0, 2) as durchschnitt_pro_buchung
FROM timetrack_times
WHERE data_timetrack_date >= CURRENT_DATE - INTERVAL '30 days'
  AND data_timetrack_time > 0
GROUP BY data_person_label, data_person_team_label
ORDER BY stunden_gesamt DESC;

SELECT 
    data_person_label,
    COUNT(*) FILTER (WHERE data_timetrack_time_is_overtime = true) as anzahl_ueberstunden,
    ROUND(SUM(data_timetrack_time) FILTER (WHERE data_timetrack_time_is_overtime = true) / 60.0, 2) as ueberstunden_gesamt,
    ROUND(SUM(data_timetrack_time) / 60.0, 2) as stunden_gesamt
FROM timetrack_times
WHERE data_timetrack_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY data_person_label
HAVING SUM(data_timetrack_time) FILTER (WHERE data_timetrack_time_is_overtime = true) > 0
ORDER BY ueberstunden_gesamt DESC;

SELECT 
    data_timetrack_approval_state,
    data_timetrack_cleared,
    COUNT(*) as anzahl_buchungen,
    ROUND(SUM(data_timetrack_time) / 60.0, 2) as stunden,
    ROUND(SUM(data_timetrack_cost_client), 2) as potentieller_umsatz
FROM timetrack_times
WHERE data_timetrack_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY data_timetrack_approval_state, data_timetrack_cleared
ORDER BY stunden DESC;

SELECT 
    data_project_label,
    data_client_label,
    COUNT(*) as buchungen,
    ROUND(SUM(data_timetrack_time) / 60.0, 2) as offene_stunden,
    ROUND(SUM(data_timetrack_cost_client), 2) as offener_umsatz
FROM timetrack_times
WHERE data_timetrack_cleared = 'open'
  AND data_timetrack_approval_state = 'approved'
  AND data_timetrack_clearable != 'blocked'
GROUP BY data_project_label, data_client_label
ORDER BY offener_umsatz DESC;

SELECT 
    data_ticket_number,
    data_ticket_label,
    data_ticket_value,
    COUNT(DISTINCT data_person_poool_uid) as beteiligte_personen,
    ROUND(SUM(data_timetrack_time) / 60.0, 2) as stunden_gebucht,
    ROUND(SUM(data_timetrack_cost_client), 2) as kosten_gebucht,
    ROUND(data_ticket_value - SUM(data_timetrack_cost_client), 2) as restbudget
FROM timetrack_times
WHERE data_ticket_poool_uid IS NOT NULL
  AND data_timetrack_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY data_ticket_number, data_ticket_label, data_ticket_value
ORDER BY restbudget ASC;