July 29, 2014 at 1:14 pm
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?
July 29, 2014 at 1:25 pm
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]
July 29, 2014 at 1:28 pm
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
July 29, 2014 at 2:33 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply