How to include loop logics in SQLscript

[Former Member]

I have a base table that stores Patient name, Date of Capture and different Diagnostic parameters. For the purpose of simplicity let us take that the diagnostic parameter here is blood pressure reading.

Readings will be submitted many times for each patient.

From this I need to create a view that has

Patient name, Reading on day-1, Reading on day-2 ......Reading on day-14

where the date that is used for the calc is the last date of reading for every patient.

So the key in the view will be Patient only.

I know that this means data redundancy but the purpose of this view is to provide data for statistical modelling.

I know how to do this modelling in ABAP but with the limited options of looping in SQLscript , how do i achieve this. I know this might not be very efficient but this is what i need.

[Former Member · Accepted solution]

Hi Samarpita,

You can carry out this type of functionality using Window Partitioning in SQL. This feature was added in SPS5. The syntax to obtain the last date of reading using the Patient as the key would be along the lines of:

SELECT PATIENT,

               LAST_VALUE(DATE_OF_CAPTURE) OVER (PARTITION BY PATIENT SORT BY DATE_OF_CAPTURE) as last

FROM TABLE_NAME

For more details on Window Partitioning see the help instructions found here:

http://help.sap.com/hana/html/_esql_functions_window.html

Regards,

Gary Elliott