I used an inline load because I don't have your qvd.
Also I added 2 rows (1123) and change the status date (in your data the year is missing)
load SR_NO, SR_STATUS, Date(Date#(SR_STATUS_DATE, 'DD-MMM-YYYY')) as SR_STATUS_DATE inline [
SR_NO, SR_STATUS, SR_STATUS_DATE
123, Open, 01-jan-2015
123, Closed, 15-jan-2015
1123, Open, 01-jan-2015
1123, Closed, 15-jan-2015
inner join (source)
Date(max(SR_STATUS_DATE)) as SR_STATUS_DATE
group by SR_NO;
A single step aggregation cannot provide the maximum value of SR_STATUS_DATE grouping by SR_NO and the disaggregated values of the fields SR_STATUS and ST_STATUS_DATE. I would do it in two steps, one for getting the raw data and other to join the aggregated maximum value.
FROM [$(vQVDBasePath)\SERVICE_REQUESTS_STATUSES.qvd] (qvd);
INNER JOIN (SR_TMP)
LOAD SR_NO, max(SR_STATUS_DATE) as MAX_SR_STATUS_DATE
GROUP BY SR_NO;