May 5, 2010 at 3:14 am
I'm trying to calculate the time a computer is on, I simply have a on_time and an off_time, I need to calculate the time the computer is on between 2 different rates so I can calculate savings. For example a day_rate which is from 07:00 - 22:59 and a night rate from 23:00 - 06:59. the following is my current stored procedure with sample Data, if you run it you can see the 1st record calculates perfectly (it being on for nearly 5hours on the day rate and just over 2 hours on the night rate) however the 2nd record does not calculate correctly, the problem seems to be if the time starts on the night_rate and runs into the day_rate it only calculates the hours the computer is on in the day_rate not the night rate.
Apolgises if i havent explained my problem well but if you run the sample stored procedure you will see my problem. Would apprecite any help as im not too sure how to resolve this and if my SQL is correct.
Thanks
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
May 5, 2010 at 7:48 am
this is more or less business logic failure/misunderstanding by you ? . i would suggest break your SP( select statement ) in chunks then verify one by one.if you feel that you are unable to convert your business requirement in Sql code , we are here to help you.:-)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 5, 2010 at 9:07 am
I think you will find this link very interesting. I didn't follow everything you were doing in your TSQL, but I think this may help.
Let me know if I'm off base and I'll see if I can help in another way.
May 5, 2010 at 9:17 am
Thanks for the replies, this is all a little over my head but ill have a read of that article and see if i can come up with anything as i nearly have it calculating 100% accurately, appreciate the help and info 🙂
May 8, 2010 at 6:55 am
Hi,
this is quite an interesting problem. I never had to do anything with such calculations, but I was interested how I would solve it and here is what I came up with. I haven't tested that on a larger amount of data, just on your sample (plus I added a few rows to your table, representing some potentially "dangerous" possibilities).
So, this is a change to data in your posted table:
DELETE FROM #costtemp
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', '20090911 18:12:10', '20090912 01:12:50', 'CLOSED', 'Manage', '1.2','3.9', '1.9' UNION ALL
SELECT '9', '9', '20090909 05:00:00', '20090909 12:00:00', 'CLOSED', 'Finance', '1.2','3.9', '1.9' UNION ALL
/*PC is ON through several rate changes during less than 24 hours*/
SELECT '10', '10', '20090911 06:31:15', '20090912 01:12:50', 'CLOSED', 'test1', '1.2','3.9', '1.9' UNION ALL
/*PC is ON for several days in a row*/
SELECT '11', '11', '20090924 05:47:33', '20091001 14:22:41', 'CLOSED', 'test2', '1.2','3.9', '1.9'
I introduce a new table of rates - it makes calculation easier, and it allows easy changes. You should always prefer that over hardcoding the times into the query.
CREATE TABLE #rates(starts CHAR(8), ends CHAR(8), rate CHAR(1))
INSERT INTO #rates (starts, ends, rate) VALUES ('00:00:00', '07:00:00', 'N')
INSERT INTO #rates (starts, ends, rate) VALUES ('07:00:00', '23:00:00', 'D')
INSERT INTO #rates (starts, ends, rate) VALUES ('23:00:00', '24:00:00', 'N')
Since we are in SQL2005, I used common table expressions - these should make the code more readable. Solution is based on assumption that cost_id identifies unique row in your table. If there can be several rows with the same cost_id, you'd have to add an identity column and use that instead.
WITH split(cost_id, time_start, time_end)
AS
/*even if it is possible for a PC to be ON for several days uninterrupted, I can work just with time first and add whole days at the end... each whole day always has the same number of day and night hours
this splits rows at midnight to make calculation easier - time_end is always greater than time_start*/
(SELECT cost_id, CONVERT(VARCHAR(8),on_time,14) as time_start,
CASE WHEN CONVERT(VARCHAR(8),on_time,14) <= CONVERT(VARCHAR(8),off_time,14)
THEN CONVERT(VARCHAR(8),off_time,14)
ELSE '23:59:59' END as time_end
FROM #costtemp c
WHERE [status] = 'CLOSED'
UNION ALL
SELECT cost_id, '00:00:00' as time_start, CONVERT(VARCHAR(8),off_time,14) as time_end
FROM #costtemp c
WHERE [status]='CLOSED' AND CONVERT(VARCHAR(8),on_time,14) > CONVERT(VARCHAR(8),off_time,14)),
rated(cost_id, rate, rstart, rend)
AS
/*this assignes rates to each of the split rows (and there can be several rates applied to 1 row)*/
(SELECT s.cost_id, r.rate,
CASE WHEN s.time_start > r.starts THEN s.time_start ELSE r.starts END as rstart,
CASE WHEN s.time_end < r.ends THEN s.time_end ELSE r.ends END as rend
FROM split s
LEFT JOIN #rates r ON r.starts <= s.time_end AND r.ends > time_start),
calc(cost_id, rate, seconds, hours)
/*this calculates time ON for each rate*/
AS
(SELECT cost_id, rate,
SUM(DATEDIFF(ss, '19000101 '+rstart, '19000101 '+rend)) as seconds,
SUM(DATEDIFF(ss, '19000101 '+rstart, '19000101 '+rend))/3600.00 as hours
FROM rated
GROUP BY cost_id, rate),
merged(cost_id, day_hours, night_hours)
/*now merge the rows again for an easy update*/
AS
(SELECT cost_id, SUM(CASE WHEN rate='D' THEN hours ELSE 0 END), SUM(CASE WHEN rate='N' THEN hours ELSE 0 END)
FROM calc
GROUP BY cost_id
)
/*and that's the update - now I have to add whole days, if the PC was on for more than 24 hours
this is not 100% clean because the day/night ratio (16:8) is hardcoded; would be better to read it from table #rates,
but I didn't want to make it look too complicated - you can change that*/
UPDATE cost
SET hours_on_day = ISNULL(m.day_hours,0) + DATEDIFF(ss, on_time, off_time)/86400 * 16,
hours_on_night = ISNULL(m.night_hours,0) + DATEDIFF(ss, on_time, off_time)/86400 * 8,
cost = (ISNULL(m.day_hours,0) + DATEDIFF(ss, on_time, off_time)/86400 * 16) * day_hour_rate * pc_kwh_rate
+ (ISNULL(m.night_hours,0) + DATEDIFF(ss, on_time, off_time)/86400 * 8) * night_hour_rate * pc_kwh_rate
FROM #costtemp cost
LEFT JOIN merged m ON m.cost_id=cost.cost_id
Well, as I warned, I'm not a specialist for this kind of calculation, so I'm not saying this is the best solution. This is how I would do it if I would have to rely on my own knowledge, without any help... and if nothing else, it might give you some ideas.
Good luck!
May 8, 2010 at 7:41 am
Just tested it on some 400.000 rows and it runs about 1 minute. On 1 million rows it took 2:15.
That's not too good, but since I didn't try to optimize the solution and the only index I have is on cost_id, it isn't really bad either.
May 8, 2010 at 10:59 pm
Vladan (5/8/2010)
Hi,this is quite an interesting problem. I never had to do anything with such calculations, but I was interested how I would solve it and here is what I came up with. I haven't tested that on a larger amount of data, just on your sample (plus I added a few rows to your table, representing some potentially "dangerous" possibilities).
So, this is a change to data in your posted table:
DELETE FROM #costtemp
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', '20090911 18:12:10', '20090912 01:12:50', 'CLOSED', 'Manage', '1.2','3.9', '1.9' UNION ALL
SELECT '9', '9', '20090909 05:00:00', '20090909 12:00:00', 'CLOSED', 'Finance', '1.2','3.9', '1.9' UNION ALL
/*PC is ON through several rate changes during less than 24 hours*/
SELECT '10', '10', '20090911 06:31:15', '20090912 01:12:50', 'CLOSED', 'test1', '1.2','3.9', '1.9' UNION ALL
/*PC is ON for several days in a row*/
SELECT '11', '11', '20090924 05:47:33', '20091001 14:22:41', 'CLOSED', 'test2', '1.2','3.9', '1.9'
I introduce a new table of rates - it makes calculation easier, and it allows easy changes. You should always prefer that over hardcoding the times into the query.
CREATE TABLE #rates(starts CHAR(8), ends CHAR(8), rate CHAR(1))
INSERT INTO #rates (starts, ends, rate) VALUES ('00:00:00', '07:00:00', 'N')
INSERT INTO #rates (starts, ends, rate) VALUES ('07:00:00', '23:00:00', 'D')
INSERT INTO #rates (starts, ends, rate) VALUES ('23:00:00', '24:00:00', 'N')
Since we are in SQL2005, I used common table expressions - these should make the code more readable. Solution is based on assumption that cost_id identifies unique row in your table. If there can be several rows with the same cost_id, you'd have to add an identity column and use that instead.
WITH split(cost_id, time_start, time_end)
AS
/*even if it is possible for a PC to be ON for several days uninterrupted, I can work just with time first and add whole days at the end... each whole day always has the same number of day and night hours
this splits rows at midnight to make calculation easier - time_end is always greater than time_start*/
(SELECT cost_id, CONVERT(VARCHAR(8),on_time,14) as time_start,
CASE WHEN CONVERT(VARCHAR(8),on_time,14) <= CONVERT(VARCHAR(8),off_time,14)
THEN CONVERT(VARCHAR(8),off_time,14)
ELSE '23:59:59' END as time_end
FROM #costtemp c
WHERE [status] = 'CLOSED'
UNION ALL
SELECT cost_id, '00:00:00' as time_start, CONVERT(VARCHAR(8),off_time,14) as time_end
FROM #costtemp c
WHERE [status]='CLOSED' AND CONVERT(VARCHAR(8),on_time,14) > CONVERT(VARCHAR(8),off_time,14)),
rated(cost_id, rate, rstart, rend)
AS
/*this assignes rates to each of the split rows (and there can be several rates applied to 1 row)*/
(SELECT s.cost_id, r.rate,
CASE WHEN s.time_start > r.starts THEN s.time_start ELSE r.starts END as rstart,
CASE WHEN s.time_end < r.ends THEN s.time_end ELSE r.ends END as rend
FROM split s
LEFT JOIN #rates r ON r.starts <= s.time_end AND r.ends > time_start),
calc(cost_id, rate, seconds, hours)
/*this calculates time ON for each rate*/
AS
(SELECT cost_id, rate,
SUM(DATEDIFF(ss, '19000101 '+rstart, '19000101 '+rend)) as seconds,
SUM(DATEDIFF(ss, '19000101 '+rstart, '19000101 '+rend))/3600.00 as hours
FROM rated
GROUP BY cost_id, rate),
merged(cost_id, day_hours, night_hours)
/*now merge the rows again for an easy update*/
AS
(SELECT cost_id, SUM(CASE WHEN rate='D' THEN hours ELSE 0 END), SUM(CASE WHEN rate='N' THEN hours ELSE 0 END)
FROM calc
GROUP BY cost_id
)
/*and that's the update - now I have to add whole days, if the PC was on for more than 24 hours
this is not 100% clean because the day/night ratio (16:8) is hardcoded; would be better to read it from table #rates,
but I didn't want to make it look too complicated - you can change that*/
UPDATE cost
SET hours_on_day = ISNULL(m.day_hours,0) + DATEDIFF(ss, on_time, off_time)/86400 * 16,
hours_on_night = ISNULL(m.night_hours,0) + DATEDIFF(ss, on_time, off_time)/86400 * 8,
cost = (ISNULL(m.day_hours,0) + DATEDIFF(ss, on_time, off_time)/86400 * 16) * day_hour_rate * pc_kwh_rate
+ (ISNULL(m.night_hours,0) + DATEDIFF(ss, on_time, off_time)/86400 * 8) * night_hour_rate * pc_kwh_rate
FROM #costtemp cost
LEFT JOIN merged m ON m.cost_id=cost.cost_id
Well, as I warned, I'm not a specialist for this kind of calculation, so I'm not saying this is the best solution. This is how I would do it if I would have to rely on my own knowledge, without any help... and if nothing else, it might give you some ideas.
Good luck!
i couldnt find indexes on these temp tables. are they missing or i overlooked ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 9, 2010 at 1:25 am
Bhuvnesh,
you're right, there are no indexes in the posted code.
I created an index only later when I was testing the performance and didn't post the code. Here it is : create index idx1 on #costtemp(cost_id)
May 9, 2010 at 6:45 am
Hey guys,
Thanks for the posts, apologizes for not getting back sooner I just didn't have a chance to have a good look at the SQL. My original post showed the problem I was having, I've been trying to do this on and off for a few months now, only started working with Stored Procedures a few months ago so I'm still a bit of a newbie so trying to learn as I go. I nearly have my problem solved as explained in the 1st post it just doesn't calculate the savings if its start in 'night rate' and runs into the 'day rate', I also need to be able to grab the times from a table instead of having them Hard coded In.
The way you have done it Vladan looks great, 100% accurate, would you feel that is a more suitable method than my original post? I was hoping it was only a small error which I could fix easy? I may have large volumes of Data, possible hundreds of thousands, thanks for help really appreciate it, as I said I'm still only learning so really do appreciate the advice 🙂
May 10, 2010 at 12:08 am
nialltuohy (5/9/2010)
would you feel that is a more suitable method than my original post?
Well it depends. 🙂 . Better to test Vladan's query with heavy data volume on testing region and dont forget to use indexes on temp tables (i would go with clus indexes) then see the exec plan ( also post it here) and time taken.that can show the things clearer.
frankly speaking, i also tried to create indexes on temp tables and study the exec plan but i could see much improved but main thing is the data was very less (the sample data you posted).
So better to test with heavy data.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 10, 2010 at 12:58 am
nialltuohy (5/9/2010)
Would you feel that is a more suitable method than my original post? I was hoping it was only a small error which I could fix easy? I may have large volumes of Data, possible hundreds of thousands.
To be honest, I concentrated on finding how I would do it and didn't analyze your code, so I'm not in the position to compare them. If I have time, I'll look into that later.
I was trying to make the calculation as simple as possible, that led me to ignoring dates, working with time only during the process and adding the whole days at the end. My first attempts failed, because I wasn't able to write the code properly if ON time was greater than OFF time (i.e. session started in the afternoon and ended in the morning next day), so I decided to add one step and split the sessions by midnight... and that worked. It is possible that I completely overlooked some easier way to solve it, as I already warned, I never needed such calculations in real life 🙂
As Bhuvnesh said, you should test the solution with your real data, so that you can see how it performs. I also tried to add some more indexes, but it didn't seem to have any traceable impact on performance - but again, you have to test this on real data. Hundreds of thousands rows shouldn't be any problem, if you need to do this calculation once a day or so. If you need to calculate such amount every few minutes, well than you probably should look for ways how to do it better - but I don't suppose that's the case.
May 11, 2010 at 8:47 am
Vladan,
Thanks for the reply, the way you have done it is very simple and for some one like myself who is learning its easy to understand, appreciate the help 🙂 One last question, as you have mentioned I should be taking the times from a table in my database, I'm hoping to do this, I have 4 Datetime fields in a table which are DAY_RATE_BEGIN, DAY_RATE_END, NIGHT_RATE_BEGIN and NIGHT_RATE_END. I was hoping to take these as my times and use them as my time periods for the calculation, I'm not sure how to do this i know i can just create a temp table and take the data from the table, but I'm guessing I should do this a certain way
CREATE TABLE #rates
(
day_rate_start datetime,
day_rate_end datetime,
night_rate_start datetime,
night_rate_end datetime
)
INSERT INTO #rates (day_rate_start, day_rate_end, night_rate_start, night_rate_end)
SELECT DAY_RATE_BEGIN, DAY_RATE_END, NIGHT_RATE_BEGIN, NIGHT_RATE_END
FROM TBL_ELECTRICTY_RATES;
Thanks for the help again 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply