user overview after 3days of exit date

Question

Hi Experts, 
 Could you please help me on the query to generate a report of user's overview after 3 days of last day. 
 Thanks, 
 Vijay

kinshasa · Accepted Answer

I think the SQL query should be simple enough for this, then you can use it to form your report. Something like: 
 
select * from Person p 
join UNSAccountB ub on ub.UID_Person = p.UID_Person 
join UNSAccountBInUNSGroupB uag on uag.UID_UNSAccountB = ub.UID_UNSAccountB 
join UNSGroupB ub on ub.UID_UNSGroupB = uag.UID_UNSGroupB 
where p.IsInActive=1

sayed.ashhad · Answer

Hi, I think to use the end dateOR IsInActive, and have flags indicating if there's a risk, the code might be something like this (I haven't checked the code exactly but have formatted it to make it more readable): select p.*, case when ( p.IsInActive=1 OR ( YEAR(p.ExitDate) > 1901 AND p.ExitDate < DATEADD ( DAY , –3 , GETDATE ()) ) ) AND ub.AccountDisabled=0 THEN 1 else 0 END as AccountActiveRisk from Person p join UNSAccountB ub on ub.UID_Person = p.UID_Person join UNSAccountBInUNSGroupB uag on uag.UID_UNSAccountB = ub.UID_UNSAccountB join UNSGroupB ub on ub.UID_UNSGroupB = uag.UID_UNSGroupB where p.IsInActive=1 OR ( YEAR(p.ExitDate) > 1901 AND p.ExitDate < GETDATE()) This will display an additional field which, if set to 1, indicates the account is live despite the person's end date being 3 days in the past (if set) OR the person being disabled. The WHERE clause covers the possibility of the exit date being populated without the person being deactivated.