gistfile1.sql

SELECT
    CASE WHEN GROUPING(
        CASE WHEN service_grade_code IN ('E01','E02','E03','E04','E05','E06','E07','E08','E09','O01','O02','O03','O04','O05','O06','O07','O08','O09','W01','W02','W03','W04','W05') THEN
            service_grade_code
        ELSE
            'Unknown'
        END
    ) = 1 THEN
        'All Ranks'
    ELSE
        CASE WHEN service_grade_code IN ('E01','E02','E03','E04','E05','E06','E07','E08','E09','O01','O02','O03','O04','O05','O06','O07','O08','O09','W01','W02','W03','W04','W05') THEN
            service_grade_code
        ELSE
            'Unknown'
        END
    END rank,
    SUM(CASE WHEN mm.service = 'Air Force' THEN 1 ELSE 0 END) air_force,
    100 * ROUND(CASE WHEN SUM(1) = 0 THEN 0 ELSE SUM(CASE WHEN mm.service = 'Air Force' THEN 1 ELSE 0 END) / CAST(SUM(1) AS FLOAT) END, 4) air_force_percentage,
    -- ... other services Army, Navy
    SUM(CASE WHEN mm.service = 'Other' THEN 1 ELSE 0 END) other,
    100 * ROUND(CASE WHEN SUM(1) = 0 THEN 0 ELSE SUM(CASE WHEN mm.service = 'Other' THEN 1 ELSE 0 END) / CAST(SUM(1) AS FLOAT) END, 4) other_percentage,
    SUM(1) total
FROM
    evacuations e LEFT JOIN
    merged_movements mm ON e.id = mm.evacuation_id LEFT JOIN
    states s ON (s.state_machinable_id = e.id AND
    s.state_machinable_type = 'Evacuation') LEFT JOIN
    trauma_codes tc ON tc.id = e.trauma_code_id
WHERE
    mm.origin_theater = 'CENTCOM' AND
    (mm.destination_theater = 'CONUS' OR mm.destination_theater = 'EUCOM' OR mm.destination_theater = 'PACOM') AND
    mm.operation in ('OEF','OIF') AND
    s.type = 'CompletedState' AND
    mm.active_duty = 1 AND
    is_injury = 0
GROUP BY
    CASE WHEN service_grade_code IN ('E01','E02','E03','E04','E05','E06','E07','E08','E09','O01','O02','O03','O04','O05','O06','O07','O08','O09','W01','W02','W03','W04','W05') THEN
        service_grade_code
    ELSE
        'Unknown'
    END
WITH ROLLUP
ORDER BY
    LEN(CASE WHEN service_grade_code IN ('E01','E02','E03','E04','E05','E06','E07','E08','E09','O01','O02','O03','O04','O05','O06','O07','O08','O09','W01','W02','W03','W04','W05') THEN
        service_grade_code
    ELSE
        'Unknown'
    END),
    CASE WHEN service_grade_code IN ('E01','E02','E03','E04','E05','E06','E07','E08','E09','O01','O02','O03','O04','O05','O06','O07','O08','O09','W01','W02','W03','W04','W05') THEN
        service_grade_code
    ELSE
        'Unknown'
    END