ALTER PROCEDURE [dbo].[SP_TEST_COMPARATIVE]
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 '1', '2', '01/20/2010 17:02:08', NULL, NULL, '01/21/2010 09:03:03', 'CLOSED', 'Exempt', '0.25', '0.14', '3.9' UNION ALL
SELECT '2', '3', '01/22/2010 18:02:08', NULL, NULL, '01/23/2010 10:03:03', 'CLOSED', 'Manufacturing', '0.25', '0.14', '3.9' UNION ALL
SELECT '3', '4', '02/10/2010 18:02:08', NULL, NULL, '02/11/2010 09:03:03', 'CLOSED', 'ORB', '0.25', '0.14', '3.9' UNION ALL
SELECT '4', '5', '02/11/2010 17:00:17', NULL, NULL,	'02/12/2010 10:03:00', 'CLOSED', 'IT', '0.25', '0.14', '3.9' UNION ALL
SELECT '5', '6', '02/13/2010 18:02:08', NULL, NULL, '02/14/2010 08:03:03', 'CLOSED', 'HR', '0.25', '0.14', '3.9' UNION ALL
SELECT '6', '7', '03/18/2010 18:02:08', NULL, NULL, '03/19/2010 09:03:03', 'CLOSED', 'NIPBI', '0.25', '0.14', '3.9' UNION ALL
SELECT '7', '8', '03/19/2010 17:02:08', NULL, NULL, '03/20/2010 09:03:03', 'CLOSED', 'AIT', '0.25', '0.14', '3.9' UNION ALL
SELECT '8', '9', '03/20/2010 16:02:08', NULL, NULL, '03/21/2010 08:03:03', 'CLOSED', 'Finance', '0.25', '0.14', '3.9' UNION ALL
SELECT '9', '10', '03/21/2010 17:02:08', NULL, NULL, '03/22/2010 11:03:03', 'CLOSED', 'Test', '0.25', '0.14', '3.9' UNION ALL
SELECT '10', '11', '03/22/2010 17:52:17', NULL, NULL, '03/23/2010 11:02:57', 'CLOSED', 'Kane', '0.25', '0.14', '3.9' UNION ALL
SELECT '11', '12', '03/23/2010 18:12:10', NULL, NULL, '03/24/2010 10:12:50', 'CLOSED', 'Labs', '0.25', '0.14', '3.9' UNION ALL
SELECT '12', '13', '03/31/2010 19:12:10', NULL, NULL, '04/01/2010 09:12:50', 'CLOSED', 'ELEC', '0.25', '0.14', '3.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(wk,startup_Time)) as 'week',
sum(savings) as 'weekly_savings'
from #savingstemp
WHERE startup_Time between CAST(CONVERT(VARCHAR(10), 
DATEADD(DD, (-5) - DATEPART(DW, GETDATE()), 
GETDATE()), 111) AS DATETIME)  and CAST(CONVERT(VARCHAR(10), 
DATEADD(DD, 1 - DATEPART(DW, GETDATE()), GETDATE()), 111) AS DATETIME)
group by datepart(yyyy,startup_Time), subpolicy_name,
datepart(wk,startup_Time)
order by datepart(yyyy,startup_Time), subpolicy_name,
datepart(wk,startup_Time);


                
RETURN 

....
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);

quote:

---

Originally posted by visakh16

change lst query to

....
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);

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by visakh16

WHERE startup_Time >= DATEADD(qq,DATEDIFF(qq,0,GETDATE())-1,0)
AND startup_Time < DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)
group by datepart(yyyy,startup_Time), subpolicy_name,
datepart(qq,startup_Time)
order by datepart(yyyy,startup_Time), subpolicy_name,
datepart(qq,startup_Time);

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---