CREATE PROCEDURE [dbo].[TEST_2RATES]
        
AS


/* Create temp table */
        CREATE TABLE #costtemp
        (
        cost_id int,
        pc_profile_id int,
        pc_name varchar(50),
        on_time datetime,
        off_time datetime,
        subpolicy_name varchar(50),
        pc_description varchar(50),
        hours_on_day float,
        day_hour_rate float,
        hours_on_night float,
        night_hour_rate float,
        pc_kwh_rate float,
        status nchar(10),
        cost float
        )
        
        
        
/** Insert Values into Temp Table from View, including Day/Night Rates*/
        
insert into #costtemp (cost_id, pc_profile_id, on_time, off_time, status, subpolicy_name, day_hour_rate, night_hour_rate, pc_kwh_rate)

SELECT '8', '8', '09/11/2009 18:12:10', '09/12/2009 01:12:50', 'CLOSED', 'Manage', '1.2','3.9', '1.9' UNION ALL

SELECT '9', '9', '09/09/2009 05:00:00', '09/09/2009 12:00:00', 'CLOSED', 'Finance', '1.2','3.9', '1.9'


/** Get cost of PC's*/  
UPDATE #costtemp
SET hours_on_day = isnull(cost.hours_on_day, 0) ,
hours_on_night = isnull(cost.hours_on_night, 0),
cost = (isnull(cost.hours_on_day, 0) * pc_kwh_rate * day_hour_rate) + (isnull(cost.hours_on_night, 0) * pc_kwh_rate * night_hour_rate)
FROM #costtemp inner join (
select cost_id, sum(datediff(MINUTE, day_start, day_end))/ 60.0 as hours_on_day, sum(datediff(MINUTE, night_start, night_end))/ 60.0 as hours_on_night
from (
        select cost_id, pc_profile_id, on_time, off_time, subpolicy_name, day_hour_rate, night_hour_rate, pc_kwh_rate, status, start1, finish1, start2, finish2, offset,
        -- Either return null, or the start of the day period or the shutdown_time. The null value is to know when to stop counting
        case 
        when off_time < case when on_time < start1 then start1 else case when on_time < finish1 then on_time else finish1 end 

        -- Provides a stoping condition, when the field day_start begins to be NULL 
        -- means that the day and night intervals don't fall in the shutdown/start period
        end then null else 
                case when on_time < start1 then start1 else case when on_time < finish1 then on_time else finish1 end end 
                end as day_start,
        -- Either returns the finish of the day period or the startup_time
        case when off_time < finish1 then off_time else finish1 end as day_end,
        -- Either returns the start of the night period or the startup_time
        case when off_time > start2 then start2 else off_time end as night_start,
        -- Either return the finish of the night period or the startup_time
        case when off_time < finish2 then off_time else finish2 end as night_end
        from (
                select cost_id, pc_profile_id, on_time, off_time, subpolicy_name, day_hour_rate, night_hour_rate, pc_kwh_rate, status, 
                -- Start time for the day period
                convert(datetime, left(convert(varchar, dateadd(d, offset, on_time), 120), 11) + '07:00:00', 120) start1, 
                -- Finish time for the day period
                convert(datetime, left(convert(varchar, dateadd(d, offset, on_time), 120), 11) + '22:59:00', 120) finish1,
                -- Start time for the night period
                convert(datetime, left(convert(varchar, dateadd(d, offset, on_time), 120), 11) + '23:00:00', 120) start2,
                -- Finish time for the night period
                convert(datetime, left(convert(varchar, dateadd(d, offset + 1, on_time), 120), 11) + '06:59:00', 120) finish2, offset
                from #costtemp cross join (
                        -- Provides a 10 row table with sequencial numbers from 0 to 9
                        -- This allows to have off states that last for 9 days
                        select top 90 row_number() over(order by id) -1 as offset
                        from syscolumns
                ) numbers
                where status = 'CLOSED' 
        ) periods
)a
-- Stopping condition
where day_start is not null
group by cost_id
) cost on #costtemp.cost_id = cost.cost_id


Select * from #costtemp

RETURN