On Dec 1, 2019, at 4:38 PM, Iaam Onkara <iamonkara@gmail.com> wrote:
@Rob: There is no time window. It is the latest values for given set of attributes regardless of timestamp. If some attributes have multiple values then multiple rows can be returned with other attributes having blank values.
Creating one sub select for one column is an obvious approach but will not be performant specially when the dataset grows, I am looking for a solution which doesn't require one sub select per column.
Hi Friends,
Please note in the desired result set
- There is an assumed mapping of Code to display i.e. code 39156-5 is BMI.
- "Oxygen Saturation" has only one value and "Pulse" has no value
In my attempts and with some help I have this query
with v_max as
(SELECT
code, uom, val, created_on, dense_rank() over ( partition by code order by created_on desc) as r
FROM vitals v
where (v.code = '8480-6' or v.code='8462-4' or v.code='39156-5' or v.code='8302-2')
)
SELECT c.display, uom, val, created_on
from v_max v inner join codes c on v.code=c.code
where r = 1;
which gives
this result
Thanks for your help
I take it the last value by timestamp per code per patient is the one to be reported? Or is there a time window?
Turning rows into columns can be done with sub-selects per derived column or (usually faster) temporary tables built up in separate selects with each adding usually one column (but possibly more).
I agree the sub select per column can easily become slow. Incremental tables does not, in my experience, suffer the same problem. (One can also repeatedly update a single table with the predefined structure.) There maybe a way to get what you want with multiple CTEs but I suspect that approach would be more akin to multiple sub selects than to incremental tables.
From your further description of the problem it will be critical to have an index on the code AND time stamp columns of the source table.