I need to get the last value (status) from a child table. I try to simplify the problem with the following example.
Create Table Users
(
UserId int,
Lastname nvarchar(50)
)
Create Table UserStatus
(
UserId int,
Date datetime,
StatusId int
)
Create Table Status
(
StatusId int
Status nvarchar(50)
)
A user will go through all Status one by one. (1) Registered -> (2) In progress -> (3) authorized.
Now I want to know which users are in progress (2) but a simple select statement like:
Select LastName from Users Inner Join Users.UsersId = UserStatus.UserId Where UsersStatus.StatusId = 2
Will not return the wanted records because all authorized Users have been in this status.
I hope you understand the problem and can help me out.
Thx in advance.
Etinuzso you want the last names of users that stopped at status 2?
Select Users.LastName From Users Inner Join (Select Max(StatusID) As MaxStatusID, UserID, UserStatus UserID) As LimitedStatus On Users.UserID = LimitedStatus.UserID WHERE MaxStatusID = 2
Or something to that effect.|||Thx KraGIE, i suppose this is the right direction. Only the values are not in a specific order (and can not be set this way). So the example was not right.
So a user can be Authorized (3) and later set to In progress (2). However in the table there is a ID.
Create Table UserStatus
(
Id int, (identifier)
UserId int,
Date datetime,
StatusId int
)
At this moment I havent figured out how to use
Select Users.LastName From Users Inner Join (Select Max(ID) As MaxID, UserID, StatusId From UserStatus Group BY USerId, StatusId) As LimitedStatus On Users.UserID = LimitedStatus.UserID WHERE StatusID = 2|||I found my solution
SELECT
u.LastName
FROM
Users u
JOIN UserStatus us
ON u.UserId = us.UserId
JOIN
(SELECT UserId, MAX([Date]) AS last_date
FROM UserStatus
GROUP BY UserId ) AS last_status
ON u.UserId = last_status.UserId
AND us.[Date] = last_status.last_date
WHERE
us.StatusId = 2
