|
nt86
Yak Posting Veteran
54 Posts |
 Posted - 2009-12-06 : 14:17:37
|
| I have a problem with writing a Stored procedure. I am populating a temp table from a view and is has two fields named shutdown_Time and startup_Time which will both have a date time in them. The temp table calculates the difference between the times and Dates to illustrate the hours the computer is off, from there i need to distinguish if the computer is off during day hours or during night hours. The day hours category is between 7:00AM and 23:00PM and the night hours category is between 23:00PM and 7:00AM. Now this is not the problem my current code calculates this correctly and inserts the hours saved day into a hours_off_day field and similarly for night inserts into hours_off_night field in my temp table, however if the time spans over both categories im unsure how to calculate the time the PC is off. For example if a computer Shutdowns at 06/12/09 20:00PM and startup at 07/12/09 11:00AM then it will need to calculate that it was off for 7 day_hours and 8 night_hours. Currently my code can only calculate when it is in 1 category or the other not if it runs between both. If anyone has any Ideas or suggestions on how to code and do this i would greatly appreciate it as Im not used to working with stored procedures, This is my code so far....thanksALTER PROCEDURE [dbo].[SP_SAVINGS_REPORT] /**@startup_time datetime, @hibernate_time datetime, @sleep_time datetime, @shutdown_time datetime, @total_hrs_off int @status varchar(50) @pc_profile_id int, @day_rate int, @night_rate int, @pc_power_rating int*/AS /* Create temp table */ CREATE TABLE #savingstemp ( pc_profile_id int, shutdown_Time datetime NULL, startup_Time datetime NULL, status varchar(50), total_hrs_off int, hours_off_day int, day_hour_rate float, hours_off_night int, night_hour_rate float, pc_kwh_rate float, total_savings float, next_day_midnight datetime ) /** Insert Values into Temp Table from View, including Day/Night Rates*/ insert into #savingstemp (pc_profile_id, shutdown_Time, startup_Time, status, day_hour_rate, night_hour_rate, pc_kwh_rate) SELECT PC_PROFILE_ID, SHUTDOWN_TIME, STARTUP_TIME, STATUS, DAY_RATE, NIGHT_RATE, PC_POWER_RATING FROM VIEW_TEMP /** 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 = case when DATEPART(hh, startup_Time) >= 7 AND DATEPART(hh, shutdown_Time) <= 23 then DATEDIFF(HOUR, shutdown_Time, startup_Time) else hours_off_day end, hours_off_night = case when DATEPART(hh, startup_Time) <= 7 AND DATEPART(hh, shutdown_Time) >= 23 then DATEDIFF(HOUR, shutdown_Time, startup_Time) else hours_off_night endWHERE STATUS = 'CLOSED'; /** Calculate the Total Savings, multiple hours * KWH Rate * Rate for both Day/Night Hours off*/ UPDATE #savingstemp SET total_savings = (isnull(hours_off_day, 0) * pc_kwh_rate * day_hour_rate) + (isnull(hours_off_night, 0) * pc_kwh_rate * night_hour_rate) /** Depending on report type, select sum(time*rate) from temp, group by pc, or date etc */ /** Return data from Temp Table*/ SELECT * FROM #savingstemp /** Execute Stored procedure*/ /**EXECUTE dbo.SP_SAVINGS_REPORT*/ /**Drop temporary table drop table #savingstemp*/ RETURNniall |
|
Calculating Date/Times in Stored Procedure