This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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.
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
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)
Hi Nasreen,
Thank you very much for your assistance! With a little tweek to your code, i got it working.
;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)
WHERE l1.LeaveAmount = 0
I am marking your post as 'Accept Answer'.
Thanks again!
Seyed
@Seyed Golshani : ohh, my bad. I am going to modify the answer. Thanks!
@Seyed Golshani : updated the original post. you do not need WHERE. Thank you :)
Hi Nasreen,
If the 'WHERE' clause is removed with get Meg Ryan on the report that that is not correct as we want to report only employees that had all '0's in their leave amount column. With Meg, the sum is 0 row, but that is because there is a 4 and -4.
@Seyed Golshani , that make sense than :)
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.
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
I think the original solution may be incorrect (I don't have the data to test, so just thinking the logic). There still may be rows for the same employee with 0 balance and positive/negative balance and the select will return that employee rows where LeaveAmount = 0 but the employee has other rows with the Actual Amounts. My understanding is that you wanted to filter these employees out.