Solved

SQL CASE WHEN

Posted on 2013-01-04
7
284 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:Wass_QA
  • 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:Wass_QA
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Wass_QA
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:Wass_QA
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:Wass_QA
ID: 38746646
Beautiful,
thank you very much
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now