SELECT to make one row from two

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SELECT to make one row from two

Author

Topic

lokelo
Starting Member

2 Posts

Posted - 2007-12-10 : 19:45:53

I've got a table employee_hours

create table employee_hours (
employee_id int,
charge_code varchar(20),
charge_date datetime,
hours	numeric(5,1),
billable bit
)

that keeps track of employee hours and whether or not the time charged is billable.

now assume that there is the following data in the table
employee_id|charge_code|charge_date|hours|billable
1999|ABC123|12/1/2007|2.0|1
1999|DEF123|12/1/2007|2.0|1
1999|EFG123|12/1/2007|2.0|0
1999|EFS123|12/1/2007|2.0|0

I would like to make a select statement that gave me the following columns in a single row.
employee_id|charge_date|billable_hours|non_billable_hours|total_hours
1999|12/1/2007|4|4|8

Any assistance would be greatly appreciated. I can't seem to get any amount of joins or group bys to work for me the way i need it. I'm using SQL Server 2000 if that matters.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-10 : 19:49:52

[code]
select
employee_id
,charge_date
,sum(case when billable=1 then hours else 0 end) as billable_hours
,sum(case when billable=0 then hours else 0 end) as non_billable_hours
,sum(hours) as total_hours
from employee_hours
group by employee_id, charge_date
order by employee_id, charge_date
[/code]

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources