Tagging Status of Employees as Active or Inactive based on the last pay date

Question

Hi All,

Hope you are doing well!..I am trying to calculate the total pay by Empcode and the flag the employees as active or inactive (Inactive -if the employee identified by emp code has not received any pay in the last 60 days with respect to the current date) and also identify the last paycheck date...Please find the DDL for the tables below: Can you please help me here...

Input
create table ##input
(empcode int,
comcode int,
paydate date,
pay float,
paymonth date)

insert into ##input values
('1234','34','5/9/2022','342','5/1/2022'),
('1234','45','5/18/2022','250','5/1/2022'),
('564','23','5/7/2022','256','5/1/2022'),
('890','45','5/8/2022','1022.5','5/1/2022'),
('1234','34','7/9/2022','545.5','7/1/2022'),
('1234','45','7/18/2022','1234','7/1/2022'),
('564','23','7/7/2022','678','7/1/2022'),
('890','45','7/8/2022','876','7/1/2022'),
('1234','45','9/1/2022','6754','9/1/2022'),
('564','23','9/7/2022','909','9/1/2022'),
('890','45','9/8/2022','654','9/1/2022'),
('564','23','11/7/2022','909','11/1/2022'),
('890','45','11/8/2022','654','11/1/2022'),
('890','57','11/9/2022','3124','11/1/2022')

Ouput
create table ##output
(empcode int,
totalpay float,
lastpaydate date,
status varchar(40))

insert into ##output values
('564','2752','11/7/2022','Active'),
('890','6330.5','11/9/2022','Active'),
('1234','','9/1/2022','Inactive ')

Thanks,
Arun

Answer 1

;with mycte as (
select * ,min(DATEDIFF(day,paymonth,GETDATE())) over(partition by empcode) dtDiff

from ##input
)

select empcode
, SUM(Case when dtDiff<=60 then pay else 0 end) totalpay
, MAX(paydate) lastpaydate
, MAX(Case when dtDiff<=60 then 'Active ' else 'Inactive' end) status
from mycte
group by empcode,dtdiff

Answer 2

Hi @Hellothere8028
Please check this query:

SELECT empcode  
      ,CASE WHEN DATEDIFF(DAY,MAX(paydate),GETDATE())<=60 THEN SUM(pay) ELSE 0 END AS TotalPay  
	  ,MAX(paydate) AS LastPayDate  
	  ,CASE WHEN DATEDIFF(DAY,MAX(paydate),GETDATE())<=60 THEN 'Active' ELSE 'Inactive'END AS status   
FROM #input  
GROUP BY empcode  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.