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