I have table with such structure
CREATE TABLE UsersHistory
(
Id INT IDENTITY,
UserID INT,
StatusId INT,
CreateTime DATETIME,
ChangedTime DATETIME
)
INSERT INTO UsersHistory(UserID, StatusId, CreateTime, ChangedTime)
SELECT 1,1,'20150414','20150414' UNION ALL
SELECT 1,2,'20150414','20150415' UNION ALL
SELECT 1,3,'20150414','20150416' UNION ALL
SELECT 2,1,'20150413','20150413' UNION ALL
SELECT 2,3,'20150413','20150416'
and query
;WITH k AS (
SELECT uh.UserID,MAX(uh.ChangedTime) AS Dt FROM UsersHistory AS uh
WHERE uh.ChangedTime<'20150416'
GROUP BY uh.UserID
)
SELECT k.UserID,uh.StatusId FROM k
INNER JOIN UsersHistory AS uh
ON k.UserID = uh.UserID AND k.Dt = uh.ChangedTime
Query is too easy and not needs more explanation. I want to simplify my it. (Remove join with datetime type column).
Any Suggestion?
You can use ROW_NUMBER()
with PARTITION
to achieve this. Something like this
;WITH CTE as
(
SELECT UserID, StatusId, CreateTime, ChangedTime,ROW_NUMBER()OVER(PARTITION BY UserID ORDER BY ChangedTime DESC) r
FROM UsersHistory
WHERE ChangedTime < '20150416'
)
SELECT UserID, StatusId FROM CTE
WHERE r = 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With