Wednesday, November 29, 2017

How do I get the distinct values from one column in T-SQL while showing the most current value from a second column?

Declare @StatusesFat TABLE
(
   EmployeeId uniqueidentifier NOT NULL,
   WorkStatus varchar(20) NULL
)
INSERT INTO @StatusesFat

...IMAGINE WHATEVER YOU WANT HERE...

Declare @StatusesFlat TABLE
(
   Id uniqueidentifier NOT NULL
)
INSERT INTO @StatusesFlat
SELECT distinct EmployeeId from @StatusesFat
GROUP BY EmployeeId
Declare @Statuses TABLE
(
   EmployeeId uniqueidentifier NOT NULL,
   WorkStatus varchar(20) NULL
)
INSERT INTO @Statuses
SELECT Id as 'EmployeeId', (SELECT top 1 WorkStatus FROM @StatusesFat
      WHERE EmployeeId = Id) as 'WorkStatus'
FROM @StatusesFlat

No comments:

Post a Comment