lms lms - 29 days ago 6
SQL Question

SQL Server column max based on another column

I have a dataset as shown below in a booking table

Custid ApptDate oldornewApp
-------------------------------
100 01-jan-2013 old
100 01-jan-2014 old
100 01-oct-2016 new
100 12-oct-2016 new
200 01-feb-2015 old
200 10-oct-2016 new


I am trying for an output which shows max date based on the
oldornewapp
column for each customer

custid MaxApptDateOldApp MaxapptDatenewapp
---------------------------------------------
100 01-jan-2014 12-oct-2016
200 01-feb-2015 10-oct-2016


Please let me know a best way to get the resultset, I am using SQL Server 2012

Thanks in advance

Answer
SELECT
    Custid
    ,MAX(CASE WHEN oldornewApp = 'old' THEN ApptDate END) as MaxApptDateOldApp
    ,MAX(CASE WHEN oldornewApp = 'new' THEN ApptDate END) as MaxApptDateNewApp
FROM
    TableName
GROUP BY
    Custid

You can use conditional aggregation to get to your desired result.

Comments