Replace SQL Server 2005 cursor

Here is the simplified cursor:

SET IMPLICIT_TRANSACTIONS ON

SET @curTemp = CURSOR FAST_FORWARD
FOR
    SELECT gpno, ssn FROM EligCov Group BY gpno, ssn
OPEN @curTemp

-- loop through cursor and build output report table
FETCH NEXT FROM @curTemp INTO @gpno, @ssn
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @trnCnt = @trnCnt + 1

    -- get the max CovEndDate for 开发者_开发百科this group/ssn combo
    SELECT @MaxCovEndDate=MAX(CovEndDate) FROM Payroll WHERE GroupNo=@gpno AND SSN=@ssn
    UPDATE EligCov SET CovEndDate = @MaxCovEndDate WHERE gpno=@gpno AND ssn=@ssn

    -- check transaction counts and commit if needed
    IF @trnCnt % 2000 = 0
    BEGIN
        IF @@TRANCOUNT > 0
        BEGIN
            COMMIT
        END
    END
    FETCH NEXT FROM @curTemp INTO @gpno, @ssn
END
CLOSE @curTemp
DEALLOCATE @curTemp 

SET IMPLICIT_TRANSACTIONS OFF

Basically table EligCov has one distinct groupNo/SSN combo. Table Payroll will have many groupno/ssn combinations. Each row in the Payroll table has a CovEndDate column containing a date.

I need to just select the max(CovEndDate) for a certain gpno/ssn combination (this could be from one row or hundreds) from Payroll and update the CovEndDate in the EligCov table (always one row for a specific groupno/ssn combo).

The payroll table has 10,000,000+ rows and the EligCov table has ~200,000 rows. Is there a way to change this to not use a cursor?

To restate, for each group/ssn in EligCov look through all the records in Payroll with the same group/ssn and grab the max(CovEndDate) and update the CovEndDate in the EligCov table.

Thanks.

Sure - pretty easy - use a CTE (Common Table Expression) that partitions your data by the "grouping" set.

;WITH DataToUpdate AS
(
    SELECT  
        GroupNo, SSN, CovEndDate AS 'MaxCovEndDate',
        ROW_NUMBER() OVER(PARTITION BY GroupNo,SSN ORDER BY CovEndDate DESC) AS 'RowNum'
    FROM dbo.Payroll 
UPDATE 
)
UPDATE dbo.EligCov 
SET CovEndDate = d.MaxCovEndDate 
FROM DataToUpdate d
WHERE gpno = d.GroupNo 
  AND ssn = d.SSN 
  AND d.RowNum = 1   -- select the latest date - the one with RowNum = 1

The CTE partitions your data by GroupNo,SSN - so each group of GroupNo,SSN is handed out new ROW_NUMBER(), starting at 1. Since the rows are sorted by CovEndDate DESC, the latest/most recent CovEndDate gets RowNum = 1.

Based on that CTE, you then update the EligCov table, only taking those most recent entries, for each group of GroupNo,SSN

One possible method:

UPDATE
    EC
SET
    CovEndDate = SQ.CovEndDate
FROM
    EligCov EC
INNER JOIN (
        SELECT gpno, ssn, MAX(CovEndDate) AS max_dt
        FROM Payroll
        GROUP BY gpno, ssn) SQ ON
    SQ.gpno = EC.gpno AND
    SQ.ssn = EC.ssn

More importantly though, I'd ask if it's really necessary to have that column in EligCov, which is against rules of normalization for a relational database.