CREATE  procedure dbo.rpt_pUStatistics    
@ProvisionType int =Null,          
@ReferalCode nvarchar(4) =NULL     
with recompile       
as          
begin          
declare @RoundTrip int            
if @referalcode='0' set @referalcode=NULL            
if @ProvisionType=0 set @provisionType=NULL          
          
    
if (@referalcode is null)  and (@provisionTYpe is null)          
  begin            
          
    select @roundTrip=count(distinct s.mailboxid)        
    from havsms..vw_vsms_sender s  inner join havsms..vw_vsms_receiver r 
    on s.mailboxid = r.mailboxid        
    join userstoprovision up      
    on s.mailboxid=up.mailboxid      
    
    -----------+++++++             
     select count(userid)as'TotalUsers',count(PIN) as 'RegisteredUSers',isnull(@roundtrip,0) as 'RoundTrip'             
     from users u  inner join userstoprovision up             
     on u.mailboxid=up.mailboxid             
          
        
               
  end            
else      
  if (@provisionType is not null) and (@referalcode is not null)        
   begin            
     select @roundTrip=count(distinct s.mailboxid)        
     from havsms..vw_vsms_sender s inner join havsms..vw_vsms_receiver r
     on s.mailboxid = r.mailboxid        
     join userstoprovision up      
     on s.mailboxid=up.mailboxid      
     where   up.ProvisionedBy=@referalcode       
     and       
     up.provisionType=@provisionType        
             
     ----------++++++++++        
                  
      select count(userid)as'TotalUsers',count(PIN) as 'RegisteredUSers',isnull(@roundtrip,0) as 'RoundTrip'             
      from users u with (nolock ) inner join userstoprovision up             
      on u.mailboxid=up.mailboxid and             
      up.provisionedby=@referalCode and         
       provisionTYpe=@provisionType        
   end            
   else if (@provisionType is not null)        
   begin        
    
     select @RoundTrip=count(distinct s.mailboxid) from HAVSMS..vw_vsms_sender s inner join havsms..vw_vsms_receiver r 
      on r.mailboxid=s.Mailboxid           
     join userstoprovision up      
     on s.mailboxid=up.mailboxid      
      where up.provisionTYpe=@provisionType            

                  
                  
      select count(userid)as'TotalUsers',count(PIN) as 'RegisteredUSers',isnull(@roundtrip,0) as 'RoundTrip'             
      from users u  inner join userstoprovision up             
      on u.mailboxid=up.mailboxid and             
       provisionTYpe=@provisionType        
    
        
end        
    
    
end    
    
    
    
  

    select @roundTrip=count(s.mailboxid)        
    from havsms..vw_vsms_sender s
    WHERE      EXISTS (SELECT * FROM havsms..vw_vsms_receiver r 
                          WHERE s.mailboxid = r.mailboxid)
           AND EXISTS (SELECT * FROM userstoprovision up 
                          WHERE s.mailboxid = up.mailboxid)