Comparing related column values for employee

Seyed Golshani 21 Reputation points
2022-04-13T16:39:35.847+00:00

Hi all,
How do I need to build a SQL statement to pull only data when all leaveAmount column for an employee is 0. If the total leave amount for the employee is 0, but their at least one leaveamount is not zero, I don't want them.

192767-image.png

When the code is working correctly, only the highlighted data needs to be retuned.

USE [AnyDB]  
GO  
  
/****** Object:  Table [dbo].[Leave]    Script Date: 4/13/2022 11:36:33 AM ******/  
SET ANSI_NULLS ON  
GO  
  
SET QUOTED_IDENTIFIER ON  
GO  
  
CREATE TABLE [dbo].[Leave](  
	[EmployeeID] [numeric](18, 0) NULL,  
	[FirstName] [nchar](10) NULL,  
	[LastName] [nchar](10) NULL,  
	[LeaveType] [nchar](10) NULL,  
	[LeaveDate] [date] NULL,  
	[LeaveAmount] [numeric](18, 0) NULL  
) ON [PRIMARY]  
GO  
  
  
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,055 questions
0 comments No comments
{count} votes

Accepted answer
  1. Nasreen Akter 10,766 Reputation points
    2022-04-13T17:10:04.697+00:00

    Hi @Seyed Golshani ,

    would you please try the following. Thanks!

    ;with CTE1 as(select EmployeeID from [dbo].[Leave]   
    group by EmployeeID  
    having sum(LeaveAmount) = 0)  
      
    select * from [dbo].[Leave] as l1  
    inner join CTE1 as l2 on (l2.EmployeeID = l1.EmployeeID)   
    

2 additional answers

Sort by: Most helpful
  1. Naomi 7,361 Reputation points
    2022-04-13T18:46:08.227+00:00

    select * from Leave E where not exists (select 1 from Leave E1 where E.EmployeeID = E1.EmployeeID and E1.LeaveAmount <> 0);

    There may be an easier variation with ALL keyword, but it's tricky and without a sample I don't know how to write it from the top of my head.

    0 comments No comments

  2. Seyed Golshani 21 Reputation points
    2022-04-13T18:56:38.667+00:00

    Hi Naomi,
    Thank you very much for your reponse! Your solution works as well and give the correct result. Can more than one answer be accepted?

    Seyed