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.