MS插入IF和GeoLocation

Question

我在中有3张桌子。1:包含所有美国拉链和geoLocation为这些拉链。2:包含存储和geoLocation数据。告诉我邮政编码和最近的商店之间的距离是多少英里。如果拉链距离商业地点不到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个月左右运行一次。这是为新的商店，商店关闭，或移动。如果数据已更改，则必须将新数据插入(作为新行)，并将expiredDate添加到旧条目(该列已存在于表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

这是对的吗？对于如何更新旧条目的expiredDate列，仍然存在问题。

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到1的递增排序(没有领带)，当来自PARTITION BY列的值发生变化时，它将重置为1，并由ORDER BY中的列排序。因此，这个特定的行号将给出每个不同邮政编码的最小距离的商店的值1，从2到第二个最近的商店等等。我相信你只需要一个最接近的活动记录，所以我们过滤这个排名的N.DistanceRankingByZipCode = 1，然后插入。