January 27, 2010 at 10:35 am
Hi All,
Im using SQL Server 2005, What Im trying to do is calculate savings in relation to electricity saved and in turn money saved. A computer has a shutdown_ Time and a Startup_Time, by getting the DateDiff between these I can calculate the amount of time the computer is off and then savings.
There are 2 rates in regards to electricity used, firstly there is a day rate which is generally from 7:00 - 23:00 and there is a night rate which is generally from 23:00 - 7:00. The hours off are calualted and inserted into a hours_off_day field or hours-off_night field depending on the timing.
Now i actually have this working very well for shutdown_Time and startup_Time. However I may actually have a sleep_Time or hibernate-Time or a shutdown_Time. So for example a computer will startup then it will shutdown or sleep or hibernate then the status is = CLOSED.
So basically a record will definelty have a startup_Time and then it will have either a shutdown_Time, sleep_Time or hibernate_Time, a record cannot have all three it can only have 1 of the three and the other 2 fields will remain NULL. Below is my code and sample data if you run the 1st piece of code for just shutdown_Time you will see that it works very well but if you look at my 2nd chunk of SQL i have tried to add in hibernate_Time and sleep_Time and i am getting crazy results.
all I've done is replace all the shutdown_time to COALESCE( shutdown_Time, hibernate_Time, sleep_Time).
Thanks in advance for any help i hope its only a small problem because it is working but just not for all 3 element of it, maybe there is a better way to do it I'm not sure 🙁
Thanks
CODE FOR SHUTDOWN (WORKING CORRECTLY)
--===== Create the test table with
CREATE TABLE #savingstemp
(
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),
building_name varchar(50),
hours_off_day int,
day_hour_rate float,
hours_off_night int,
night_hour_rate float,
pc_kwh_rate float,
savings float
)
--===== Insert Sample data into Temp Table
insert #savingstemp (pc_profile_id, shutdown_Time, startup_Time, status, subpolicy_name, building_name, day_hour_rate, night_hour_rate, pc_kwh_rate)
SELECT '2', '02/01/2009 11:02:08', '02/01/2009 16:03:03', 'CLOSED', 'Exempt', 'ORB', '1.2','3.9', '1.9' UNION ALL
SELECT '3', '04/09/2009 11:00:17', '04/09/2009 16:03:00', 'CLOSED', 'Exempt', 'ORB', '1.2', '3.9', '1.9' UNION ALL
--===== Calculate Hours off and Savings
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 pc_profile_id, sum(datediff(hour, day_start, day_end)) as hours_off_day, sum(datediff(hour, night_start, night_end)) as hours_off_night
from (
select pc_profile_id, status, subpolicy_name, day_hour_rate, night_hour_rate, pc_kwh_rate,
shutdown_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 shutdown_Time < start1 then start1 else case when shutdown_Time < finish1 then shutdown_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 shutdown_Time < start1 then start1 else case when shutdown_Time < finish1 then shutdown_Time else finish1 end end
end as day_start,
-- Either returns the finish of the day period 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 period 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 period 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 de shutdown an start time
select pc_profile_id, shutdown_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, shutdown_time), 120), 11) + '07:00:00', 120) start1,
-- Finish time for the day period
convert(datetime, left(convert(varchar, dateadd(d, offset, shutdown_time), 120), 11) + '22:59:00', 120) finish1,
-- Start time for the night period
convert(datetime, left(convert(varchar, dateadd(d, offset, shutdown_time), 120), 11) + '23:00:00', 120) start2,
-- Finish time for the night period
convert(datetime, left(convert(varchar, dateadd(d, offset + 1, shutdown_time), 120), 11) + '06:59:00', 120) finish2, offset
from #savingstemp 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 10 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 pc_profile_id
) Savings on #savingstemp.pc_profile_id = Savings.pc_profile_id
select*
from #savingstemp
CODE FOR SHUTDOWN, HIBERNATE, SLEEP (RESULTS INACCURATE)
--===== Insert Sample data into Temp Table
insert #savingstemp (pc_profile_id, shutdown_Time, hibernate_Time, sleep_Time, startup_Time, status, subpolicy_name, building_name, day_hour_rate, night_hour_rate, pc_kwh_rate)
SELECT '2', 'NULL', '02/01/2009 11:02:08', 'NULL', '02/01/2009 16:03:03', 'CLOSED', 'Exempt', 'ORB', '1.2','3.9', '1.9' UNION ALL
SELECT '3', '04/09/2009 11:00:17', 'NULL', 'NULL', '04/09/2009 16:03:00', 'CLOSED', 'Exempt', 'ORB', '1.2', '3.9', '1.9' UNION ALL
SELECT '4', 'NULL', 'NULL', '04/09/2009 14:00:17', '04/09/2009 17:03:00', 'CLOSED', 'Exempt', 'ORB', '1.2', '3.9', '1.9' UNION ALL
--===== Calculate hours off and savings
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 pc_profile_id, sum(datediff(hour, day_start, day_end)) as hours_off_day, sum(datediff(hour, night_start, night_end)) as hours_off_night
from (
select 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 period 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 period 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 period 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 de shutdown an start time
select 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 (
-- 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 10 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 pc_profile_id
) Savings on #savingstemp.pc_profile_id = Savings.pc_profile_id
select*
from #savingstemp
January 27, 2010 at 10:48 am
Interesting. I saw the exact same problem on this site, by another person, just a few months ago.
Different wording for the explanation, but the exact same rules.
Is this a classwork type thing? Something for a test?
Either way, a solution was worked out. I don't remember the title of the thread it was in, but if you search the site for a few of your keywords, it should find it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 27, 2010 at 10:59 am
GSquared,
thanks for the reply, that post that you maybe referring to http://www.sqlservercentral.com/Forums/Topic826285-1291-1.aspx was actually from me, I posted this problem a while back, however I only just began working with Stored Procedures and probably posted a little early before I knew exactly what the problem and desired solution was.
The code I have posted in this thread works very well exactly as i hoped but it only works for shutdown_Time as I explained when I need it to work for all three. I think its only something small in my COALESCE but maybe I'm wrong, I still consider my self a bit of a newbie so it could be an amateur mistake I'm not sure, apologies for any cross posting but i think the code i have posted now is very close to my desired solution. Thanks 🙂
January 27, 2010 at 3:31 pm
i cant get it to run w/o erroring. i've made a few changes and gave up
January 27, 2010 at 3:38 pm
insert #savingstemp (pc_profile_id, shutdown_Time, startup_Time, status, subpolicy_name, building_name, day_hour_rate, night_hour_rate, pc_kwh_rate)
SELECT '2', '02/01/2009 11:02:08', '02/01/2009 16:03:03', 'CLOSED', 'Exempt', 'ORB', '1.2','3.9', '1.9' UNION ALL
SELECT '3', '04/09/2009 11:00:17', '04/09/2009 16:03:00', 'CLOSED', 'Exempt', 'ORB', '1.2', '3.9', '1.9' UNION ALL
===========================================================================
--that wont work nor will the below, the above has extra "union all" the below is trying to insert the string 'NULL' into a nullable datetime. it needs to be null not 'null'. Did u run this before submitting? Got me over here cleanig up your code before i can even attempt to fix. Not cool.
============================================================================
insert #savingstemp (pc_profile_id, shutdown_Time, hibernate_Time, sleep_Time, startup_Time, status, subpolicy_name, building_name, day_hour_rate, night_hour_rate, pc_kwh_rate)
SELECT '2', NULL, '02/01/2009 11:02:08', NULL, '02/01/2009 16:03:03', 'CLOSED', 'Exempt', 'ORB', '1.2','3.9', '1.9' UNION ALL
SELECT '3', '04/09/2009 11:00:17', NULL, NULL, '04/09/2009 16:03:00', 'CLOSED', 'Exempt', 'ORB', '1.2', '3.9', '1.9' UNION ALL
SELECT '4', NULL, NULL, '04/09/2009 14:00:17', '04/09/2009 17:03:00', 'CLOSED', 'Exempt', 'ORB', '1.2', '3.9', '1.9'
January 27, 2010 at 3:38 pm
--here is ur code w/o the two errors mentioned above
drop table #savingstemp
--===== Create the test table with
CREATE TABLE #savingstemp
(
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),
building_name varchar(50),
hours_off_day int,
day_hour_rate float,
hours_off_night int,
night_hour_rate float,
pc_kwh_rate float,
savings float
)
--===== Insert Sample data into Temp Table
insert #savingstemp (pc_profile_id, shutdown_Time, startup_Time, status, subpolicy_name, building_name, day_hour_rate, night_hour_rate, pc_kwh_rate)
SELECT '2', '02/01/2009 11:02:08', '02/01/2009 16:03:03', 'CLOSED', 'Exempt', 'ORB', '1.2','3.9', '1.9' UNION ALL
SELECT '3', '04/09/2009 11:00:17', '04/09/2009 16:03:00', 'CLOSED', 'Exempt', 'ORB', '1.2', '3.9', '1.9'
--===== Calculate Hours off and Savings
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 pc_profile_id, sum(datediff(hour, day_start, day_end)) as hours_off_day, sum(datediff(hour, night_start, night_end)) as hours_off_night
from (
select pc_profile_id, status, subpolicy_name, day_hour_rate, night_hour_rate, pc_kwh_rate,
shutdown_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 shutdown_Time < start1 then start1 else case when shutdown_Time < finish1 then shutdown_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 shutdown_Time < start1 then start1 else case when shutdown_Time < finish1 then shutdown_Time else finish1 end end
end as day_start,
-- Either returns the finish of the day period 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 period 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 period 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 de shutdown an start time
select pc_profile_id, shutdown_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, shutdown_time), 120), 11) + '07:00:00', 120) start1,
-- Finish time for the day period
convert(datetime, left(convert(varchar, dateadd(d, offset, shutdown_time), 120), 11) + '22:59:00', 120) finish1,
-- Start time for the night period
convert(datetime, left(convert(varchar, dateadd(d, offset, shutdown_time), 120), 11) + '23:00:00', 120) start2,
-- Finish time for the night period
convert(datetime, left(convert(varchar, dateadd(d, offset + 1, shutdown_time), 120), 11) + '06:59:00', 120) finish2, offset
from #savingstemp 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 10 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 pc_profile_id
) Savings on #savingstemp.pc_profile_id = Savings.pc_profile_id
select*
from #savingstemp
--CODE FOR SHUTDOWN, HIBERNATE, SLEEP (RESULTS INACCURATE)
--===== Insert Sample data into Temp Table
--===== Insert Sample data into Temp Table
insert #savingstemp (pc_profile_id, shutdown_Time, hibernate_Time, sleep_Time, startup_Time, status, subpolicy_name, building_name, day_hour_rate, night_hour_rate, pc_kwh_rate)
SELECT '2', NULL, '02/01/2009 11:02:08', NULL, '02/01/2009 16:03:03', 'CLOSED', 'Exempt', 'ORB', '1.2','3.9', '1.9' UNION ALL
SELECT '3', '04/09/2009 11:00:17', NULL, NULL, '04/09/2009 16:03:00', 'CLOSED', 'Exempt', 'ORB', '1.2', '3.9', '1.9' UNION ALL
SELECT '4', NULL, NULL, '04/09/2009 14:00:17', '04/09/2009 17:03:00', 'CLOSED', 'Exempt', 'ORB', '1.2', '3.9', '1.9'
select *
from #savingstemp
--===== Calculate hours off and savings
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 pc_profile_id, sum(datediff(hour, day_start, day_end)) as hours_off_day, sum(datediff(hour, night_start, night_end)) as hours_off_night
from (
select 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 period 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 period 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 period 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 de shutdown an start time
select 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 (
-- 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 10 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 pc_profile_id
) Savings on #savingstemp.pc_profile_id = Savings.pc_profile_id
select*
from #savingstemp
January 27, 2010 at 3:45 pm
when u say crazy results can you explain. Are you saying the two chunks of sql should return the same result set?
January 27, 2010 at 3:53 pm
now i know this may sound stupid but i'm trying to figure out what your saying your problem is. if you saying you that both result set should return the same result set then they will never because you have two separate inserts to the same #savingstemp table w the same pc_profile_id. Therefore your result set will return more than one record for each instance you do that. Notice the below will insert two records for pc_profile_id 2 and 3
--first insert
insert #savingstemp (pc_profile_id, shutdown_Time, startup_Time, status, subpolicy_name, building_name, day_hour_rate, night_hour_rate, pc_kwh_rate)
SELECT '2', '02/01/2009 11:02:08', '02/01/2009 16:03:03', 'CLOSED', 'Exempt', 'ORB', '1.2','3.9', '1.9' UNION ALL
SELECT '3', '04/09/2009 11:00:17', '04/09/2009 16:03:00', 'CLOSED', 'Exempt', 'ORB', '1.2', '3.9', '1.9'
--second insert
insert #savingstemp (pc_profile_id, shutdown_Time, hibernate_Time, sleep_Time, startup_Time, status, subpolicy_name, building_name, day_hour_rate, night_hour_rate, pc_kwh_rate)
SELECT '2', NULL, '02/01/2009 11:02:08', NULL, '02/01/2009 16:03:03', 'CLOSED', 'Exempt', 'ORB', '1.2','3.9', '1.9' UNION ALL
SELECT '3', '04/09/2009 11:00:17', NULL, NULL, '04/09/2009 16:03:00', 'CLOSED', 'Exempt', 'ORB', '1.2', '3.9', '1.9' UNION ALL
SELECT '4', NULL, NULL, '04/09/2009 14:00:17', '04/09/2009 17:03:00', 'CLOSED', 'Exempt', 'ORB', '1.2', '3.9', '1.9'
January 27, 2010 at 3:54 pm
--this version has unique pc+profile_id's
drop table #savingstemp
--===== Create the test table with
CREATE TABLE #savingstemp
(
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),
building_name varchar(50),
hours_off_day int,
day_hour_rate float,
hours_off_night int,
night_hour_rate float,
pc_kwh_rate float,
savings float
)
--===== Insert Sample data into Temp Table
insert #savingstemp (pc_profile_id, shutdown_Time, startup_Time, status, subpolicy_name, building_name, day_hour_rate, night_hour_rate, pc_kwh_rate)
SELECT '2', '02/01/2009 11:02:08', '02/01/2009 16:03:03', 'CLOSED', 'Exempt', 'ORB', '1.2','3.9', '1.9' UNION ALL
SELECT '3', '04/09/2009 11:00:17', '04/09/2009 16:03:00', 'CLOSED', 'Exempt', 'ORB', '1.2', '3.9', '1.9'
--===== Calculate Hours off and Savings
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 pc_profile_id, sum(datediff(hour, day_start, day_end)) as hours_off_day, sum(datediff(hour, night_start, night_end)) as hours_off_night
from (
select pc_profile_id, status, subpolicy_name, day_hour_rate, night_hour_rate, pc_kwh_rate,
shutdown_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 shutdown_Time < start1 then start1 else case when shutdown_Time < finish1 then shutdown_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 shutdown_Time < start1 then start1 else case when shutdown_Time < finish1 then shutdown_Time else finish1 end end
end as day_start,
-- Either returns the finish of the day period 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 period 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 period 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 de shutdown an start time
select pc_profile_id, shutdown_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, shutdown_time), 120), 11) + '07:00:00', 120) start1,
-- Finish time for the day period
convert(datetime, left(convert(varchar, dateadd(d, offset, shutdown_time), 120), 11) + '22:59:00', 120) finish1,
-- Start time for the night period
convert(datetime, left(convert(varchar, dateadd(d, offset, shutdown_time), 120), 11) + '23:00:00', 120) start2,
-- Finish time for the night period
convert(datetime, left(convert(varchar, dateadd(d, offset + 1, shutdown_time), 120), 11) + '06:59:00', 120) finish2, offset
from #savingstemp 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 10 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 pc_profile_id
) Savings on #savingstemp.pc_profile_id = Savings.pc_profile_id
select*
from #savingstemp
--CODE FOR SHUTDOWN, HIBERNATE, SLEEP (RESULTS INACCURATE)
--===== Insert Sample data into Temp Table
--===== Insert Sample data into Temp Table
insert #savingstemp (pc_profile_id, shutdown_Time, hibernate_Time, sleep_Time, startup_Time, status, subpolicy_name, building_name, day_hour_rate, night_hour_rate, pc_kwh_rate)
SELECT '5', NULL, '02/01/2009 11:02:08', NULL, '02/01/2009 16:03:03', 'CLOSED', 'Exempt', 'ORB', '1.2','3.9', '1.9' UNION ALL
SELECT '6', '04/09/2009 11:00:17', NULL, NULL, '04/09/2009 16:03:00', 'CLOSED', 'Exempt', 'ORB', '1.2', '3.9', '1.9' UNION ALL
SELECT '7', NULL, NULL, '04/09/2009 14:00:17', '04/09/2009 17:03:00', 'CLOSED', 'Exempt', 'ORB', '1.2', '3.9', '1.9'
select *
from #savingstemp
--===== Calculate hours off and savings
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 pc_profile_id, sum(datediff(hour, day_start, day_end)) as hours_off_day, sum(datediff(hour, night_start, night_end)) as hours_off_night
from (
select 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 period 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 period 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 period 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 de shutdown an start time
select 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 (
-- 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 10 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 pc_profile_id
) Savings on #savingstemp.pc_profile_id = Savings.pc_profile_id
select*
from #savingstemp
January 27, 2010 at 4:34 pm
Thanks for the reply, I just found out what is wrong, firstly if more than one record has the same profile_ID it seems to be adding the answers together and its inaccurate. for example if you run the Stored procedure i posted here now (this is it up to date and complete) it will calculate my results correctly.....
but now change the last sample record to profile_ID 8 same as the previous one it will give me the wrong result. Unfortunately my real Data will have multiple records with he same profile_ID, i just added an extra column into each record with a unique ID and adjusted my code works well now.
Thanks for the posts, appreciate it
ALTER PROCEDURE dbo.StoredProcedure1
AS
--===== Create the test table with
CREATE TABLE #savingstemp
(
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),
building_name varchar(50),
hours_off_day int,
day_hour_rate float,
hours_off_night int,
night_hour_rate float,
pc_kwh_rate float,
savings float
)
--===== Calculate hours off and savings
insert #savingstemp (pc_profile_id, shutdown_Time, hibernate_Time, sleep_Time, startup_Time, status, subpolicy_name, building_name, day_hour_rate, night_hour_rate, pc_kwh_rate)
SELECT '5', NULL, '02/01/2009 11:02:08', NULL, '02/01/2009 16:03:03', 'CLOSED', 'Exempt', 'ORB', '1.2','3.9', '1.9' UNION ALL
SELECT '6', '04/09/2009 11:00:17', NULL, NULL, '04/09/2009 16:03:00', 'CLOSED', 'Exempt', 'ORB', '1.2', '3.9', '1.9' UNION ALL
SELECT '7', NULL, NULL, '09/04/2009 14:00:17', '09/05/2009 17:03:00', 'CLOSED', 'Exempt', 'ORB', '1.2', '3.9', '1.9' UNION ALL
SELECT '8', NULL, '04/09/2009 12:00:17', NULL, '04/09/2009 17:03:00', 'CLOSED', 'Exempt', 'ORB', '1.2', '3.9', '1.9' UNION ALL
SELECT '9', NULL, NULL, '09/04/2009 09:00:17', '09/04/2009 12:03:00', 'CLOSED', 'Exempt', 'ORB', '1.2', '3.9', '1.9'
select *
from #savingstemp
--===== Calculate hours off and savings
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 pc_profile_id, sum(datediff(hour, day_start, day_end)) as hours_off_day, sum(datediff(hour, night_start, night_end)) as hours_off_night
from (
select 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 period 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 period 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 period 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 de shutdown an start time
select 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 (
-- 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 10 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 pc_profile_id
) Savings on #savingstemp.pc_profile_id = Savings.pc_profile_id
select*
from #savingstemp
RETURN
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply