No primary key. I found, and modified, some code which uses a cursor similar to what I need. I ran the query, but it kept running for about 4-5 minutes so I figured I must have an endless loop. Here is the code I used:
EXECUTE ('ALTER TABLE dbo.Cost_Import_TEMP
ADD FLAG_MAX_EDATE varchar(50)
')
--Declare variables that hold record fetched from cursor
EXECUTE ('DECLARE @STORE varchar(50), @UPC varchar(50)
--Declare cursor
DECLARE STOREAndUPC_Cursor CURSOR FOR
SELECT DISTINCT STORE, UPC
FROM dbo.Cost_Import_TEMP
--Open cursor
OPEN STOREAndUPC_Cursor
--Place values for STORE and UPC from first record into variables
FETCH NEXT FROM STOREAndUPC_Cursor
INTO @STORE, @UPC
--Begin loop which iterates through all records in cursor
WHILE @@FETCH_STATUS = 0
BEGIN
--Update flag used to identify duplicate records with the most current effective date
UPDATE Cost_Import_TEMP
SET FLAG_MAX_EDate = 1
WHERE STORE = @STORE
AND UPC = @UPC
AND CONVERT(datetime, EffectiveDate) IN (SELECT MAX(CONVERT(datetime, EffectiveDate))
FROM Cost_Import_TEMP
WHERE STORE = @STORE
AND UPC = @UPC)
--Place values for Zone and UPC from next record into variables
FETCH NEXT FROM STOREAndUPC_Cursor
INTO @STORE, @UPC
END --End Loop
--Close and deallocate cursor so it can be used again
CLOSE STOREAndUPC_Cursor
DEALLOCATE STOREAndUPC_Cursor
--Insert filtered out records with the most current effective date back to import table
INSERT INTO Cost_Import (STORE, UPC, Cost, EffectiveDate)
SELECT DISTINCT STORE, UPC, Cost, EffectiveDate
FROM Cost_Import_TEMP
WHERE IsMaxEffectiveDate = 1
')
Tad
|
