Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Returning multiple values from a single value


Returning multiple values from a single value

Author
Message
vito.mangogna
vito.mangogna
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 3
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:

empID TotHrs RegTm OvrTime DoubleTime
---------------------------------------------------------------------------
` 100 13 8 4 1
200 11 8 3 0
300 7 7 0 0

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?
Alvin Ramard
Alvin Ramard
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2676 Visits: 11590
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:

empID TotHrs RegTm OvrTime DoubleTime
---------------------------------------------------------------------------
` 100 13 8 4 1
200 11 8 3 0
300 7 7 0 0

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

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
autoexcrement
autoexcrement
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 777
IF object_id('tempdb..#TimeTicket') IS NOT NULL
DROP TABLE #TimeTicket

CREATE TABLE #TimeTicket
(
employeeID INT NOT NULL,
TotalHours DECIMAL(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
vito.mangogna
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search