How can I select values from different rows depending on the most recent entry date, all for the same employee ID?

Question

Basically I have a table which is used to hold employee work wear details. It is formed of the columns:

EmployeeID, CostCentre, AssociateLevel, IssueDate, TrouserSize, TrouserLength, TopSize & ShoeSize. 

An employee can be assigned a pair of trousers, a top and shoes at the same time or only one or two pieces of clothing. As we all know peoples sizes and employee levels can change which is why I need help really. Different types of employees (associatelevels) require different colours of clothing but you can ignore this part. Everytime an employee receives an item of clothing a new row will be inserted into the table with an input date. I need to be able to select the most recent clothes size for each item of clothing for each employee.

It is not necessary for all the columns to hold values because an employee could receive trousers or poloshirts at different times in the year.

So for example if employee '54664LSS' was given a pair of 'XL' trousers and a 'L' top on 24/03/11 but then received a 'M' top on 26/05/10. The input of these items would be help on two different rows obviously. So if I wanted to select the most recent clothing for each clothes category. Then the values of the 'M' sized top and the 'L' sized trousers would need to be returned.

Any help would be greatly appreciated as I'm pretty stuck :(. Thanks.

Answer 1

SELECT TOP 1
  TrouserSize, AssociateLevel
FROM 
  Employee
WHERE
  EmployeeID = '54664LSS' AND
  TrouserSize IS NOT NULL
ORDER BY 
  IssueDate DESC

SELECT TOP 1
  TopSize, AssociateLevel
FROM 
  Employee
WHERE
  EmployeeID = '54664LSS' AND
  TopSize IS NOT NULL
ORDER BY 
  IssueDate DESC

(Sorry I edited this sql about 6 times until I came up with what I think is the answer)

Assumptions - there is only one issue date column. You can have more than one item issued on the issue date, but you dont need to see those results together (otherwise we will have to group the unioned results). If there wasnt an item issued, a null is placed in the column.

Not sure what your logic will be if the AssociateLevel changes between clothing issues

Answer 2

It's always easier to answer if you can give a schema definition and some sample data, and the expected output, rather than a commentary. I've attempted a schema definition, haven't got any sample data, and think that this answer gives what you want:

/*create table Clothing (
 EmployeeID int not null,
 CostCentre varchar(10) not null,
 AssociateLevel int not null,
 IssueDate datetime not null,
 TrouserSize int null,
 TrouserLength int null,
 TopSize varchar(4) null,
 ShoeSize varchar(10) null
)*/
go
with Trousers as (
 select
  EmployeeID,
  CostCentre,
  AssociateLevel,
  IssueDate,
  TrouserSize,
  TrouserLength,
  RANK() OVER (PARTITION BY EmployeeID ORDER BY IssueDate Desc) as RowNum
 from
  Clothing
 where
  TrouserSize is not null
), Tops as (
 select
  EmployeeID,
  CostCentre,
  AssociateLevel,
  IssueDate,
  TopSize,
  RANK() OVER (PARTITION BY EmployeeID ORDER BY IssueDate Desc) as RowNum
 from
  Clothing
 where
  TopSize is not null
), Shoes as (
 select
  EmployeeID,
  CostCentre,
  AssociateLevel,
  IssueDate,
  ShoeSize,
  RANK() OVER (PARTITION BY EmployeeID ORDER BY IssueDate Desc) as RowNum
 from
  Clothing
 where
  ShoeSize is not null
)
select
  COALESCE(tr.EmployeeID,tops.EmployeeID,sh.EmployeeID) as EmployeeID,
  tr.CostCentre,
  tr.AssociateLevel,
  tr.IssueDate,
  tr.TrouserSize,
  tr.TrouserLength,
  tops.CostCentre,
  tops.AssociateLevel,
  tops.IssueDate,
  tops.TopSize,
  sh.CostCentre,
  sh.AssociateLevel,
  sh.IssueDate,
  sh.ShoeSize
from
 Trousers tr
  full outer join
 Tops
  on
   tr.EmployeeID = Tops.EmployeeID
  full outer join
 Shoes sh
  on
   tr.EmployeeID = sh.EmployeeID and
   Tops.EmployeeID = sh.EmployeeID
where
 (tr.RowNum is null or tr.RowNUm = 1) and
 (Tops.RowNum is null or Tops.RowNUm = 1) and
 (sh.RowNum is null or sh.RowNum = 1)