?
Solved

SQL CASE WHEN

Posted on 2013-01-04
7
Medium Priority
?
291 Views
Last Modified: 2013-01-05
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.
0
Comment
Question by:W.E.B
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38745803
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
 
LVL 8

Expert Comment

by:5teveo
ID: 38745805
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
 

Author Comment

by:W.E.B
ID: 38745840
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:W.E.B
ID: 38745841
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
 

Author Comment

by:W.E.B
ID: 38745880
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
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 38746073
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
 

Author Closing Comment

by:W.E.B
ID: 38746646
Beautiful,
thank you very much
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question