Development
SQL WITH Clause
Apr 21, 2015 · SQL
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