[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL CASE WHEN

Posted on 2013-01-04
7
Medium Priority
?
293 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
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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.
Integration Management Part 2
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

865 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