What SQL query or LINQ code would get the following data?

Answer 1

The most recent work plan for all users:

SELECT
     WP1.*
FROM
     Workplans WP1
LEFT OUTER JOIN dbo.Workplans WP2 ON
     WP2.UserID = WP1.UserID AND
     WP2.Date > WP1.Date
WHERE
     WP2.WorkplanID IS NULL

For a given set of users, write a table-valued function that turns a delimited list of IDs into a table of those IDs. If you search around I know I've posted that code on here before. Then you can use:

SELECT
     WP1.*
FROM
     dbo.GetTableFromIDList(@id_list) U
INNER JOIN Workplans WP1 ON
     WP1.UserID = U.ID
LEFT OUTER JOIN dbo.Workplans WP2 ON
     WP2.UserID = WP1.UserID AND
     WP2.Date > WP1.Date
WHERE
     WP2.WorkplanID IS NULL

For a given set of users, return all of their current projects:

SELECT
     P.*
FROM
     dbo.GetTableFromIDList(@id_list) U
INNER JOIN Workplans WP1 ON
     WP1.UserID = U.ID
LEFT OUTER JOIN dbo.Workplans WP2 ON
     WP2.UserID = WP1.UserID AND
     WP2.Date > WP1.Date
INNER JOIN dbo.Projects P ON
     P.WorkplanID = WP1.WorkplanID
WHERE
     WP2.WorkplanID IS NULL

Of course, you should actually list out the column names instead of using "*".