Sql-server – Replace all rows in a table based on date

You simply want:

SELECT
    [Customer ID],
    MAX([Some Date]) AS[Latest Date]
FROM[Cust Date TABLE]
GROUP BY
    [Customer ID];

Ok - you've revised it. You now want to order the rows and pick the top one:

WITH numbered AS (
    SELECT
        [Customer ID],
        [Some Date],
        [Balance],
        ROW_NUMBER() OVER (
            PARTITION BY
                [Customer ID]
            ORDER BY
                [Some Date] DESC
        ) AS rownum
    FROM[Cust Date TABLE]
)
SELECT
    [Customer ID],
    [Some Date],
    [Balance]
FROM numbered
WHERE
    rownum = 1;

I would timestamp your staging loads and hash your unique attributes in a persisted computed column so as to allow indexing tuning. It's a time / space trade-off, but space is much, much cheaper than time these days. The ratio will be in your favor as well, executing dozens of times faster in the long run at the expense of about three times the storage space for the staging table. In any incremental load scenario, you'd also be in a position to happily truncate your staging tables after every warehouse load, so from my point of view, it's win / win.

The staging table ( from your previous question, with some tweaks ) would be set up like this:

CREATE TABLE dbo.StagingTable   
( 
    [DateInserted]  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    [HashBytes]     AS HASHBYTES( 'SHA1', 
                        CONVERT( VARBINARY( 4 ), COALESCE( [A], 0 ) ) 
                        + CONVERT( VARBINARY( 4 ), COALESCE( [B], 0 ) ) 
                        + CONVERT( VARBINARY( 32 ), COALESCE( [C], '' ) )
                        ) PERSISTED,
    [KEY]           INTEGER NOT NULL,
    [A]             INTEGER,
    [B]             INTEGER,
    [C]             VARCHAR( 32 )
);

ALTER TABLE dbo.StagingTable
ADD CONSTRAINT PK__StagingTable
    PRIMARY KEY CLUSTERED ( [KEY] );

CREATE NONCLUSTERED INDEX IX__StagingTable__DateInserted__HashBytes
    ON  dbo.StagingTable ( [DateInserted], [HashBytes] )
INCLUDE ( [A], [B], [C] );
GO

The "work" here is figuring out a decent expression for running through your hashing algorithm ( SHA1 is chosen here because it's probably good enough, but if you honestly believe you're making your way into exobyte territory in the near future, feel free to use SHA2 instead ). Simply converting all of the necessary attributes into a VARCHAR column is problematic, since ( 13, 1, 'A' ) and ( 1, 13, 'A' ) could result in the same final string, so binary concatenation has been chosen for this implementation.

Since [A] and [B] are INTEGER types, converting them to VARBINARY( 4 ) is sufficient to represent them. If they were BIGINT, we'd have used VARBINARY( 8 ). The added [C] column is a somewhat more obvious example. The built-in DATALENGTH function can help assist you in determining suitable values for binary concatenation scenarios.

An index is added ( despite the general rule to not index your staging tables since they slow down inserts and increase your storage footprint ), largely to facilitate maintaining historical loads. As your confidence grows in your solution, you may consider removing the indexes, favoring the truncation method mentioned earlier.

For the warehouse ( or fact ) table, I would set it up similar to the following:

CREATE TABLE dbo.WarehouseTable
(
    [DateInserted]  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    [DateStaged]    DATETIME,
    [HashBytes]     VARBINARY( 32 ),
    [KEY]           INTEGER NOT NULL IDENTITY( 1, 1 ),
    [A]             INTEGER,
    [B]             INTEGER,
    [C]             VARCHAR( 32 )
);

ALTER TABLE dbo.WarehouseTable
ADD CONSTRAINT PK__WarehouseTable
    PRIMARY KEY CLUSTERED ( [KEY] );

CREATE NONCLUSTERED INDEX IX__WarehouseTable__HashBytes__DateStaged
    ON  dbo.WarehouseTable ( [HashBytes], [DateStaged] );
GO

The [DateStaged] column is not entirely necessary in the truncation scenario, but included to again facilitate performing SELECT against a large staging table. The [DateInserted] column is not specifically necessary to the solution, but such a control column is invaluable and I balked when I thought about removing it. I tried, really.

The non-clustered index is added with the [HashBytes] column as the most significant key, since joins against the table from staging will heavily leverage that column. With these two structures in place, you have a loading query culminating as follows:

INSERT INTO dbo.WarehouseTable ( [DateStaged], [HashBytes], [A], [B], [C] )
SELECT  DISTINCT s.[DateInserted], s.[HashBytes], s.[A], s.[B], s.[C]
FROM    dbo.StagingTable s
WHERE   s.DateInserted > COALESCE( (    SELECT  MAX( [DateStaged] )
                                        FROM    dbo.WarehouseTable ), 0 )
    AND NOT EXISTS (    SELECT  [HashBytes]
                        FROM    dbo.WarehouseTable
                        WHERE   [HashBytes] = s.[HashBytes] );
GO

The results of a few mock loads can be seen in this SQL Fiddle. As @RFL points out, there's a good chance you'll want to consider tools designed for this sort of thing as well.