Enables a bespoke charging model, so that resources that may change size during the month (e.g. disk size) are charged at their final value for the month (Cloud Cruiser's internal algorithm only directly supports charging for the peak value. )

sp_end_state_usage.sql

DELIMITER GO
CREATE PROCEDURE $CCATTRIBUTE(name)  
    @inSelectDate NCHAR(8)
AS
BEGIN 

    DECLARE @vStartDate BIGINT;
    DECLARE @vEndDate BIGINT;

    SET @vStartDate = dbo.cc_date_to_seconds(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(@inSelectDate + N' 00:00:00' AS datetime)), 0));
    SET @vEndDate = dbo.cc_date_to_seconds(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(@inSelectDate + N' 00:00:00' AS datetime)) + 1, 0))

    /* 1. Get list of data_measure_type_ids for monthly, non-prorated services 
          (interval  = 2, proration = 'N') */

    CREATE TABLE #MonthlyMeasureIDs (measure_id INT);

    INSERT INTO #MonthlyMeasureIDs (measure_id)
        SELECT
            data_measure_type_id
        FROM
            service s,
            service_measure sm
        WHERE
            s.id = sm.service_id AND
            s.charge_interval = 2 AND
            s.proration_type = 'N'
        GROUP BY
            data_measure_type_id;


    /* 2. Get list of end state records per month per resource */

    CREATE TABLE #LatestTimePerDim (
        dimension           NVARCHAR(255),
        data_type_id        INT,
        AccountingPeriod    INT,
        EndStateTime        BIGINT,
        record_id           INT
        );

    CREATE INDEX latest_time ON #LatestTimePerDim (EndStateTime);
    CREATE INDEX latest_dim ON #LatestTimePerDim (dimension);

    INSERT INTO #LatestTimePerDim
        SELECT
            dd.dimension,
            ddt.data_type_id,
            MONTH(dbo.cc_seconds_to_date(dr.end_datetime)) AS AccountingPeriod,
            MAX(dr.end_datetime) AS EndStateTime,
            MAX(dr.id) AS record_id
        FROM
            data_dimension dd
            INNER JOIN data_record dr ON dd.record_id = dr.id
            INNER JOIN data_dim_type ddt ON dd.dim_type_id = ddt.id
            INNER JOIN data_type dt ON ddt.data_type_id = dt.id
        WHERE
            ddt.is_key = 1 AND
            dr.end_datetime >= @vStartDate AND
            dr.end_datetime < @vEndDate
        GROUP BY
            dd.dimension,
            dd.dim_type_id,
            ddt.data_type_id,
            dt.display_name,
            MONTH(dbo.cc_seconds_to_date(dr.end_datetime));

    /* 3. Get list of records whose dims which match the monthly service definitions */
    CREATE TABLE #EligibleRecords (
        record_id INT,
        AccountingPeriod INT
        );

    CREATE INDEX ER_ID on #EligibleRecords (record_id);

    INSERT INTO #EligibleRecords
        SELECT 
            dd.record_id AS RecordID,
            MONTH(dbo.cc_seconds_to_date(dr.end_datetime)) AS AccountingPeriod
        FROM
            data_record dr
            INNER JOIN data_dimension dd ON dr.id = dd.record_id
            INNER JOIN service_measure_filter smf ON dd.dimension = smf.value AND dd.dim_type_id = smf.dim_type_id
            INNER JOIN service_measure sm ON sm.service_id = smf.service_measure_id
            INNER JOIN service s ON s.id = sm.service_id
            INNER JOIN data_dim_type ddt ON ddt.id = smf.dim_type_id
        WHERE
            s.charge_interval = 2
            AND s.proration_type = 'N'
            AND dr.end_datetime >= @vStartDate
            AND dr.end_datetime < @vEndDate;


    /* Zero out measure records that meet the combined criteria */

    UPDATE dm
    SET
        dm.measure = 0
    FROM
        data_measure dm
        INNER JOIN #MonthlyMeasureIDs mmid on dm.measure_type_id = mmid.measure_id
        INNER JOIN #EligibleRecords er on dm.record_id = er.record_id
        LEFT OUTER JOIN #LatestTimePerDim ltpd ON dm.record_id = ltpd.record_id
    WHERE
        ltpd.record_id IS NULL
        AND dm.measure <> 0 

    /* Drop the temporary tables */

    DROP TABLE #MonthlyMeasureIDs
    DROP TABLE #EligibleRecords
    DROP TABLE #LatestTimePerDim

    SELECT
        @inSelectDate AS SelectDate,
        dbo.cc_seconds_to_date(@vStartDate) AS StartDate,
        dbo.cc_seconds_to_date(@vEndDate-1) AS EndDate

END;
GO