Solved

SQL CASE WHEN

Posted on 2013-01-04
7
290 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 92

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 92

Accepted Solution

by:
Patrick Matthews earned 250 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

691 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