Recursive loop between a table and itself

Question

Post reply

Recursive loop between a table and itself

Tallboy

SSCrazy

Points: 2457
More actions
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...

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

Tallboy SSCrazy Points: 2457 More actions · Answer 2

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 !

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

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/

Tallboy SSCrazy Points: 2457 More actions · Answer 4

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.