COALESCE Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

COALESCE Problem

Author

Topic

nt86
Yak Posting Veteran

54 Posts

Posted - 2010-01-27 : 06:08:58

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 :(

Thanks

CODE FOR SHUTDOWN (WORKING CORRECTLY)


ALTER PROCEDURE dbo.TEST_2RATES
	
AS

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 #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

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 #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


 /** Get hours off between Shutdown and Startup times*/

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

niall

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-27 : 08:32:43

You may want to put your nested CASE statements in Parenthasis and see what happens. It could be picking off the "else case when COALESCE( shutdown_Time, hibernate_Time, sleep_Time) < finish1... " as an else for the outer most case, since it is recommended to have an else, but not required.

Try nesting them properly and see if the logic is doing what you suspect.

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 08:43:46

I haven't looked at your SQL closely )sorry, not enough time today

), but I'm interested in what you are monitoring this for - being that I am interested in "green projects". Dunno if you are able to share any background on what this is for?

nt86
Yak Posting Veteran

54 Posts

Posted - 2010-01-27 : 09:59:09

Thanks for the advice, I tired that but unfortunately Im still getting crazy results, now its executing and returning what it should but its not actually returning the correct answer.

Thanks also Kristen for your reply, this is just a small project I am doing as part of my college course. This is an element Im unfortunately unable to get working correctly.

My SQL seems correct in my eyes anyway so not sure why its not working correctly, i think it maybe be something to do with my COALESCE statements not sure :(

quote:
Originally posted by DP978

You may want to put your nested CASE statements in Parenthasis and see what happens. It could be picking off the "else case when COALESCE( shutdown_Time, hibernate_Time, sleep_Time) < finish1... " as an else for the outer most case, since it is recommended to have an else, but not required.

Try nesting them properly and see if the logic is doing what you suspect.

niall

nt86
Yak Posting Veteran

54 Posts

Posted - 2010-01-27 : 18:38:56

I actually found out what my problem, my records had duplicate profile _Id and it was accumulating my results, so i just added a extra unique identifier field for each record and used that, works great now thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources