Sql how to get MAX and MIN Historydata along with corresponding user

1
votes

enter image description here

I have this table with HistoryDate and User. I need to query Max(Historydate), User correspondind to Maxdate, Min(HistoryDate), User corresponding to MinDate for each ReviewID like this 

   ModifiedDate |ModifiedBy| CreatedDate  | CreatedBy |ReviewId
  2/16/17 12:58 | Susan    | 2/7/17 15:39 | John      | 1
  2/15/17 13:25 | Sam      | 2/9/17 20:27 | John      | 2

I am able to query it using table variable but wondering any other way to get it.

2
sqlmaxmin
what do you want to happen when two reviews have the same history date? or do you want to post what you did to get it into a table variable? – Mic
Depending on ReviewId if it's max or min displayed along with User. Main purpose of this is to display Min Date and User as CreatedDate and CreatedBy and MaxDate and User as Latest ModifiedDate and ModifiedBy for each ReviewId and ignore rest of the data. – TsunamiCha

2 Answers

0
votes

Have a go at using sub-queries in the FROM clause to get each min and max. In order to get only the min/max for each review, you'll need to use GROUP BY ReviewID like so:

SELECT max.date AS maxDate, max.user AS maxUser, min.date AS minDate, 
min.user AS minUser
FROM (
    (SELECT MAX(HistoryDate) date, user, ReviewID
    FROM HistoryTable
    GROUP BY ReviewID) max
        JOIN 
    (SELECT MIN(HistoryDate) date, user, ReviewID
    FROM HistoryTable
    GROUP BY ReviewID) min
        ON max.ReviewID = min.ReviewID
)
0
votes

Hi after few days of playing with script this worked for me.

SELECT ModifiedDate, ModifiedBy, CreatedDate ,CreatedBy,  crt.ReviewID 
     FROM   (SELECT  HistoryDate  CreatedDate, ReviewId,   [User] CreatedBy FROM HistoryTable 
        WHERE HistoryDate in (SELECT min(HistoryDate)  FROM  HistoryTable GROUP BY ReviewID )) crt
INNER JOIN
    (SELECT HistoryDate  ModifiedDate, ReviewID, [User] ModifiedBy FROM  HistoryTable 
        WHERE HistoryDate in (SELECT Max(HistoryDate)  FROM HistoryTable GROUP BY ReviewID )) Modi
ON crt.ReviewId = Modi.ReviewID
         ORDER BY crt.ReviewID