Inserting records based on a recent date in SQL Server

Question

I have two tables Table 1 and Table 2.

Shown in this picture

Table 1 is the master table.

I need to insert data to Table 2 from Table 1 based on the Sale Date. ProductID is the primary key in Table 2. For example, for product ID 100276408 in table 1, the most recent sale date is 11/4/2015; for product ID 100276409, the most recent sale date is 17/8/2014; fro product ID 100276400, the most recent sale date is 7/7/2016. I need to insert these three records in to Table 2 .The result I want is shown in the Table 2. How Do I do that in SQL server 2014? Thank you for your time and help.

Answer 1

Try like this,

   INSERT INTO Table2 (
    ProductId
    ,LastCheckDate
    ,SaleDate
    ,SaleStatusCode
    ,SaleType
    )
SELECT t.ProductId
    ,t.LastCheckDate
    ,t.SaleDate
    ,t.SaleStatusCode
    ,t.SaleType
FROM (
    SELECT ProductId
        ,LastCheckDate
        ,SaleDate
        ,SaleStatusCode
        ,SaleType
        ,row_number() OVER (
            PARTITION BY productid ORDER BY productid
                ,saledate DESC
            ) rn
    FROM Table1
    ) t
WHERE t.rn = 1

Answer 2

I would try this:

SELECT ProductID, LastCheckDate, SaleDate, SaleStatusCode, SaleType INTO Table1
FROM (SELECT *, 
    ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY SaleDate DESC AS rk)) T
WHERE rk = 1

I think this will pick randomly if there is a tie so you might want to think about that.