CREATE PROCEDURE [dbo].[SP_AAAAAAAAAAAAAAAAAAAAAA]
AS	

--=====Create temp table 
	CREATE TABLE #savingstemp
	(
	audit_id int,
	pc_profile_id int,
	shutdown_Time datetime NULL,
	hibernate_Time datetime NULL, 
	sleep_Time datetime NULL,  
	startup_Time datetime NULL,
	status varchar(50),
	subpolicy_name varchar(50),
	hours_off_day int,
	hours_off_night int,
	day_hour_rate float,
	night_hour_rate float,
	pc_kwh_rate float,
	savings float	
	)
	
	
--===== Insert Values in to Temp Table
insert into #savingstemp (audit_id, pc_profile_id, shutdown_Time, hibernate_Time, sleep_Time, startup_Time, status, subpolicy_name, day_hour_rate, night_hour_rate, pc_kwh_rate)
SELECT '2', '1', '05/20/2010 11:02:08' , NULL, NULL, '05/21/2010 16:03:03', 'CLOSED', 'Sales', '1.2','3.9', '1.9' UNION ALL
SELECT '3', '2', '05/20/2010 11:00:17',	NULL, NULL, '05/21/2010 16:03:00', 'CLOSED', 'Exempt', '1.2', '3.9', '1.9' UNION ALL
SELECT '4', '3', '05/20/2010 05:00:00',	NULL, NULL, '05/21/2010 15:00:00', 'CLOSED', 'Sales', '1.2', '3.9', '1.9' UNION ALL
SELECT '4', '3', '06/10/2010 07:00:00',	NULL, NULL, '06/11/2010 13:00:00', 'CLOSED', 'Test', '1.2', '3.9', '1.9' UNION ALL
SELECT '4', '3', '06/10/2010 08:00:00',	NULL, NULL, '06/11/2010 14:00:00', 'CLOSED', 'Sales', '1.2', '3.9', '1.9' UNION ALL
SELECT '4', '3', '06/10/2010 09:00:00',	NULL, NULL, '06/11/2010 14:00:00', 'CLOSED', 'Exempt', '1.2', '3.9', '1.9' 


	
--===== Get hours off between Shutdown/hibernate/sleep and Startup and insert them into Hours Off Day or Hours Off Night fields*/
	UPDATE #savingstemp
SET hours_off_day = isnull(savings.hours_off_day, 0) ,
hours_off_night = isnull(savings.hours_off_night, 0),
savings = (isnull(savings.hours_off_day, 0) * pc_kwh_rate * day_hour_rate) + (isnull(savings.hours_off_night, 0) * pc_kwh_rate * night_hour_rate)
FROM #savingstemp inner join (
select audit_id, sum(datediff(MINUTE, day_start, day_end))/ 60.0 as hours_off_day, sum(datediff(MINUTE, night_start, night_end))/ 60.0 as hours_off_night
from (
select audit_id, pc_profile_id, status, subpolicy_name, day_hour_rate, night_hour_rate, pc_kwh_rate,
shutdown_Time, hibernate_Time, sleep_Time, startup_Time, 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 startup_Time < case when COALESCE( shutdown_Time, hibernate_Time, sleep_Time) < start1 then start1 else case when COALESCE( shutdown_Time, hibernate_Time, sleep_Time) < finish1 then COALESCE( shutdown_Time, hibernate_Time, sleep_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 COALESCE( shutdown_Time, hibernate_Time, sleep_Time) < start1 then start1 else case when COALESCE( shutdown_Time, hibernate_Time, sleep_Time) < finish1 then COALESCE( shutdown_Time, hibernate_Time, sleep_Time) else finish1 end end 
end as day_start,
-- Either returns the finish of the day rate or the startup_time
case when startup_Time < finish1 then startup_Time else finish1 end as day_end,
-- Either returns the start of the night rate or the startup_time
case when startup_Time > start2 then start2 else startup_Time end as night_start,
-- Either return the finish of the night rate or the startup_time
case when startup_Time < finish2 then startup_Time else finish2 end as night_end
from (
-- For each record in the table provides the shutdown, hibernate, sleep and start time
select audit_id, pc_profile_id, shutdown_Time, hibernate_Time, sleep_Time, startup_Time, status, subpolicy_name, day_hour_rate, night_hour_rate, pc_kwh_rate, 
-- Start time for the day period
convert(datetime, left(convert(varchar, dateadd(d, offset, COALESCE( shutdown_Time, hibernate_Time, sleep_Time) ), 120), 11) + '07:00:00', 120) start1, 
-- Finish time for the day period
convert(datetime, left(convert(varchar, dateadd(d, offset, COALESCE( shutdown_Time, hibernate_Time, sleep_Time) ), 120), 11) + '22:59:00', 120) finish1,
-- Start time for the night period
convert(datetime, left(convert(varchar, dateadd(d, offset, COALESCE( shutdown_Time, hibernate_Time, sleep_Time) ), 120), 11) + '23:00:00', 120) start2,
-- Finish time for the night period
convert(datetime, left(convert(varchar, dateadd(d, offset + 1, COALESCE( shutdown_Time, hibernate_Time, sleep_Time) ), 120), 11) + '06:59:00', 120) finish2, offset
from #savingstemp cross join (
-- Tally Table 
select top 90 row_number() over(order by id) -1 as offset
from syscolumns
) numbers
where status = 'CLOSED' 
) periods
)a
-- Stopping condition and calculations
where day_start is not null
group by audit_id
) Savings on #savingstemp.audit_id= Savings.audit_id


--===== Group Savings per Week    

select datepart(yyyy,startup_Time) as 'year',
subpolicy_name as 'subpolicy',  
max(datename(month,startup_Time)) as 'month',
sum(savings) as 'previous_monthly_savings'
from #savingstemp
WHERE startup_Time >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)
AND startup_Time < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
group by datepart(yyyy,startup_Time), subpolicy_name,
datepart(mm,startup_Time)
order by datepart(yyyy,startup_Time), subpolicy_name,
datepart(mm,startup_Time);


--===== Group Savings per Month    
DECLARE @FirstDay DATETIME

select @FirstDay = CAST(CAST(YEAR(getdate()) AS VARCHAR(4)) + '/' + 
                CAST(MONTH(getdate()) AS VARCHAR(2)) + '/01' AS DATETIME)   
				          
select datepart(yyyy,startup_Time) as 'year', subpolicy_name as 'subpolicy', 
max(datename(month,startup_Time)) as 'month',
sum(savings) as 'monthly_savings_todate'
from #savingstemp
WHERE startup_Time between @FirstDay and GETDATE()

group by datepart(yyyy,startup_Time), subpolicy_name,
datepart(month,startup_Time)
order by datepart(yyyy,startup_Time), subpolicy_name,
datepart(month,startup_Time);


RETURN