You can use ranking functions. See:
About ROW_NUMBER(), RANK(), and DENSE_RANK() Functions in SAP HANA | SAP Blogs[
^]
How to pivot/unpivot in SAP HANA | SAP Blogs[
^]
Assuming that
Calweek
is numeric field, something like that should do the job.
SELECT DISTINCT [No.],
MAX([volume]) OVER(PARTITION BY [No.], [volume] ORDER BY [Calweek]) AS [VOL]
MIN(Calweek) OVER(PARTITION BY [No.], [volume] ORDER BY Calweek,) AS FWEEK,
MAX(Calweek) OVER(PARTITION BY [No.], [volume] ORDER BY [Calweek] DESC) AS LWEEK,
FROM <YourSchema>.<YourTable>;
Good luck!