MS 服务器插入 IF 和地理位置

Question

我在MS服务器中有3个表。 1：包含所有美国拉链和这些拉链的地理位置。 2：包含商店和地理位置数据。 3：告诉我邮政编码和最近的商店之间的距离(以英里为单位(。如果邮政编码在营业地点 50 英里范围内。将数据插入表 3 的脚本为：

declare @zip varchar (10);
declare @RangeInMiles int
set @RangeInMiles = 50
declare zip_cursor CURSOR FOR
Select ZipCode from dimZip;
OPEN zip_cursor;
FETCH NEXT FROM zip_cursor
INTO @zip;
WHILE @@FETCH_STATUS = 0
BEGIN
declare @geo geography
set @geo = (select z.GeographyLocation
from dimZip z
where z.ZipCode = @zip)

--Select the nearest Postal Codes
INSERT INTO dimZipToStore
(ZipCode, ZipStateID, StoreID, StoreState, DistanceInMiles)
SELECT top 1 dz.ZipCode, dz.StateID, ld.StoreID, l.StoreState, 
ld.GeographyLocation.STDistance(@geo)/1609.34 as DistanceInMiles 
--1609.344 meteres in mile
FROM dimZip dz
cross join dimLocationDetail ld
join dimlocation l on l.StoreID = ld.StoreID  
WHERE ld.GeographyLocation is not null
and dz.ZipCode = @zip 
and ld.GeographyLocation.STDistance(@geo)<=(@RangeInMiles * 1609.344)
order by DistanceInMiles
FETCH NEXT FROM zip_cursor  
INTO @zip;  
END  
CLOSE zip_cursor;
DEALLOCATE zip_Cursor;
GO

这非常有效。 但是，现在我必须创建一个更新脚本，我们将每 3 个月左右运行一次。这适用于新店、关闭或搬迁的商店。 它必须做的是在数据发生更改时插入(作为新行(新数据，并将过期日期添加到旧条目中(表 3 中已存在此列(。 我在网上找遍了，但什么也找不到。我对 Tsql 仍然很陌生，可能错过了解决方案。任何线索/帮助将不胜感激。 谢谢。

根据要求添加：

CREATE TABLE dimZipToStore(
ZipCode varchar (10),
ZipStateID varchar (2),
StoreID varchar (5),
StoreState varchar (3),
DistanceInMiles FLOAT,
CreateDate DateTime not null default (GetDate()),
ExipiredDate Datetime       
)

在将上述光标重新运行到临时表中后，我可能已经找到了插入的解决方案：

INSERT INTO dimZipToStore
(ZipCode, ZipStateID, StoreID, StoreState, DistanceInMiles)
select * from(
select ZipCode, ZipStateID, StoreID, StoreState, DistanceInMiles
from #tempDimZipToStore TZ
EXCEPT
select ZipCode, ZipStateID,    StoreID, StoreState, DistanceInMiles 
from dimZipToStore Z)
where TZ.DistanceInMiles < Z.DistanceInMiles

这是正确的吗？在弄清楚如何更新旧条目的过期日期列时仍然遇到问题。

Answer 1

我认为您需要类似于以下内容的内容。请查看并根据您的需求进行调整。这些语句假定临时表中已有新的更新记录。

-- Insert new stores if there isn't one currently by zip code and distance is no more than 50 miles
INSERT INTO dimZipToStore (
ZipCode,
ZipStateID,
StoreID,
StoreState,
DistanceInMiles)
SELECT
ZipCode = N.ZipCode,
ZipStateID = N.ZipStateID,
StoreID = N.StoreID,
StoreState = N.StoreState,
DistanceInMiles = N.DistanceInMiles
FROM
#tempDimZipToStore AS N
WHERE
N.DistanceInMiles <= 50 AND
NOT EXISTS (
SELECT
'there is currently no store for this zip code'
FROM
dimZipToStore AS O
WHERE
N.ZipCode = O.ZipCode)

-- Insert the new, closer store (just the closest one)
;WITH DistanceRankingsByZipCode AS
(
SELECT
N.ZipCode,
N.ZipStateID,
N.StoreID,
N.StoreState,
N.DistanceInMiles,
DistanceRankingByZipCode = ROW_NUMBER() OVER (PARTITION BY N.ZipCode ORDER BY N.DistanceInMiles ASC)
FROM
#tempDimZipToStore AS N
)
INSERT INTO dimZipToStore (
ZipCode,
ZipStateID,
StoreID,
StoreState,
DistanceInMiles)
SELECT
ZipCode = N.ZipCode,
ZipStateID = N.ZipStateID,
StoreID = N.StoreID,
StoreState = N.StoreState,
DistanceInMiles = N.DistanceInMiles
FROM
DistanceRankingsByZipCode AS N
WHERE
N.DistanceRankingByZipCode = 1 AND
EXISTS (
SELECT
'there is currently a farther active store for the same zip code'
FROM
dimZipToStore AS O
WHERE
N.ZipCode = O.ZipCode AND
N.DistanceInMiles < O.DistanceInMiles AND
O.ExpiredDate IS NULL)

-- Update old record if a closer store exists (it's now on the same table)
;WITH MinDistanceByZipCode AS
(
SELECT
D.ZipCode,
MinDistanceInMiles = MIN(D.DistanceInMiles)
FROM
dimZipToStore AS D
GROUP BY
D.ZipCode
)
UPDATE O SET
ExipiredDate = GETDATE()
FROM
dimZipToStore AS O
INNER JOIN MinDistanceByZipCode AS C ON O.ZipCode = C.ZipCode
WHERE
O.ExpiredDate IS NULL AND
O.DistanceInMiles > C.MinDistanceInMiles

在第二个语句中，我们使用函数ROW_NUMBER()生成从 1 开始的增量排名(没有平局(，当PARTITION BY列的值发生变化时，它会重置回 1，并按ORDER BY中的列排序。因此，此特定行号将为每个不同邮政编码具有最小距离的商店提供值 1，将值 2 提供给第 2 个最接近的商店，依此类推。我相信您只需要最接近的记录的活动记录，因此我们在插入之前按此排名N.DistanceRankingByZipCode = 1进行过滤。