Post reply

Fairly complex SQL - Can it be done better in SP format?

  • March 3, 2010 at 6:56 am

    #386423

    This code works and I have 2 other columns I'm summing as well but was wondering if it would be more efficient in a SP type of format?

    I'm trying to find out the difference between hours worked and hours scheduled between 2 dates.

    If it can be done a better way, are there any examples or cursoring through the column and summing as you go..............Thanks for any assistance.

    (select sum((wfctimeinseconds/60) / 60)

    from vp_alltotals

    where ismoneyamountsw = 0

    and wfctimeinseconds <> 0

    and PERSONNUM = P1.PERSONNUM

    and applydate between @Begdate and P1.PREVPAYPERIODEND

    and paycodename = 'REGSalaried') - (substring(P1.PAYRULENAME,1,2)) * (DATEDIFF(ww, @Begdate, P1.PREVPAYPERIODEND )) as DIRLVAVAIL

    Regards,

    Joe

  • March 3, 2010 at 10:58 pm

    #1128271

    Hi Joe and welcome aboard.

    Most folks like to test their code before they post it but don't have the time to build test data to do it with. Keeping that in mind, I recommend you read the article at the following link... you'll probably get an answer in no time if you follow the methods in that article...

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    --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)

  • March 4, 2010 at 8:27 am

    #1128539

    Thanks Jeff!

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

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