FACILITY_UTILIZATION

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

FACILITY_ID

int

√

null

The ID of the facility

FACILITY_NAME

varchar

255

√

null

The name of the facility

CENTER_ID

int

√

null

The ID of the facility’s center

CENTER_NAME

varchar

255

√

null

The name of the facility’s center

SITE_ID

int

√

null

The ID of the center’s site

SITE_NAME

varchar

255

√

null

The name of the center’s site

FACILITY_DATE

date

√

null

The date that the availability is calculated for

DAY_OF_WEEK

int

√

null

The day of week number (1=Sunday to 7=Saturday)

DAY_OF_WEEK_NAME

nvarchar

√

null

The day of week name (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday)

IS_CLOSED

varchar

√

null

Whether the facility is configured as closed on this day of the week (Yes/No)

IS_OPEN_24_HOURS

varchar

√

null

Whether the facility is configured as open 24 hours per day (Yes/No)

MIN_OPEN_TIME

time

√

null

The minimum open time for the facility on this date

MAX_CLOSE_TIME

time

√

null

The maximum close time for the facility on this date

OPEN_MINUTES

int

√

null

The number of minutes the facility is open on this date

SKIP_MINUTES

int

√

null

The number of minutes the facility is closed on this date due to a skip date

AVAILABLE_MINUTES

int

√

null

The number of minutes the facility is available for reservation on this date

OPEN_HOURS

numeric

12,2

√

null

The number of hours the facility is open on this date

SKIP_HOURS

numeric

12,2

√

null

The number of hours the facility is closed on this date due to a skip date

AVAILABLE_HOURS

numeric

12,2

√

null

The number of hours the facility is available for reservation on this date

PERMIT_MINUTES

int

null

The number of minutes the facility is booked for permits on this date

ACTIVITY_MINUTES

int

null

The number of minutes the facility is booked for activities on this date

PROGRAM_MINUTES

int

null

The number of minutes the facility is booked for daycare programs on this date

LEAGUE_MINUTES

int

null

The number of minutes the facility is booked for league schedules on this date

TOTAL_BOOKED_MINUTES

int

null

The total number of minutes the facility is booked on this date

PERMIT_HOURS

numeric

12,2

√

null

The number of hours the facility is booked for permits on this date

ACTIVITY_HOURS

numeric

12,2

√

null

The number of hours the facility is booked for activities on this date

PROGRAM_HOURS

numeric

12,2

√

null

The number of hours the facility is booked for daycare programs on this date

LEAGUE_HOURS

numeric

12,2

√

null

The number of hours the facility is booked for league schedules on this date

TOTAL_BOOKED_HOURS

numeric

12,2

√

null

The total number of hours the facility is booked on this date

PERMIT_ATTENDANCE

int

null

The total attendance for permits that the facility is booked for on this date

ACTIVITY_ATTENDANCE

int

null

The total attendance for activities that the facility is booked for on this date

PROGRAM_ATTENDANCE

int

null

The total attendance for daycare programs that the facility is booked for on this date

LEAGUE_ATTENDANCE

int

null

The total attendance for league schedules that the facility is booked for on this date

TOTAL_BOOKED_ATTENDANCE

int

null

The total attendance that the facility is booked for on this date

Relationships

Close relationships within degrees of separation

View Definition

CREATE   VIEW dbo.FACILITY_UTILIZATION
AS
WITH cte_FACILITY_RESERVATION_SUMMARY (
    FACILITY_ID,
    FACILITY_DATE,
    PERMIT_MINUTES,
    ACTIVITY_MINUTES,
    PROGRAM_MINUTES,
    LEAGUE_MINUTES,
    TOTAL_BOOKED_MINUTES,
    PERMIT_ATTENDANCE,
    ACTIVITY_ATTENDANCE,
    PROGRAM_ATTENDANCE,
    LEAGUE_ATTENDANCE,
    TOTAL_BOOKED_ATTENDANCE
) AS (
    SELECT TOP 100 PERCENT
        fre.FACILITY_ID,
        fre.FACILITY_DATE,
        SUM(CASE WHEN fre.IS_PERMIT = 0 THEN 0 ELSE fre.BOOKED_MINUTES END) AS PERMIT_MINUTES,
        SUM(CASE WHEN fre.IS_ACTIVITY = 0 THEN 0 ELSE fre.BOOKED_MINUTES END) AS ACTIVITY_MINUTES,
        SUM(CASE WHEN fre.IS_PROGRAM = 0 THEN 0 ELSE fre.BOOKED_MINUTES END) AS PROGRAM_MINUTES,
        SUM(CASE WHEN fre.IS_LEAGUE = 0 THEN 0 ELSE fre.BOOKED_MINUTES END) AS LEAGUE_MINUTES,
        SUM(fre.BOOKED_MINUTES) AS TOTAL_BOOKED_MINUTES,
        SUM(CASE WHEN fre.IS_PERMIT = 0 THEN 0 ELSE fre.BOOKED_ATTENDANCE END) AS PERMIT_ATTENDANCE,
        SUM(CASE WHEN fre.IS_ACTIVITY = 0 THEN 0 ELSE fre.BOOKED_ATTENDANCE END) AS ACTIVITY_ATTENDANCE,
        SUM(CASE WHEN fre.IS_PROGRAM = 0 THEN 0 ELSE fre.BOOKED_ATTENDANCE END) AS PROGRAM_ATTENDANCE,
        SUM(CASE WHEN fre.IS_LEAGUE = 0 THEN 0 ELSE fre.BOOKED_ATTENDANCE END) AS LEAGUE_ATTENDANCE,
        SUM(fre.BOOKED_ATTENDANCE) AS TOTAL_BOOKED_ATTENDANCE
    FROM (
        SELECT
            fav.FACILITY_ID,
            fav.FACILITY_DATE,
            (CASE WHEN fre.PERMIT_ID IS NOT NULL THEN 1 ELSE 0 END) AS IS_PERMIT,
            (CASE WHEN fre.ACTIVITY_ID IS NOT NULL THEN 1 ELSE 0 END) AS IS_ACTIVITY,
            (CASE WHEN fre.SESSION_ID IS NOT NULL THEN 1 ELSE 0 END) AS IS_PROGRAM,
            (CASE WHEN fre.LEAGUE_SCHEDULE_ID IS NOT NULL THEN 1 ELSE 0 END) AS IS_LEAGUE,
            (CASE
                ------------------------------------------------------------------
                -- Single day booking
                ------------------------------------------------------------------
                WHEN fre.RESERVATION_START_DATE = fav.FACILITY_DATE
                    AND fre.RESERVATION_END_DATE = fav.FACILITY_DATE THEN
                    -- Reservation starts and ends on the same date, so just calculate the duration of the reservation
                    DATEDIFF(MINUTE, CAST(fre.RESERVATION_START_DATE AS DATETIME) + CAST(fre.RESERVATION_START_TIME AS DATETIME), CAST(fre.RESERVATION_END_DATE AS DATETIME) + CAST(fre.RESERVATION_END_TIME AS DATETIME))
                WHEN fre.RESERVATION_START_DATE = fav.FACILITY_DATE
                    AND fre.RESERVATION_END_DATE = DATEADD(DAY, 1, fre.RESERVATION_START_DATE)
                    AND fre.RESERVATION_END_TIME = '00:00:00' THEN
                    -- Reservation ends at midnight of the start date, so just calculate the duration of the reservation
                    DATEDIFF(MINUTE, CAST(fre.RESERVATION_START_DATE AS DATETIME) + CAST(fre.RESERVATION_START_TIME AS DATETIME), CAST(fre.RESERVATION_END_DATE AS DATETIME) + CAST(fre.RESERVATION_END_TIME AS DATETIME))
                ------------------------------------------------------------------
                -- First day of a multiple day booking
                ------------------------------------------------------------------
                WHEN fre.RESERVATION_START_DATE = fav.FACILITY_DATE
                    AND fre.RESERVATION_END_DATE = DATEADD(DAY, 1, fre.RESERVATION_START_DATE)
                    AND fav.MAX_CLOSE_TIME > '00:00:00'
                    AND fav.MAX_CLOSE_TIME < fav.MIN_OPEN_TIME
                    AND fre.RESERVATION_END_TIME <= fav.MAX_CLOSE_TIME THEN
                    -- Reservation ends the day after it starts but on or before the facility close time, so just calculate the duration of the reservation
                    DATEDIFF(MINUTE, CAST(fre.RESERVATION_START_DATE AS DATETIME) + CAST(fre.RESERVATION_START_TIME AS DATETIME), CAST(fre.RESERVATION_END_DATE AS DATETIME) + CAST(fre.RESERVATION_END_TIME AS DATETIME))
                WHEN fre.RESERVATION_START_DATE = fav.FACILITY_DATE
                    AND fav.MAX_CLOSE_TIME > '00:00:00'
                    AND fav.MAX_CLOSE_TIME < fav.MIN_OPEN_TIME
                    AND fre.RESERVATION_END_TIME > fav.MAX_CLOSE_TIME  THEN
                    -- Reservation spans multiple dates with a facility close time on the first date after midnight, so for first day calculate duration to facility close time
                    DATEDIFF(MINUTE, CAST(fre.RESERVATION_START_DATE AS DATETIME) + CAST(fre.RESERVATION_START_TIME AS DATETIME), CAST(DATEADD(DAY, 1, fre.RESERVATION_START_DATE) AS DATETIME) + CAST(fav.MAX_CLOSE_TIME AS DATETIME))
                WHEN fre.RESERVATION_START_DATE = fav.FACILITY_DATE THEN
                    -- Reservation spans multiple dates with a facility close time on the first date on or before midnight, so for first day calculate duration to midnight
                    DATEDIFF(MINUTE, CAST(fre.RESERVATION_START_DATE AS DATETIME) + CAST(fre.RESERVATION_START_TIME AS DATETIME), DATEADD(DAY, 1, fre.RESERVATION_START_DATE))
                ------------------------------------------------------------------
                -- Second to last day of a multiple day booking
                ------------------------------------------------------------------
                WHEN fav.FACILITY_DATE > fre.RESERVATION_START_DATE
                    AND fav.FACILITY_DATE = DATEADD(DAY, -1, fre.RESERVATION_END_DATE)
                    AND fav.MAX_CLOSE_TIME IS NOT NULL
                    AND fav.MAX_CLOSE_TIME > '00:00:00'
                    AND fav.MAX_CLOSE_TIME < fav.MIN_OPEN_TIME
                    AND fre.RESERVATION_END_TIME < fav.MAX_CLOSE_TIME THEN
                    -- Reservation spans multiple dates with the end time of the last date on or prior to the facility close time for the previous day, reduce duration by minutes prior to close time
                    1440 - DATEDIFF(MINUTE, CAST(fre.RESERVATION_END_DATE AS DATETIME) + CAST(fre.RESERVATION_END_TIME AS DATETIME), CAST(fre.RESERVATION_END_DATE AS DATETIME) + CAST(fav.MAX_CLOSE_TIME AS DATETIME))
                ------------------------------------------------------------------
                -- Last day of a multiple day booking
                ------------------------------------------------------------------
                WHEN fre.RESERVATION_END_DATE = fav.FACILITY_DATE
                    AND fre.RESERVATION_END_TIME = '00:00:00' THEN
                    -- Reservation spans multiple dates with the end time of the last date at midnight, so no duration counts on this date
                    0
                WHEN fre.RESERVATION_END_DATE = fav.FACILITY_DATE
                    AND prior_fav.MAX_CLOSE_TIME IS NOT NULL
                    AND prior_fav.MAX_CLOSE_TIME > '00:00:00'
                    AND prior_fav.MAX_CLOSE_TIME < prior_fav.MIN_OPEN_TIME
                    AND fre.RESERVATION_END_TIME <= prior_fav.MAX_CLOSE_TIME THEN
                    -- Reservation spans multiple dates with the end time of the last date on or prior to the facility close time for the previous day, so no duration counts on this date
                    0
                WHEN fre.RESERVATION_END_DATE = fav.FACILITY_DATE
                    AND prior_fav.MAX_CLOSE_TIME IS NOT NULL
                    AND prior_fav.MAX_CLOSE_TIME > '00:00:00'
                    AND prior_fav.MAX_CLOSE_TIME < prior_fav.MIN_OPEN_TIME
                    AND fre.RESERVATION_END_TIME > prior_fav.MAX_CLOSE_TIME THEN
                    -- Reservation spans multiple dates with the end time of the last date after the facility close time for the previous day, so calculate duration from the facility close time
                    DATEDIFF(MINUTE, CAST(fre.RESERVATION_END_DATE AS DATETIME) + CAST(prior_fav.MAX_CLOSE_TIME AS DATETIME), CAST(fre.RESERVATION_END_DATE AS DATETIME) + CAST(fre.RESERVATION_END_TIME AS DATETIME))
                WHEN fre.RESERVATION_END_DATE = fav.FACILITY_DATE THEN
                    -- Reservation spans multiple dates with the facility close time for the day prior to the end date set to midnight, so calculate duration from midnight to booking end time
                    DATEDIFF(MINUTE, CAST(fre.RESERVATION_END_DATE AS DATETIME), CAST(fre.RESERVATION_END_DATE AS DATETIME) + CAST(fre.RESERVATION_END_TIME AS DATETIME))
                ------------------------------------------------------------------
                -- Other days of a multiple day booking
                ------------------------------------------------------------------
                ELSE 1440 -- minutes in a full day
                END) AS BOOKED_MINUTES,
            (CASE
                WHEN fre.RESERVATION_END_DATE = fav.FACILITY_DATE
                    AND fre.RESERVATION_END_DATE > fre.RESERVATION_START_DATE
                    AND prior_fav.MAX_CLOSE_TIME IS NOT NULL
                    AND prior_fav.MAX_CLOSE_TIME > '00:00:00'
                    AND prior_fav.MAX_CLOSE_TIME < prior_fav.MIN_OPEN_TIME
                    AND fre.RESERVATION_END_TIME <= prior_fav.MAX_CLOSE_TIME THEN
                    -- Reservation spans multiple dates with the end time of the last date on or prior to the end time for the previous day
                    0
                ELSE fre.ATTENDANCE
                END) AS BOOKED_ATTENDANCE
        FROM
            hub.FACILITY_RESERVATION fre
            JOIN hub.FACILITY_AVAILABILITY fav ON fav.FACILITY_ID = fre.FACILITY_ID AND fav.FACILITY_DATE >= fre.RESERVATION_START_DATE AND fav.FACILITY_DATE <= fre.RESERVATION_END_DATE
            LEFT JOIN hub.FACILITY_AVAILABILITY prior_fav ON prior_fav.FACILITY_ID = fre.FACILITY_ID AND prior_fav.FACILITY_DATE = DATEADD(DAY, -1, fre.RESERVATION_END_DATE)
        WHERE
            fre.RESERVATION_END_DATE >= DATEADD(DAY, 1, EOMONTH(GETDATE(), -37))
            AND fre.RESERVATION_START_DATE <= EOMONTH(GETDATE(), 12)
        ) fre

    GROUP BY
        fre.FACILITY_ID,
        fre.FACILITY_DATE
    ORDER BY
        fre.FACILITY_ID,
        fre.FACILITY_DATE
)
SELECT
    fav.FACILITY_ID,
    fac.FACILITY_NAME,
    cen.CENTER_ID,
    cen.CENTER_NAME,
    sit.SITE_ID,
    sit.SITE_NAME,
    fav.FACILITY_DATE,
    DATEPART(WEEKDAY, fav.FACILITY_DATE) AS DAY_OF_WEEK,
    DATENAME(WEEKDAY, fav.FACILITY_DATE) AS DAY_OF_WEEK_NAME,
    fav.IS_CLOSED,
    fav.IS_OPEN_24_HOURS,
    fav.MIN_OPEN_TIME,
    fav.MAX_CLOSE_TIME,
    fav.OPEN_MINUTES,
    fav.SKIP_MINUTES,
    (fav.OPEN_MINUTES - fav.SKIP_MINUTES) AS AVAILABLE_MINUTES,
    CAST((CAST(fav.OPEN_MINUTES AS NUMERIC(10, 2)) / 60) AS NUMERIC(10, 2)) AS OPEN_HOURS,
    CAST((CAST(fav.SKIP_MINUTES AS NUMERIC(10, 2)) / 60) AS NUMERIC(10, 2)) AS SKIP_HOURS,
    CAST((CAST((fav.OPEN_MINUTES - fav.SKIP_MINUTES) AS NUMERIC(10, 2)) / 60) AS NUMERIC(10, 2)) AS AVAILABLE_HOURS,
    ISNULL(frs.PERMIT_MINUTES, 0) AS PERMIT_MINUTES,
    ISNULL(frs.ACTIVITY_MINUTES, 0) AS ACTIVITY_MINUTES,
    ISNULL(frs.PROGRAM_MINUTES, 0) AS PROGRAM_MINUTES,
    ISNULL(frs.LEAGUE_MINUTES, 0) AS LEAGUE_MINUTES,
    ISNULL(frs.TOTAL_BOOKED_MINUTES, 0) AS TOTAL_BOOKED_MINUTES,
    CAST((CAST(ISNULL(frs.PERMIT_MINUTES, 0) AS NUMERIC(10, 2)) / 60) AS NUMERIC(10, 2)) AS PERMIT_HOURS,
    CAST((CAST(ISNULL(frs.ACTIVITY_MINUTES, 0) AS NUMERIC(10, 2)) / 60) AS NUMERIC(10, 2)) AS ACTIVITY_HOURS,
    CAST((CAST(ISNULL(frs.PROGRAM_MINUTES, 0) AS NUMERIC(10, 2)) / 60) AS NUMERIC(10, 2)) AS PROGRAM_HOURS,
    CAST((CAST(ISNULL(frs.LEAGUE_MINUTES, 0) AS NUMERIC(10, 2)) / 60) AS NUMERIC(10, 2)) AS LEAGUE_HOURS,
    CAST((CAST(ISNULL(frs.TOTAL_BOOKED_MINUTES, 0) AS NUMERIC(10, 2)) / 60) AS NUMERIC(10, 2)) AS TOTAL_BOOKED_HOURS,
    ISNULL(frs.PERMIT_ATTENDANCE, 0) AS PERMIT_ATTENDANCE,
    ISNULL(frs.ACTIVITY_ATTENDANCE, 0) AS ACTIVITY_ATTENDANCE,
    ISNULL(frs.PROGRAM_ATTENDANCE, 0) AS PROGRAM_ATTENDANCE,
    ISNULL(frs.LEAGUE_ATTENDANCE, 0) AS LEAGUE_ATTENDANCE,
    ISNULL(frs.TOTAL_BOOKED_ATTENDANCE, 0) AS TOTAL_BOOKED_ATTENDANCE
FROM
    hub.FACILITY_AVAILABILITY fav
    JOIN hub.FACILITIES fac ON fac.FACILITY_ID = fav.FACILITY_ID
    LEFT JOIN hub.CENTERS cen ON cen.CENTER_ID = fac.CENTER_ID
    LEFT JOIN hub.SITES sit ON sit.SITE_ID = cen.SITE_ID
    LEFT JOIN cte_FACILITY_RESERVATION_SUMMARY frs ON frs.FACILITY_ID = fav.FACILITY_ID AND frs.FACILITY_DATE = fav.FACILITY_DATE;

Possibly Referenced Tables/Views

ACTIVITY_ATTENDANCE

Description

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views