这将获得所有记录，其中客户x存在星期五+下星期一的记录，但不包括星期六的记录。
SELECT a1.*
FROM Archive a1
INNER JOIN Archive a2 ON (
(a1.Nbr = a2.Nbr)
AND (a1.ExtendedNbr = a2.ExtendedNbr)
AND (a1.ValDate + 3 = a2.ValDate)
AND (DatePart("w", a1.ValDate) = 6)
)
WHERE NOT EXISTS
(SELECT * FROM Archive a3
WHERE a3.Nbr = a1.Nbr
AND a3.ValDate = a1.ValDate + 1)
您可以使用它来插入星期六：
INSERT INTO Archive ([Customer Name], Nbr, City, [Value of Day], ExtendedNbr, ValDate)
SELECT a1.[Customer Name], a1.Nbr, a1.City, a1.[Value of Day], a1.ExtendedNbr, a1.ValDate + 1
FROM Archive a1
INNER JOIN Archive a2 ON (
(a1.Nbr = a2.Nbr)
AND (a1.ExtendedNbr = a2.ExtendedNbr)
AND (a1.ValDate + 3 = a2.ValDate)
AND (DatePart("w", a1.ValDate) = 6)
)
WHERE NOT EXISTS
(SELECT * FROM Archive a3
WHERE a3.Nbr = a1.Nbr
AND a3.ValDate = a1.ValDate + 1)
要插入周日，使用相同的，但取代+ 1通过+ 2在这两个地方。
要插入随机的单个失踪日（银行假期），请更改a1.ValDate + 3为a1.ValDate + 2，然后删除AND (DatePart("w", a1.ValDate) = 6)
如果DatePart()在JOIN内遇到麻烦，则为备用版本：
INSERT INTO Archive ([Customer Name], Nbr, City, [Value of Day], ExtendedNbr, ValDate)
SELECT a1.[Customer Name], a1.Nbr, a1.City, a1.[Value of Day], a1.ExtendedNbr, a1.ValDate + 1
FROM Archive a1
INNER JOIN Archive a2 ON (
(a1.Nbr = a2.Nbr)
AND (a1.ExtendedNbr = a2.ExtendedNbr)
AND (a1.ValDate + 3 = a2.ValDate)
)
WHERE NOT EXISTS
(SELECT * FROM Archive a3
WHERE a3.Nbr = a1.Nbr
AND a3.ValDate = a1.ValDate + 1)
AND (DatePart("w", a1.ValDate) = 6)
602392714
清零编程群