W.E.B

asked on

# SQL CASE WHEN

Hello,

I use below code, I'm having a problem adding a case statement.

Case Department WHEN 1 THEN '01-X1'

WHEN 2 THEN '02-X2'

WHEN 3 THEN '03-X3'

Else ''

end

Select emp_id, (Select DepartmentCode from Employees WHERE Employees.EmployeesId = hours_detail.emp_id) AS [Department],

(Select FirstName from Employees where Employees.EmployeesId = hours_detail.emp_id) as [FirstName],

(Select LastName from Employees where Employees.EmployeesId = hours_detail.emp_id) as [LastName],

(Select ChequeName from Employees where Employees.EmployeesId = hours_detail.emp_id) as [ChequeName],

Sum (regular) as Regular, Sum (overtime) as OverTime,Sum (overtime2) as OverTime2,

Sum (vacation) as Vacation, Sum (personal_paid) as personal_paid,Sum (personal_unpaid) as personal_unpaid,

Sum (statutory_paid) as statutory_paid, Sum (statutory_unpaid) as statutory_unpaid,

Sum (sick_paid) as sick_paid, Sum (sick_unpaid) as sick_unpaid

From hours_detail where DATE Between '2012-05-01 00:00:00.000' and '2013-04-30 23:59:59.000'

Group by hours_detail.emp_id

Any help is appreciated.

I use below code, I'm having a problem adding a case statement.

Case Department WHEN 1 THEN '01-X1'

WHEN 2 THEN '02-X2'

WHEN 3 THEN '03-X3'

Else ''

end

Select emp_id, (Select DepartmentCode from Employees WHERE Employees.EmployeesId = hours_detail.emp_id) AS [Department],

(Select FirstName from Employees where Employees.EmployeesId = hours_detail.emp_id) as [FirstName],

(Select LastName from Employees where Employees.EmployeesId = hours_detail.emp_id) as [LastName],

(Select ChequeName from Employees where Employees.EmployeesId = hours_detail.emp_id) as [ChequeName],

Sum (regular) as Regular, Sum (overtime) as OverTime,Sum (overtime2) as OverTime2,

Sum (vacation) as Vacation, Sum (personal_paid) as personal_paid,Sum (personal_unpaid) as personal_unpaid,

Sum (statutory_paid) as statutory_paid, Sum (statutory_unpaid) as statutory_unpaid,

Sum (sick_paid) as sick_paid, Sum (sick_unpaid) as sick_unpaid

From hours_detail where DATE Between '2012-05-01 00:00:00.000' and '2013-04-30 23:59:59.000'

Group by hours_detail.emp_id

Any help is appreciated.

Build this sql statement without access to SQLServer but should work

Used Left Join clause and referenced Employees in case statement

Select emp_id,

Case Employees.DepartmentCode

WHEN 1 THEN '01-X1'

WHEN 2 THEN '02-X2'

WHEN 3 THEN '03-X3'

Else ''

end,

(Select FirstName from Employees where Employees.EmployeesId = hours_detail.emp_id) as [FirstName],

(Select LastName from Employees where Employees.EmployeesId = hours_detail.emp_id) as [LastName],

(Select ChequeName from Employees where Employees.EmployeesId = hours_detail.emp_id) as [ChequeName],

Sum (regular) as Regular, Sum (overtime) as OverTime,Sum (overtime2) as OverTime2,

Sum (vacation) as Vacation, Sum (personal_paid) as personal_paid,Sum (personal_unpaid) as personal_unpaid,

Sum (statutory_paid) as statutory_paid, Sum (statutory_unpaid) as statutory_unpaid,

Sum (sick_paid) as sick_paid, Sum (sick_unpaid) as sick_unpaid

From hours_detail

Left join

Employees on Employees.EmployeesId = hours_detail.emp_id

where DATE Between '2012-05-01 00:00:00.000' and '2013-04-30 23:59:59.000'

Group by hours_detail.emp_id

Used Left Join clause and referenced Employees in case statement

Select emp_id,

Case Employees.DepartmentCode

WHEN 1 THEN '01-X1'

WHEN 2 THEN '02-X2'

WHEN 3 THEN '03-X3'

Else ''

end,

(Select FirstName from Employees where Employees.EmployeesId = hours_detail.emp_id) as [FirstName],

(Select LastName from Employees where Employees.EmployeesId = hours_detail.emp_id) as [LastName],

(Select ChequeName from Employees where Employees.EmployeesId = hours_detail.emp_id) as [ChequeName],

Sum (regular) as Regular, Sum (overtime) as OverTime,Sum (overtime2) as OverTime2,

Sum (vacation) as Vacation, Sum (personal_paid) as personal_paid,Sum (personal_unpaid) as personal_unpaid,

Sum (statutory_paid) as statutory_paid, Sum (statutory_unpaid) as statutory_unpaid,

Sum (sick_paid) as sick_paid, Sum (sick_unpaid) as sick_unpaid

From hours_detail

Left join

Employees on Employees.EmployeesId = hours_detail.emp_id

where DATE Between '2012-05-01 00:00:00.000' and '2013-04-30 23:59:59.000'

Group by hours_detail.emp_id

ASKER

Hello Matthewspatrick,

Your code is working but with 2 errors ,

Msg 209, Level 16, State 1, Line 14

Ambiguous column name 'personal_unpaid'.

Msg 209, Level 16, State 1, Line 18

Ambiguous column name 'sick_unpaid'.

I did try a left join, but had no success,

if you can help with a join, would be appreciated.

Your code is working but with 2 errors ,

Msg 209, Level 16, State 1, Line 14

Ambiguous column name 'personal_unpaid'.

Msg 209, Level 16, State 1, Line 18

Ambiguous column name 'sick_unpaid'.

I did try a left join, but had no success,

if you can help with a join, would be appreciated.

ASKER

Hell o5teveo,

I get errors running your code.

Column 'Employees.DepartmentCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

thanks

I get errors running your code.

Column 'Employees.DepartmentCode'

thanks

ASKER

Here is the hours_detail table

id int

emp_id int

date datetime

regular real

overtime real

overtime2 real

vacation real

personal_paid real

personal_unpaid real

statutory_paid real

statutory_unpaid real

sick_paid real

sick_unpaid real

id int

emp_id int

date datetime

regular real

overtime real

overtime2 real

vacation real

personal_paid real

personal_unpaid real

statutory_paid real

statutory_unpaid real

sick_paid real

sick_unpaid real

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Beautiful,

thank you very much

thank you very much

Open in new window