Password Expiration date

I need a SQL code to set password expiration date to current date if it is created 120days from todays date and not used.if the password is less than 120 days from todays date then set it expiration date plus 120 date.

create table #savi(samaccountname  varchar(50), passwordexpirationdate date,
                   isUsed bit )

insert into #savi
select 'dvader', dateadd(dd, -120, getdate()), 0 union
select 'lskywalker', dateadd(dd, -119, getdate()), 1 union
select 'savitha', getdate() -12, 1

select *, DATEDIFF(dd, tgt.passwordexpirationdate, getdate()), 
dateadd(dd, 120, getdate()) 
from #savi tgt

update tgt
   set tgt.passwordexpirationdate = 
		 when DATEDIFF(dd, tgt.passwordexpirationdate, getdate())  = 120 and isUsed = 0
			then getdate()
		 else dateadd(dd, 120, getdate())
 From #savi tgt 

 select * from #savi

drop table #savi
1 Like