SQL How to pull in all records that don't contain

1

Hello,

This is a bit of a trick question to explain, but I'll try my best.

The essence of the question is that I have a employee salary table and the columns are like so,: Employee ID, Month of Salary, Salary (Currency).

I want to run a select that will show me all of the employees that don't have a record for X month.

I have attached an image to assist in the visualising of this, and here is an example of what UI would want from this data:

Lets say from this small example that I want to see all of the employees that weren't paid on the 1st October 2021. From looking I know that employee 3 was the only one paid and 1 and 2 were not paid. How would I be able to query this on a much larger range of data without knowing which month it could be that they weren't paid?
Capture

2

It would help if you provided sample data in the form of create/insert statements. I think you want to use NOT EXISTS:

Select *
  From dbo.YourTable  yt
 Where Not Exists (Select *
                     From dbo.YourTable  yt2
                    Where yt2.EmployeeID = yt.EmployeeID
                      And yt2.Month = '2021-10-01');

This will get you all rows for that employee - so you may want to filter the list more. If the goal is to see who was paid last month, but not this month:

Select *
  From dbo.YourTable  yt
 Where Not Exists (Select *
                     From dbo.YourTable  yt2
                    Where yt2.EmployeeID = yt.EmployeeID
                      And yt2.Month = '2021-10-01')
   And yt.Month = '2021-09-01';
1 Like
3

That's what I was looking for, thank you!

4

Welcome - happy to help.