Hi Raz11,
since this is less of a technical problem and more of a coding problem let me give you my idea how your goal can be achieved:
First you need to have a way of getting the first and last entry for the current day. In order to get such a list you basically need to split the entire list per patient and order that list by the timestamp ascending which yields a sublist including all measurements for that patient. Next find the first and the last entry. Repeat this step for all patients.
Depending on what technology you are using (all done in OpenSQL or loading the entire medical database into one itab via OpenSQL and extracting the data for each patient into another itab via OpenSQL/ABAP your exact way of doing may differ.
One solution (may not be the most efficient one) is to do a SELECT on the medical database to get all data, a SELECT DISTINCT on the same but just for the patient Ids. Then loop over patient IDs and for each patient IDmake query against the medical itab where you fetch all the data associated with that patient and store them in a third iTab let's call it lt_patient_data. Get the length of that itab via data(lv_tab_size) lines( lt_patient_data). Finally you can access this table via index. Index [1] for the first element, Index [lv_tab_size] for the last entry (ABAP starts at 1 with indexing).
This code snippet should show my idea
" Get all data
select * from t005 into table (lt_t005).
" Get the primary key (PK)
select distinct land1 from t005 into table (lt_laender).
" Loop over all the PK values
loop at lt_laender into data(ls_land).
" Query against the main db with the PK
select * from _t005 as t where land1 = _land-land1 into table (lt_t005_02).
" Get the table size
data(lv_tab_size) = lines( lt_t005_02 ).
" Retrieve the firs and the last row
data(ls_first_row) = lt_t005_02[ 1 ].
data(ls_last_row) = lt_t005_02[ lv_tab_size ].
" Work with the data
ls_first_row...
ENDLOOP.
Edit: For the second question you have to be more specific what your goal is. Meaning what exactly you want to calculate. If you just want one of the like 3 values per patient per day then either first/last entry with indexing OR with a different subselect.
If your goal is to do the calculation of the say first entry for all patients with one SQL statement you should aim to first extract the first measurement per patient from the table and store those data in a second table and then iterate over that secondary table and do the calcs
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.