continues missing 2 months in sql server

Question

how to get which empid not get salary continus 2 months(subsequence) .

create table tempc( eid int, paiddate varchar(25),salry money)
insert into tempc values(1,'2021-JAN-12',10),
(1,'2020-FEB-12',20),
(1,'2020-MAR-12',20),
(1,'2020-MAY-12',20),
(1,'2020-JUN-12',20),
(1,'2020-JUL-12',20),
(1,'2020-OCT-12',20),
(2,'2021-JAN-12',20),
(2,'2021-FEB-12',20),
(2,'2021-MAR-12',20),
(3,'2021-JAN-12',20),
(3,'2021-APR-12',20),
(3,'2021-MAY-12',20)
bassed on above data expected outputlike below

eid | paiddate | salary
1 |2020-Aug-12 |0
1 |2020-Sep-12 |0
3 |2021-Feb-12 |0
3 |2021-Mar-12 |0

I tried like below
declare @mindate date = (select min(convert (varchar(10), cast(paiddate as date),121 ))mindate from tempc);
declare @maxdate date = (select max(convert (varchar(10), cast(paiddate as date),121 ))mindate from tempc);

with dt as
(select @mindate as dte
union all
select dateadd(mm,1,dte) from dt where dte < @maxdate)
select eid,convert (varchar(10), cast(paiddate as date),121 )paiddate ,
dte from dt
outer apply (select * from tempc t where month(dt.dte)=month(convert (varchar(10), cast(paiddate as date),121 ))
and year(dt.dte)=year(convert (varchar(10), cast(paiddate as date),121 ))
)t
order by eid

above query not give expected reulst .could you please tell me how to write a query to achive this task in sql server .

Answer 1

Hi @balakrishna k ,

Please also check:

;with cte  
as(select eid,paiddate,  
  row_number() over(partition by eid order by eid) rr,  
  substring(cast(cast(paiddate as date) as varchar),6,2) mm  
  from tempc)  
,cte2 as  
  (select c.*  
   from cte c  
   left join cte c2 on c.rr=c2.rr-1 and c.eid=c2.eid  
   where cast(c2.mm as int)-cast(c.mm as int)=3)  
  
select eid,upper(format(dateadd(MM,1,paiddate),'yyyy"-"MMM"-"dd')) paiddate,0 salry   
from cte2  
union   
select eid,upper(format(dateadd(MM,2,paiddate),'yyyy"-"MMM"-"dd')),0   
from cte2  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Check a query:

; with Q1 as
(
    select *, 
        datediff(month, d, lead(d) over(partition by eid order by d)) df
    from tempc
    cross apply (values (cast(paiddate as date))) D(d)
),
Q2 as
(
    select eid, dateadd(month, 1, paiddate) as paiddate, df
    from Q1
    where df > 2
    union all
    select eid, dateadd(month, 1, paiddate), df - 1
    from Q2
    where df > 2
)
select eid, upper(format(paiddate, 'yyyy"-"MMM"-"dd')) as paiddate, 0 as salary
from Q2
order by eid, paiddate

Note that the first year in your sample data probably should be 2020 instead of 2021.