Search code examples
sqllinqdatabase-designderiving

Deriving currently on loan items

I have a table of loan information.

example:

_________________________________________________________________
|id|LoanDate|EquipmentId|FromUser|FromLocation|ToUser|ToLocation|
|---------------------------------------------------------------|
|1 |2012-1-1| 100       | NULL   |  219       | 605  | NULL     |
|2 |2012-1-2| 100       | 605    |  NULL      | NULL | 219      |
|3 |2012-1-3| 100       | NULL   |  219       | 509  | NULL     |
-----------------------------------------------------------------

As you can see, user 605 was loaned equipment for one day, then returned it the following day. Then the equipment was loaned to a different user the day after that.

How would I go about trying to find which user which currently has what piece of equipment currently on loan to them, considering that this table has entries that track the movement - including returns across dates.

Any ideas about how to go about this would be appreciated. Pseudo-code, SQL or LINQ are fine too.

Thanks

Solution

  • If you order the loans by LoanDate descending and then group by EquipmentId, the first element in each group will be the most recent loan for its EquipmentId. If the userId is not null, then that is the ID of the user who currently has the equipment.

    var items = from loan in loans
            orderby loan.LoanDate desc
            group loan by loan.EquipmentId into grp
            let userId = grp.First().ToUser
            where userId != null
            select new { EquipmentId = grp.Key, UserId = userId };