Timetrack Days Tabelle

SELECT 
    data_person_label,
    data_person_team_label,
    COUNT(*) as arbeitstage,
    ROUND(SUM(data_working_time_netto) / 60.0, 2) as stunden_ist,
    ROUND(SUM(data_working_time_target_netto) / 60.0, 2) as stunden_soll,
    ROUND((SUM(data_working_time_netto) - SUM(data_working_time_target_netto)) / 60.0, 2) as differenz_stunden
FROM timetrack_days 
WHERE data_day_date >= DATE_TRUNC('month', CURRENT_DATE)
    AND data_working_time_netto > 0
GROUP BY data_person_label, data_person_team_label
ORDER BY stunden_ist DESC;

SELECT 
    data_person_label,
    data_day_date,
    data_flextime_change,
    SUM(data_flextime_change) OVER (
        PARTITION BY data_person_poool_uid 
        ORDER BY data_day_date
    ) / 60.0 as gleitzeit_kumulativ_stunden
FROM timetrack_days 
WHERE data_person_poool_uid = 'person_123456'
    AND data_day_date >= DATE_TRUNC('year', CURRENT_DATE)
ORDER BY data_day_date;

SELECT 
    data_person_team_label,
    COUNT(DISTINCT data_person_poool_uid) as anzahl_mitarbeiter,
    ROUND(SUM(data_timetrack_regular_sum) / 60.0, 2) as stunden_projektzeit,
    ROUND(SUM(data_timetrack_internal_sum) / 60.0, 2) as stunden_intern,
    ROUND(SUM(data_timetrack_vacation_sum) / 60.0, 2) as stunden_urlaub,
    ROUND(SUM(data_timetrack_sick_sum) / 60.0, 2) as stunden_krank,
    ROUND(SUM(data_timetrack_total_sum) / 60.0, 2) as stunden_gesamt
FROM timetrack_days 
WHERE data_day_date >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY data_person_team_label
ORDER BY stunden_projektzeit DESC;

SELECT 
    data_person_label,
    COUNT(CASE WHEN data_timetrack_vacation_sum > 0 THEN 1 END) as urlaubstage_genommen,
    ROUND(SUM(data_timetrack_vacation_sum) / 60.0 / 8.0, 1) as urlaubstage_equivalent,
    ROUND(SUM(data_working_time_account_modification_vacation_days), 1) as urlaubstage_konto_abzug
FROM timetrack_days 
WHERE data_day_date >= DATE_TRUNC('year', CURRENT_DATE)
GROUP BY data_person_label
ORDER BY urlaubstage_genommen DESC;

SELECT 
    data_person_label,
    COUNT(*) as arbeitstage,
    ROUND(AVG(data_working_time_target_netto) / 60.0, 2) as soll_stunden_tag_avg,
    ROUND(AVG(data_working_time_netto) / 60.0, 2) as ist_stunden_tag_avg,
    ROUND((AVG(data_working_time_netto) - AVG(data_working_time_target_netto)) / 60.0, 2) as abweichung_avg
FROM timetrack_days 
WHERE data_day_date >= CURRENT_DATE - INTERVAL '30 days'
    AND data_working_time_target_netto > 0
GROUP BY data_person_label
ORDER BY abweichung_avg DESC;

SELECT 
    data_person_label,
    data_day_date,
    data_day_weekday,
    data_day_type_description,
    CASE 
        WHEN data_day_is_holiday = true THEN 'Feiertag'
        WHEN data_timetrack_vacation_sum > 0 THEN 'Urlaub'
        WHEN data_timetrack_sick_sum > 0 THEN 'Krank'
        WHEN data_timetrack_comp_time_sum > 0 THEN 'Freizeitausgleich'
        ELSE 'Arbeitstag'
    END as abwesenheits_typ
FROM timetrack_days 
WHERE data_day_date >= DATE_TRUNC('month', CURRENT_DATE)
    AND (
        data_day_is_holiday = true 
        OR data_timetrack_vacation_sum > 0 
        OR data_timetrack_sick_sum > 0
        OR data_timetrack_comp_time_sum > 0
    )
ORDER BY data_person_label, data_day_date;

SELECT 
    data_person_label,
    data_day_date,
    data_timetrack_is_invalid,
    data_timetrack_is_invalid_reason,
    ROUND(data_working_time_netto / 60.0, 2) as ist_stunden,
    ROUND(data_working_time_target_netto / 60.0, 2) as soll_stunden
FROM timetrack_days 
WHERE data_timetrack_is_invalid = true
    AND data_day_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY data_day_date DESC, data_person_label;

-- Von Minuten zu Uhrzeit
SELECT 
    LPAD((data_working_time_from / 60)::text, 2, '0') || ':' || 
    LPAD((data_working_time_from % 60)::text, 2, '0') as von_uhrzeit,
    LPAD((data_working_time_to / 60)::text, 2, '0') || ':' || 
    LPAD((data_working_time_to % 60)::text, 2, '0') as bis_uhrzeit
FROM timetrack_days
WHERE data_working_time_from > 0;