;with mycte as (
select * ,min(DATEDIFF(day,paymonth,GETDATE())) over(partition by empcode) dtDiff
, 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
group by empcode,dtdiff
Hi @Jingyang Li ...Appreciate your response !...Just checking if the statement min(DATEDIFF(day,paymonth,GETDATE())) over(partition by empcode) dtDiff would capture the latest paydate for every empcode
Sign in to comment
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
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.