Calculation Report Tabelle

SELECT 
    DATE_TRUNC('month', data_day_date) AS monat,
    data_item_account_type,
    SUM(CASE 
        WHEN data_item_value_is_weighted = true 
        THEN data_item_value * (data_item_value_weighted_percentage / 100.0)
        ELSE data_item_value 
    END) AS summe
FROM calculation_report
GROUP BY 
    DATE_TRUNC('month', data_day_date),
    data_item_account_type
ORDER BY monat, data_item_account_type;

SELECT 
    data_project_label,
    data_item_value AS lead_wert_brutto,
    data_item_value_weighted_percentage AS gewichtung,
    (data_item_value * data_item_value_weighted_percentage / 100.0) AS lead_wert_gewichtet,
    data_item_due_at AS voraussichtlicher_abschluss
FROM calculation_report
WHERE data_item_type = 'lead'
    AND data_item_value_is_weighted = true
ORDER BY lead_wert_gewichtet DESC;

SELECT 
    EXTRACT(QUARTER FROM data_day_date) AS quartal,
    EXTRACT(YEAR FROM data_day_date) AS jahr,
    data_item_account_type,
    data_item_is_forecast,
    COUNT(*) AS anzahl_eintraege,
    SUM(data_item_value) AS summe
FROM calculation_report
WHERE EXTRACT(YEAR FROM data_day_date) = 2024
GROUP BY 
    EXTRACT(QUARTER FROM data_day_date),
    EXTRACT(YEAR FROM data_day_date),
    data_item_account_type,
    data_item_is_forecast
ORDER BY quartal, data_item_account_type, data_item_is_forecast;

SELECT 
    data_project_number,
    data_project_label,
    data_item_type,
    COUNT(*) AS anzahl,
    SUM(data_item_value) AS umsatz_geplant
FROM calculation_report
WHERE data_item_account_type = 'receivable'
    AND data_project_number IS NOT NULL
GROUP BY 
    data_project_number,
    data_project_label,
    data_item_type
ORDER BY data_project_number, umsatz_geplant DESC;

SELECT 
    data_project_team_label AS team,
    DATE_TRUNC('month', data_day_date) AS monat,
    COUNT(*) AS anzahl_eintraege,
    SUM(data_item_value) AS personalkosten
FROM calculation_report
WHERE data_item_type IN ('salary', 'salary_extra')
    AND data_project_team_label IS NOT NULL
GROUP BY 
    data_project_team_label,
    DATE_TRUNC('month', data_day_date)
ORDER BY monat, personalkosten DESC;

SELECT 
    data_client_label,
    data_item_payed_state,
    COUNT(*) AS anzahl_rechnungen,
    SUM(data_item_value) AS summe
FROM calculation_report
WHERE data_item_account_type = 'receivable'
    AND data_item_type = 'invoice'
GROUP BY 
    data_client_label,
    data_item_payed_state
ORDER BY data_client_label, data_item_payed_state;