• Cadavre (11/26/2012)


    leesider (11/26/2012)


    I am going to need a cursor, I just don't know how to detect when a year or month datepart changes from date to date.

    No cursor required.

    Try this: -

    SELECT [YEAR] = YEAR(startup_Time),

    [MONTH_NUMBER] = DENSE_RANK() OVER(ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, 0, startup_Time), 0)),

    startup_Time

    FROM #KWHtemp;

    Apologies, the requirements have changed; the week number has to be which week the startup_Time is in the year not the week starting from zero. I.e. the week of the the year that the date is located in. For example, 2012-04-03 is in the fifteenth week of the year so if that date appears then the week number 15 should appear along side it. Each date has to have the week number of the year that it is located in.

    The code you posted is almost what I need but at the same time as that code is executing I need to execute an inner select from another table that also contains a startup_Time. I want to compare this startup_Time with the startup_Time in the outer select and as it goes from one week to the next, select from a column KWH_savings in the inner select.

    In pseudocode this would be somthing like below:

    Outer select here (year, week number, start_up time)

    Inner select (KWH_savings, startup_Time)

    when inner startup_Time is in the same week the same as outer startup_Time, sum the KWH_savings and output it for that week.

    close inner select

    close outer select

    I should end up with an output like this:

    Year week total_savings

    2012 10 72.2571543100001

    2012 11 629.06429487

    2012 12 1190.04593862

    2012 13 1957.97607841

    Sorry for the confusion.