Thread: making two columns out of one

I've got a table containing some timesheet data.
Fields are emp, earncode, lo_shift, hi_shift.

SELECT emp, sum(hi_shift - lo_shift) as reghrs from timesheet 
where earncode in ('R', 'C', 'X') order by emp

will give me all the regular hours

SELECT emp, sum(hi_shift - lo_shift) as ovrhrs from timesheet 
where earncode not in ('R', 'C', 'X') order by emp

will give me all the overtime hours.

How do I combine these to get one result set with emp, reghrs, ovrhrs on
each row.

Frank




________________________________________________________                          1stUp.com - Free the Web  Get your
freeInternet access at http://www.1stUp.com
 

It took quite a bit of trial and error, but I finally got it!

SELECT  emp,  SUM (CASE WHEN  earncode IN ('R','C','X')  THEN  (hi_shift-lo_shift)
ELSE  0  END)  AS  reghrs, SUM (CASE WHEN  earncode IN ('R','C','X')  THEN  0  ELSE
(hi_shift-lo_shift)  END)  AS  ovrhrs  FROM  timesheet GROUP BY  emp ORDER BY  emp;


At 09:34 AM 9/11/00 -0400, Frank Bax wrote:
>I've got a table containing some timesheet data.
>Fields are emp, earncode, lo_shift, hi_shift.
>
>SELECT emp, sum(hi_shift - lo_shift) as reghrs from timesheet 
>where earncode in ('R', 'C', 'X') order by emp
>
>will give me all the regular hours
>
>SELECT emp, sum(hi_shift - lo_shift) as ovrhrs from timesheet 
>where earncode not in ('R', 'C', 'X') order by emp
>
>will give me all the overtime hours.
>
>How do I combine these to get one result set with emp, reghrs, ovrhrs on
>each row.
>
>Frank