Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    7

    Unanswered: Calculating Date/Time

    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....thanks


    ALTER 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 end
    WHERE 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*/

    RETURN

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    You need to break the problem down.

    There are two possibilities:

    1. startup_time - shutdown_time < 24 hours
    2. startup_time - shutdown_time >= 24 hours

    Now, it doesn't matter when you start counting, but in any 24 hour period, you'll get the same amount of day and night. (Imagine marking the night and day on a clock face. It doesn't matter how much you rotate the clock face, the amount of daytime or nighttime doesn't change.)

    If you subtract one day from the startup time, you know that it was on for the same number of day or night hours, no matter when that day started.

    So, to handle condition two, you figure out the full days between shutdown_time and startup_time and multiply that by the number of night hours per day. Then use your original code to handle the remainder.

    Once you've figured that out, my next question is... do you have to account for daylight saving time?!

  3. #3
    Join Date
    Nov 2009
    Posts
    7
    Quote Originally Posted by sco08y View Post
    You need to break the problem down.

    There are two possibilities:

    1. startup_time - shutdown_time < 24 hours
    2. startup_time - shutdown_time >= 24 hours

    Now, it doesn't matter when you start counting, but in any 24 hour period, you'll get the same amount of day and night. (Imagine marking the night and day on a clock face. It doesn't matter how much you rotate the clock face, the amount of daytime or nighttime doesn't change.)

    If you subtract one day from the startup time, you know that it was on for the same number of day or night hours, no matter when that day started.

    So, to handle condition two, you figure out the full days between shutdown_time and startup_time and multiply that by the number of night hours per day. Then use your original code to handle the remainder.

    Once you've figured that out, my next question is... do you have to account for daylight saving time?!

    Thanks for the reply, this has been a problem for me now for a few weeks, i understand what your saying but im still not certain how to calculate if it runs over both categories, I just have a hours_off_day and hours_off_night field in my temp table. I know if it runs over 24 hours it will be a total day but if its 42 hours for example even though ill know its 1 full day im still left with the same problem that ill have an additional 18 hours that will run over both rates.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •