Tsql CTE Example Code

declare @sd date=convert(varchar(10),getdate(),120),@ed date =convert(varchar(10),getdate(),120)
;WITH CTE
as
(
 select users.user_name,users.u_fbid
 ,count([messages].MessageID) SENT from  [messages] with (nolock)  
 left join users on messages.SenderFbID=users.u_fbid  
 where [messages].IsDelevered=0  
  and ([messages].messagedate
  between  
  (case @StartDate when null then @sd else @Startdate end)  
  AND  
  (Case @EndDate when null then @sd else @EndDate end)
  or Convert(varchar(10),messagedate,120)= @sd
  )      

   
group by users.user_name,users.u_fbid  
),
 
CTE2
as
(
 select users.user_name,users.u_fbid
 ,count([messages].MessageID) received from  [messages] with (nolock)  
 left join users on messages.SenderFbID=users.u_fbid  
 where [messages].IsDelevered=1 and
  (Convert(varchar(10),[messages].messagedate,120)  
  between  
  (case @StartDate when null then @sd else @Startdate end)  
  AND  
  (Case @EndDate when null then @sd else @EndDate end)
  or Convert(varchar(10),messagedate,120)= @sd)      

group by users.user_name,users.u_fbid
    )
     
select top 5
isnull(cte.user_name,cte2.user_name) User_name
,isnull([Sent],0) SentCount
,isnull(received,0) receivedcount
from cte FULL JOIN cte2 on cte.u_fbid=cte2.u_fbid

order by SentCount desc




 
END