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

Hellothere8028 801 Reputation points

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...

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

insert into ##input values

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

insert into ##output values
('1234','','9/1/2022','Inactive ')


0 comments No comments
{count} votes

Accepted answer
  1. Jingyang Li 5,311 Reputation points

    ;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

1 additional answer

Sort by: Most helpful
  1. CosmogHong-MSFT 11,796 Reputation points Microsoft Vendor

    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,

    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.

    0 comments No comments