Post reply

streamlining sql for a defined week columns

  • December 22, 2008 at 11:17 am

    #197771

    Hi

    i am developing a report that requires a sum of hours for each week of the 52 weeks of the year. currently the sql looks something like this:-

    select personnum, personfullname, applydate, timeinseconds

    case

    when applydate>=01/01/08 and <=08/01/12 then timeinseconds else 0 end as week 1

    when applydate>=09/01/08 and <=06/01/12 then timeinseconds else 0 end as week 2

    etc.

    this not surprisingly taking forever to run. Infact, I haven't had the patients to wait for it. The database currently has 3 months worth of data and about 15% of the data that the database will have when the current project is fully implemented. Can anyone help me streamline the code, suggest another way of defining the columns or will it be a case of pre-scheduling the report run at 4am??

  • December 22, 2008 at 12:05 pm

    #916303

    The code you posted isn't even in valid SQL syntax.

    Please supply your actual query code, table definitions, sample data, and your expected output with the sample data.

  • December 22, 2008 at 12:48 pm

    #916333

    The reason it takes so long is because you've not aggregated the data. Please see the following for how to do a proper cross tab...

    [font="Arial Black"]http://www.sqlservercentral.com/articles/T-SQL/63681/[/font][/url]

    Without some actual test data provided in a format similar to that in the link in my signature, that's about the best I can do.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply