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

Hellothere8028 821 Reputation points
2022-11-21T12:53:32.473+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,554 questions
0 comments
Accepted answer
  1. Jingyang Li 5,891 Reputation points
    2022-11-21T15:44:21.503+00:00

    ;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
Most helpful Newest Oldest
  1. CosmogHong-MSFT 23,321 Reputation points Microsoft Vendor
    2022-11-22T02:43:24.733+00:00

    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.

    0 comments