14

14

ms

DECLARE @as_of_date DATE = '2014-01-04'

;WITH cte AS (
    SELECT  [id], [idd], [created_on], [property], [old_value], [new_value], rn=ROW_NUMBER() OVER (PARTITION BY idd ORDER BY created_on DESC)
    FROM    YourTable
    WHERE created_on <= @as_of_date
)
SELECT  *
FROM    cte
WHERE rn = 1

;WITH CTE1 (created_on, idd)
AS (
    SELECT MAX(created_on), idd
    FROM Table_1
    WHERE created_on < @DateTo
    GROUP BY idd)
SELECT t1.* FROM Table_1 t1
INNER JOIN CTE1 ON t1.created_on = CTE1.created_on and t1.idd = CTE1.idd
ORDER BY idd;


SELECT * FROM Table_1 t1
WHERE t1.created_on = (SELECT MAX(created_on) FROM Table_1 t2 where t1.idd = t2.idd AND created_on < @DateTo)
ORDER BY idd;