Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Returning multiple values from a single value Expand / Collapse
Author
Message
Posted Tuesday, July 29, 2014 1:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 2:22 PM
Points: 2, Visits: 2
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?
Post #1597467
Posted Tuesday, July 29, 2014 1:25 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:51 AM
Points: 1,510, Visits: 8,470
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.
Post #1597474
Posted Tuesday, July 29, 2014 1:28 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 3:19 PM
Points: 157, Visits: 608
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
Post #1597478
Posted Tuesday, July 29, 2014 2:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 2:22 PM
Points: 2, Visits: 2
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.
Post #1597490
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse