CODEWITHSUNDEEP
sqlsql-server-2005reporting-servicesreportingservices-2005
Kon
Kon

Reputation: 385

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

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.

Upvotes: 2

Views: 789

Answers (3)

Taryn
Taryn

Reputation: 247690

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

Upvotes: 1

Kon
Kon

Reputation: 385

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

Upvotes: 0

Lamak
Lamak

Reputation: 70638

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.

Upvotes: 1

Related Questions