Post reply

Recursive loop between a table and itself

  • June 18, 2014 at 4:19 pm

    #292512

    I have a simple table tblWorkedHours

    ID int,

    PayPeriod int,

    MonthEnd date,

    WorkedHours float,

    ContractHours float,

    OwedHours float,

    PrvsOwedHours float,

    FinalOwedHours float

    I have two calculated columns called PrvsOwedHours, FinalOwedHours.

    FinaOWedHours = OWedHOurs+PrvsOwedHours.

    PrvsOwedHours is the Previous months FinalOwedHours, that is carried forward into the next month.

    But what is the best way to do this...should I create a new table every month and link the two or is therd some way to do it recursively.

    I have no test data im afraid...

  • June 18, 2014 at 5:21 pm

    #1723032

    If those are calculated columns, why do you need them at all?

    You certainly don't need a table per month. On the worst scenario, you could use a self join.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • June 18, 2014 at 5:28 pm

    #1723033

    HI Thanks for your prompt reply.

    When I calculate the FinalOwedHours I need to bring it back into the calculation for the next month as PrvsOwedHours !

    SO I am not sure how to do that !

  • June 19, 2014 at 7:49 am

    #1723255

    I would recommend not using floats here. You should use an exact datatype like numeric instead. Once you start doing calculations and stuff using floats you will get rounding errors because float is an approximate datatype. Especially as this appears to be some sort of system involved in payroll you need to be precise.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • June 19, 2014 at 5:00 pm

    #1723442

    Thank you for that info.

    Your absolutely right I should use numeric as to avoid all sorts of rounding errors etc thank you.

    I have decided to create a table to solve my recursive problem and each month the user has to insert (via a form in vb.net) any employees who carry over owed hours.

    Its not elegant bit it will work and avoid a nasty recursive dilemma.

Viewing 5 posts - 1 through 4 (of 4 total)

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