Here are some inline comments to the significant parts of the query:
-- define common table expression (CTE) using the WITH keyword,
-- which is pretty much like derived table but more powerful
WITH Dups
AS
(SELECT week_start_date, pan,
-- use aggregate function with the OVER clause
-- this allows to apply count over a partition/group
-- in this case will provide counts for each 'pan' value
-- note the PARTITION BY section that lists which column
-- will define the partition
COUNT(*) OVER(PARTITION BY pan) AS cnt
FROM Diaries)
DELETE Dups
-- delete only those rows that have count 1
-- since count was per 'pan' value it will remove
-- those that have any duplicate rows
WHERE cnt 1;
Note that the DELETE statement has to follow the CTE definition:
WITH
AS
(SELECT ...)
DELETE ...
The query that consumes the CTE has to be immediately after the CTE
definition (and it could be SELECT/INSERT/DELETE/UPDATE query).
--
Plamen Ratchev
http://www.SQLStudio.com 
