Returning multiple values from a single value

Question

Returning multiple values from a single value

vito.mangogna

Newbie

Points: 8
More actions
July 29, 2014 at 1:14 pm

#289080

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?

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

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

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]

autoexcrement SSCertifiable Points: 5951 More actions · Answer 2

IF object_id('tempdb..#TimeTicket') IS NOT NULL

DROP TABLE #TimeTicket

CREATE TABLE #TimeTicket

(

employeeIDINTNOT NULL,

TotalHoursDECIMAL(4,2)NOT NULL

)

INSERT INTO #TimeTicket (employeeID, TotalHours)

SELECT 100, 13 UNION ALL

SELECT 200, 11 UNION ALL

SELECT 300, 7 UNION ALL

SELECT 400, 23.25 UNION ALL

SELECT 500, 3.5

SELECT

empID= employeeID,

TotHrs= TotalHours,

RegTm=

CASE

WHEN TotalHours >= 8 THEN 8

ELSE TotalHours

END,

OvrTime=

CASE

WHEN TotalHours >= 12 THEN 4

WHEN TotalHours >= 8 THEN TotalHours - 8

ELSE 0

END,

DoubleTime=

CASE

WHEN TotalHours > 12 THEN TotalHours - 12

ELSE 0

END

FROM #TimeTicket

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

vito.mangogna Newbie Points: 8 More actions · Answer 3

Ah, I see. Thank you. I tried case statements for a couple of hours, but I didn't think of putting them in that form.