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



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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]