Computed Columns

Question

Computed Columns

Martin D. Cymerman

SSCrazy

Points: 2079
More actions
December 19, 2006 at 5:40 am

#115536

I’m writing a query to obtain results of OT hours worked. I have three columns: EMPNAME, Salary and Hourly.
How can I show an additional two columns for any hours over 40. One for Salary and one for Hourly.
Empname,Salary, Hourly,OT_Salary,OT_Hourly
Spock, 41, 0
Kirk, 45, 0
Smith , 0 , 49
I have thus far;
SELECT a.EMPNAME,
SUM((CASE a.fclass WHEN 'Salary' THEN b.TOTAL_1 ELSE 0 END)/60) AS Salary ,
SUM((CASE a.fclass WHEN 'Hourly' THEN b.TOTAL_1 ELSE 0 END)/60) AS Hourly
FROM Table a
Left Join Totals b
ON a.EMPNUM = b.EMPNUM
Where a.empnum like'0001%'
Group By a.EMPNAME
Thanks
CY

Viewing 5 posts - 1 through 5 (of 5 total)

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

DaveItz SSCrazy Points: 2146 More actions · Answer 1

I guess you can always do:

SELECT
   t.EMPNAME,
   (CASE WHEN t.Salary < 40 THEN t.Salary ELSE 40 END) As Salary,
   (CASE WHEN t.Hourly  40 THEN t.Salary-40 ELSE 0 END) As OT_Salary,
   (CASE WHEN t.Hourly > 40 THEN t.Hourly-40 ELSE 0 END) As OT_Hourly
FROM (
   SELECT a.EMPNAME,
   SUM((CASE a.fclass WHEN 'Salary' THEN b.TOTAL_1 ELSE 0 END)/60) AS Salary ,
   SUM((CASE a.fclass WHEN 'Hourly' THEN b.TOTAL_1 ELSE 0 END)/60) AS Hourly
   FROM Table a
   Left Join Totals b ON a.EMPNUM = b.EMPNUM
   Where a.empnum like'0001%'
   Group By a.EMPNAME
) t

Martin D. Cymerman SSCrazy Points: 2079 More actions · Answer 2

Martin D. Cymerman

SSCrazy

Points: 2079

December 19, 2006 at 6:13 am

#678895

Excellent Thanks!

CY

DaveItz SSCrazy Points: 2146 More actions · Answer 3

No problem.

I'm guessing you are allowing for employees who are employed twice, once as a salary and again as an hourly. Otherwise you can put a.fclass in the SELECT and GROUP BY clauses and stick to one Hours column and one OT_Hours column. Incidentally, to be careful, you probably want to group by a.EMPID, a.EMPNAME

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 4

Martin,

If the Total_1 column is any of the integer datatypes and (so it appears) that the Total_1 column is a total number of minutes worked, you may be in for a bit of a surprise... run the following query...

PRINT 45/60

... you don't get 0.75... you get zero, nada, SQUAT! If this is for a job tracking or payroll system, that kind of error will put you in the public chow lines while you wait for your next job to appear

Now... try this...

PRINT 45/60.0

...and you get the correct answer of 0.750000.

What kind of limits are you looking for on the columns? Decimal hours (eg. 43.4 hours) or hours and minutes (eg 43:24 or 43 hours 24 minutes) or what? If you only want whole hours, do you want to round, round down, or round up?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)