Select column name of max date from each row in SQL/SSRS 2005

Question

How can I add the 'Last Step' column on the far right to a report in SSRS? Essentially, I need the name of the column that contains the most recent date from that row. Actual data will be a few thousand rows, each with ~15 dates.

Name Step X Step W Step A Step B Step Y Last Step ------------------------------------------------------------------------------- John 1/21/2013 1/24/2013 1/3/2013 1/27/2013 1/7/2013 Step B Sally 1/22/2013 1/24/2013 1/12/2013 1/1/2013 1/5/2013 Step W

SQL Fiddle with simple example data.

Community · Accepted Answer · 2017-05-23 12:12:31Z

Here is another way of doing this by modifying the query from your first question. This just adds the name into the result so you can get the max(date) for each name:

;with cte as ( select name, col, value from yourtable unpivot ( value for col in ([Step X], [Step W], [Step A], [Step B], [Step Y]) ) unpiv ) select t.name, [Step X], [Step W], [Step A], [Step B], [Step Y], d.col LastStep from yourtable t cross apply ( select c1.col from cte c1 inner join ( select name, max(value) MaxDate from cte group by name ) c2 on c1.value = c2.maxdate and c1.name = c2.name where t.name = c1.name ) d

See SQL Fiddle with Demo

Lamak · Accepted Answer · 2013-02-18 19:51:21Z

Here is a way without using dynamic sql (since this is for reporting services):

SELECT T.*, C.ColName FROM yourtable T OUTER APPLY (SELECT TOP 1 * FROM (SELECT 'Step X', [Step X] UNION ALL SELECT 'Step W', [Step W] UNION ALL SELECT 'Step A', [Step A] UNION ALL SELECT 'Step B', [Step B] UNION ALL SELECT 'Step Y', [Step Y]) X(ColName,ColValue) ORDER BY ColValue DESC) C

and the fiddle.

Kon · Accepted Answer · 2013-02-18 21:37:12Z

For Posterity's sake, here is a modified version of bluefeet's answer to a similar question, and the fiddle.

WITH cte AS ( SELECT *, Rank() OVER (PARTITION BY Name ORDER BY value DESC) AS Rank FROM ( SELECT * from yourtable unpivot ( value for col in ([Step X], [Step W], [Step A], [Step B], [Step Y]) ) unpiv ) X ) SELECT yt.[Step X] , yt.[Step W] , yt.[Step A] , yt.[Step B] , yt.[Step Y] , c1.col FROM yourtable yt JOIN cte c1 ON yt.Name = c1.Name AND c1.Rank = 1

Collectives™ on Stack Overflow

Select column name of max date from each row in SQL/SSRS 2005

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related