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.
W.E.BAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
Trying again:

Select h.emp_id, 
CASE e.DepartmentCode WHEN 1 THEN '01-X1'
    WHEN 2 THEN '02-X2'
    WHEN 3 THEN '03-X3'
    ELSE '' END AS [Department],
e.FirstName,
e.LastName,
e.ChequeName,
Sum (h.regular) as Regular, 
Sum (h.overtime) as OverTime,
Sum (h.overtime2) as OverTime2,
Sum (h.vacation) as Vacation, 
Sum (h.personal_paid) as personal_paid,
Sum (h.personal_unpaid) as personal_unpaid,
Sum (h.statutory_paid) as statutory_paid, 
Sum (h.statutory_unpaid) as statutory_unpaid,
Sum (h.sick_paid) as sick_paid, 
Sum (h.sick_unpaid) as sick_unpaid
From  hours_detail h INNER JOIN Employees e ON h.emp_id = e.EmployeesId
where h.DATE Between '2012-05-01 00:00:00.000' and '2013-04-30 23:59:59.000' 
Group by h.emp_id, 
CASE e.DepartmentCode WHEN 1 THEN '01-X1'
    WHEN 2 THEN '02-X2'
    WHEN 3 THEN '03-X3'
    ELSE '' END,
e.FirstName,
e.LastName,
e.ChequeName

Open in new window

0
 
Patrick MatthewsCommented:
Any reason why you're using correlated subqueries rather than a join?

Select h.emp_id, 
CASE e.DepartmentCode WHEN 1 THEN '01-X1'
    WHEN 2 THEN '02-X2'
    WHEN 3 THEN '03-X3'
    ELSE '' END AS [Department],
e.FirstName,
e.LastName,
e.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 h INNER JOIN Employees e ON h.emp_id = e.EmployeesId
where h.DATE Between '2012-05-01 00:00:00.000' and '2013-04-30 23:59:59.000' 
Group by h.emp_id, 
CASE e.DepartmentCode WHEN 1 THEN '01-X1'
    WHEN 2 THEN '02-X2'
    WHEN 3 THEN '03-X3'
    ELSE '' END,
e.FirstName,
e.LastName,
e.ChequeName

Open in new window

0
 
5teveoCommented:
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
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
W.E.BAuthor Commented:
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.
0
 
W.E.BAuthor Commented:
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
0
 
W.E.BAuthor Commented:
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
0
 
W.E.BAuthor Commented:
Beautiful,
thank you very much
0
All Courses

From novice to tech pro — start learning today.