SQL WITH Clause

Simple WITH clause

WITH AS (subquery)
SELECT FROM [,table]

WITH clause with multiple subqueries

WITH AS (subquery)
, AS (subquery)
SELECT FROM , [, table]
[ WHERE ]

for example find duplicate phone number rows in customer

;WITH dupes AS 
 (
  SELECT DAY_PHONE
  FROM cust
  GROUP BY DAY_PHONE
  HAVING COUNT(*) > 1
)

SELECT c.CODE, c.org_name, c.last_name, c.first_name, c.e_mail, c.street, c.street_city, CONVERT(date, c.last) as LAST, c.DAY_PHONE
  FROM dupes 
  INNER JOIN dbo.CUST AS c
  ON dupes.DAY_PHONE = c.day_phone

  order by dupes.DAY_PHONE

now more complex. the following statement converts the DAY_PHONE field to only numbers and only the first 10 digits and then checks for duplicate records

OnlyNumbers is a stored procedure that returns only the numbers from a string

;WITH custs AS 
(
  SELECT left(dbo.OnlyNumbers(day_phone),10) as PHONE
  FROM dbo.CUST
)
, dupes AS 
 (
  SELECT PHONE
  FROM custs
  GROUP BY PHONE
  HAVING COUNT(*) > 1
)


SELECT c.CODE, c.org_name, c.last_name, c.first_name, c.e_mail, c.street, c.street_city, CONVERT(date, c.last) as LAST, c.DAY_PHONE, dupes.PHONE
  FROM dupes 
  INNER JOIN dbo.CUST AS c
  ON dupes.PHONE = left(dbo.OnlyNumbers(c.day_phone),10)
  where c.cust_grp_num < 2
  and c.shiponly = ''

  order by dupes.phone
  

next same as above but check multiple fields for duplicates

-- first preselect customers
-- then get duplicate rows only
-- then show all rows with these dupe values
;WITH custs AS 
(
  SELECT left(dbo.OnlyNumbers(day_phone),10) as PHONE, LAST_NAME, ORG_NAME
  FROM dbo.CUST
  where cust.cust_grp_num < 2 and cust.shiponly = ''
)
, dupes AS 
 (
  SELECT PHONE, LAST_NAME , ORG_NAME
  FROM custs
  GROUP BY PHONE, LAST_NAME, ORG_NAME
  HAVING COUNT(*) > 1
)


SELECT c.CODE, c.org_name, c.last_name, c.first_name, c.e_mail, c.street, c.street_city, CONVERT(date, c.last) as LAST, c.DAY_PHONE, dupes.PHONE
  FROM dupes 
  INNER JOIN dbo.CUST AS c
  ON dupes.PHONE = left(dbo.OnlyNumbers(c.day_phone),10)
  and dupes.LAST_NAME = c.last_name
  and dupes.ORG_NAME = c.ORG_NAME

  order by dupes.phone