XML wraparound for the sp_end_state_usage.sql stored procedure, for uploading via web UI.

sp_end_state_usage.xml

<?xml version="1.0" encoding="UTF-8"?><procedure description="Zeros out usage measures except for last record of month per resource" name="sp_end_state_usage">
<sqlserver>
<![CDATA[



    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




]]>
</sqlserver>
</procedure>