Bug 1117681

A useful report is "utilisation" of particular systems: what proportion of a given time period where they reserved for. We currently have nothing covering that in our supported reporting queries, we should add it.

Here is a query I wrote for a user, in Teiid syntax:

    SELECT fqdn,
        CAST(SUM(TIMESTAMPDIFF(SQL_TSI_SECOND,
            CASE WHEN reservation.start_time < {ts '2014-07-01 00:00:00'}
                THEN {ts '2014-07-01 00:00:00'}
                ELSE reservation.start_time
                END,
            CASE WHEN reservation.finish_time IS NULL
                OR reservation.finish_time > {ts '2014-07-10 00:00:00'}
                THEN {ts '2014-07-10 00:00:00'}
                ELSE reservation.finish_time
                END))
            AS FLOAT)
        / TIMESTAMPDIFF(SQL_TSI_SECOND,
            {ts '2014-07-01 00:00:00'},
            {ts '2014-07-10 00:00:00'})
    FROM Beaker.reservation
    INNER JOIN Beaker.system
        ON reservation.system_id = system.id
    WHERE reservation.start_time >= {ts '2014-07-01 00:00:00'}
        AND (reservation.finish_time < {ts '2014-07-10 00:00:00'}
            OR reservation.finish_time IS NULL)
        -- add clauses here to select particular systems
    GROUP BY system.fqdn;