vito.mangogna (7/29/2014)
I'm trying to write a query that will return three related values from a single value.My input data in it's simplest form is this:
select employeeID, TotalHours from TimeTicket
I want to return a result set like this:
empIDTotHrs RegTmOvrTimeDoubleTime
---------------------------------------------------------------------------
`10013841
20011830
3007700
I want to break down the TotalHours into 3 values; RegularTime, OverTime and doubleTime. The business rule is the first 8 hours are RegularTime, the next 4 hours are OverTime and anything over 12 is DoubleTime.
I have looked at case, if-then and a number of other alternatives, but none of them are satisfactory.
This seems like a simple problem. Can anyone recommend a solution?
Sounds like a case for the CASE statement.
For example, to calculate the RegularTime: RegularTime = (CASE WHEN TotalHours <= 8 THEN TotalHours ELSE 8 END)
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]