MS Server Insert IF and GeoLocation

I have 3 tables in MS Server. 1: contains all american zips and geoLocation for those zips. 2: contains stores and geoLocation data. 3: tells me the distance in miles between zip codes and the closest store. If the zip is within 50 miles of a business location. The script for inserting the data into table 3 is:

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

This works perfectly. However, now I must create an update script, that we will run once every 3 months or so. This is for new stores, stores closing, or moving. What it must do is INSERT (as a new row) the new data if the data has changed, and add an expiredDate to the old entry (this column already exist in table 3). I have looked all over the web, but can't find anything. I am still new-ish to Tsql, and may have missed the solution. Any leads/help is greatly appreciated. Thank you.

Added upon request:

 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       
    )

I may have found a solution for the insert after rerunning the above cursor into a temp Table:

 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

Would this be correct? Still having issues figuring out how to update the expiredDate column for the old entry.

Solution

I think you need something similar to the following. Please review and adjust to your needs. These statements assume that you have the new, updated records already in your temporary table.

-- 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

On the 2nd statement, we use function ROW_NUMBER() which generates an incremental ranking from to 1 onwards (with no ties), which resets back to 1 when values from the PARTITION BY columns change, and is ordered by the columns in the ORDER BY. So this particular row number will give the value 1 to the stores that have the minimum distance for each different zip code, 2 to the 2nd closest and so on. I believe you just need an active record for the closest one, so we filter by this ranking N.DistanceRankingByZipCode = 1 before inserting.