SQL - duplicate values

List of employees for example. Quick way to find duplicate values using LAG windowing function

;with c as (
SELECT [FirstName]
      ,[LastName],
   [FirstName]  + ' ' +   [LastName] x
      ,[StaffNumber]
 FROM Employee
  ),
  cc as(
  SELECT FirstName, LastName, StaffNumber, x , lag (x) over (partition by LastName order by x) dup
  from c)
  select FirstName, LastName, StaffNumber, x ,  dup From cc
  where x=dup
  order by LastName

Count Distinct Over Partition

To get a count of distinct values over a windowing partition:

DENSE_RANK() OVER (PARTITION BY e.Dept_Desc order by e.Staff_No)
+ DENSE_RANK() OVER (PARTITION BY e.Dept_Desc order by e.Staff_No desc)
- 1