我需要更新表中的记录,其日期字段的值小于表中共享另一个字段的相同值的所有记录的最大日期值减go 天数.
从下面的列表中,如果记录的Header_BusDate小于表中具有相同Header_SiteKey字段值的所有记录的最大日期值(减go 天数),则将Header_Ready字段设置为1.
我需要在不使用游标的情况下完成这项工作(我已经有游标了,但我的雇主对游标过敏).
CREATE TABLE #Headers
(
Header_Key decimal(15,0) NOT NULL,
Header_SiteKey decimal(15,0) NOT NULL,
Header_BusDate smalldatetime NOT NULL,
Header_Ready bit NOT NULL default(0)
)
INSERT INTO #Headers
VALUES
(610, 4, '2023-04-16', 0),
(609, 4, '2023-04-15', 0),
(608, 4, '2023-04-14', 0),
(607, 4, '2023-04-13', 0),
(606, 4, '2023-04-12', 0),
(605, 4, '2023-04-11', 0),
(604, 4, '2023-04-10', 0),
(617, 6, '2032-12-31', 0),
(616, 6, '2023-04-15', 0),
(615, 6, '2023-04-14', 0),
(614, 6, '2023-04-13', 0),
(613, 6, '2023-04-12', 0),
(612, 6, '2023-04-11', 0),
(611, 6, '2023-04-10', 0)
/* This doesn't work */
UPDATE #Headers
SET Header_Ready = 1
WHERE Header_BusDate <
(SELECT MAX(Header_BusDate) - 2 day
FROM #Headers hdrs
WHERE hdrs.Header_SiteKey = Header_SiteKey)
SELECT * FROM #Headers
DROP TABLE #Headers
以下是我想要的结果(从我的游标解决方案输出):
Header_Key Header_SiteKey Header_BusDate Header_Ready
610 4 2023-04-16 00:00:00 0
609 4 2023-04-15 00:00:00 0
608 4 2023-04-14 00:00:00 0
607 4 2023-04-13 00:00:00 1
606 4 2023-04-12 00:00:00 1
605 4 2023-04-11 00:00:00 1
604 4 2023-04-10 00:00:00 1
617 6 2032-12-31 00:00:00 0
616 6 2023-04-15 00:00:00 1
615 6 2023-04-14 00:00:00 1
614 6 2023-04-13 00:00:00 1
613 6 2023-04-12 00:00:00 1
612 6 2023-04-11 00:00:00 1
611 6 2023-04-10 00:00:00 1
提前感谢您的帮助!