SQL Views and Basics

CASE WHEN status = 'IA' THEN 'Invoice Approved'
     WHEN status = 'P' THEN 'Pending Review'
     WHEN status = 'R' THEN 'Rejected'
END AS invoice_status

SELECT
    DATEPART(month, invoice_date) AS month,
    SUM(contract_value)
    + SUM(change_orders)
    - SUM(costs) AS net_value
FROM kroo_procore.invoices
WHERE invoice_date BETWEEN '2025-09-01' AND '2025-09-30'
    AND _fivetran_deleted = 0  -- Always filter deleted records
GROUP BY DATEPART(month, invoice_date)

SELECT v.job, p.project_name, p.total_contract_value,
       v.jtd_cost
FROM kroo_viewpoint_vista.jcjm v
LEFT JOIN kroo_procore.projects p
    ON v.job = p.project_number

-- ✅ Correct - Always include this filter
SELECT project_name, contract_amount
FROM kroo_procore.projects
WHERE _fivetran_deleted = 0

-- ❌ Wrong - Will include deleted records
SELECT project_name, contract_amount
FROM kroo_procore.projects

CREATE OR REPLACE VIEW
kroo_procore_reporting.vw_project_change_order_summary AS

SELECT
    p.project_id,
    p.project_name,
    COUNT(co.procore_id) AS change_order_count,
    SUM(co.contract_amount) AS total_contract_amount
FROM kroo_procore.change_orders co
JOIN kroo_procore.projects p
    ON co.project_id = p.procore_id
WHERE co.status = 'Approved'
    AND co._fivetran_deleted = 0
GROUP BY p.project_id, p.project_name
ORDER BY total_contract_amount DESC;

SELECT
    p.project_id,
    p.project_name,
    i.invoice_amount
FROM kroo_procore.projects p
JOIN kroo_procore.invoices i
    ON p.project_id = i.project_id
-- Only include approved invoices
WHERE i.status = 'Approved'

SELECT
    p.project_id,
    SUM(i.invoice_amount) AS total_invoices
FROM kroo_procore.projects p
JOIN kroo_procore.invoices i
    ON p.project_id = i.project_id
WHERE i.status = 'Approved'
GROUP BY p.project_id

CREATE VIEW kroo_reporting.project_financial_summary AS
SELECT ...

CREATE SCHEMA kroo_p6_reporting;
GO

CREATE VIEW kroo_p6_reporting.p6_cmic_summary AS
SELECT ...

CREATE VIEW kroo_reporting.monthly_financial_summary AS
SELECT
    YEAR(invoice_date) AS year,
    MONTH(invoice_date) AS month,
    DATENAME(MONTH, invoice_date) AS month_name,
    SUM(contract_value + ISNULL(change_orders, 0)) AS gross_revenue,
    SUM(costs) AS total_costs,
    SUM(contract_value + ISNULL(change_orders, 0) - costs) AS net_margin
FROM kroo_procore.invoices
WHERE _fivetran_deleted = 0
GROUP BY YEAR(invoice_date), MONTH(invoice_date), DATENAME(MONTH, invoice_date)

CREATE VIEW kroo_reporting.project_health_dashboard AS
SELECT
    p.project_id,
    p.project_name,
    p.project_status,
    COALESCE(SUM(co.contract_amount), 0) AS total_change_orders,
    COUNT(CASE WHEN rfi.status = 'Open' THEN 1 END) AS open_rfis,
    DATEDIFF(DAY, p.start_date, GETDATE()) AS project_age_days,
    CASE
        WHEN COUNT(CASE WHEN rfi.status = 'Open' THEN 1 END) > 5 THEN 'High Risk'
        WHEN COUNT(CASE WHEN rfi.status = 'Open' THEN 1 END) > 2 THEN 'Medium Risk'
        ELSE 'Low Risk'
    END AS risk_level
FROM kroo_procore.projects p
LEFT JOIN kroo_procore.change_orders co ON p.procore_id = co.project_id
LEFT JOIN kroo_procore.rfis rfi ON p.procore_id = rfi.project_id
WHERE p._fivetran_deleted = 0
GROUP BY p.project_id, p.project_name, p.project_status, p.start_date