Who has joined and who has left

Transact-SQL
1

Hello

Sql Server version = 2008

declare @yearmonth varchar(6)  set @yearmonth = '202504'

declare @dt1 datetime
declare @dt2 datetime

select @dt1 = dt1 , @dt2 = dt2  from salary_dates d where yearmonth = @yearmonth
--@dt1 = '2025-03-26 and @dt2='2025-04-25

SELECT  o.emp_code, e.card_code
FROM populate_overtime o
join employee e on o.emp_code = e.card_code
where (o.dt between @dt1 and @dt2)

The above sql shows 22785 rows i(n table populate_overtime)
Out of 22785 rows (o.emp_code) 7 left (e.left_date)
160355 160273 160421 160422 160431 009838 160268
and 6 joined (e.appoint_date)
160436 160437 160438 160439 160440 160441

I want the result to be left and joined employees.

Result ( 7 +6=13 rows)
card_code = 
160355
160273
160421
160422
160431
009838
160268 
160436
160437
160438
160439
160440
160441
2

hi

hope this helps

create tables insert sample data script 
-- Create tables
CREATE TABLE employee (
    card_code VARCHAR(10) PRIMARY KEY,
    left_date DATE,
    appoint_date DATE
);

CREATE TABLE populate_overtime (
    emp_code VARCHAR(10),
    dt DATE
);

CREATE TABLE salary_dates (
    yearmonth VARCHAR(6),
    dt1 DATE,
    dt2 DATE
);

-- Insert sample data into salary_dates
INSERT INTO salary_dates (yearmonth, dt1, dt2) VALUES
('202504', '2025-03-26', '2025-04-25');

-- Insert sample data into employee
INSERT INTO employee (card_code, left_date, appoint_date) VALUES
('160355', '2024-03-01', NULL),  -- Left employee
('160273', '2024-01-15', NULL),  -- Left employee
('160421', '2024-02-25', NULL),  -- Left employee
('160422', '2024-01-30', NULL),  -- Left employee
('160431', '2024-02-10', NULL),  -- Left employee
('009838', '2024-03-20', NULL),  -- Left employee
('160268', '2023-12-31', NULL),  -- Left employee
('160436', NULL, '2024-01-15'),  -- Joined employee
('160437', NULL, '2024-02-01'),  -- Joined employee
('160438', NULL, '2024-02-20'),  -- Joined employee
('160439', NULL, '2024-03-01'),  -- Joined employee
('160440', NULL, '2024-03-15'),  -- Joined employee
('160441', NULL, '2024-03-20');  -- Joined employee

-- Insert sample data into populate_overtime
INSERT INTO populate_overtime (emp_code, dt) VALUES
('160355', '2025-04-01'),
('160273', '2025-04-02'),
('160421', '2025-04-03'),
('160422', '2025-04-04'),
('160431', '2025-04-05'),
('009838', '2025-04-06'),
('160268', '2025-04-07'),
('160436', '2025-04-08'),
('160437', '2025-04-10'),
('160438', '2025-04-12'),
('160439', '2025-04-20'),
('160440', '2025-04-21'),
('160441', '2025-04-22');
3

hi

hope this helps

simplistic design makes it easy :stuck_out_tongue_winking_eye:

sort of data ware house design

you can add the overtime column to the table
you can add the date filters

design script 
drop table if exists dbo.employee 

create table dbo.employee       ( empID int, empNAME varchar(100), joinDATE date , leftDATE date , stillEmployee int )

insert into dbo.employee values (1         ,'Jane Doe'            ,'2024-05-19'     ,'2025-05-18' , 0 ) 
insert into dbo.employee values (2         ,'Bob Brown '          ,'2024-05-13'     ,null         , 1 )
insert into dbo.employee values (3         ,'Prakash Singh'       ,'2024-05-05'     ,null         , 1 ) 

select 'Employee Data',* from dbo.employee

select 'before Update Still Employees',* from dbo.employee where stillEmployee = 1
select 'before Update Left Employees' ,* from dbo.employee where stillEmployee = 0

update dbo.employee set leftDATE =getdate() , stillEmployee = 0 where empID = 2

select 'After Update Still Employees',* from dbo.employee where stillEmployee = 1
select 'After Update Left  Employees' ,* from dbo.employee where stillEmployee = 0

update dbo.employee set leftDATE =getdate() , stillEmployee = 0 where empID = 2

select * from dbo.employee where stillEmployee = 1
select * from dbo.employee where stillEmployee = 0

image
image600×404 13.9 KB