Re: Solving my query needs with Rank and may be CrossTab

> On Dec 2, 2019, at 9:55 AM, Iaam Onkara <iamonkara(at)gmail(dot)com> wrote:
>
> @Rob. What your referring to sounds like Materialized views, isn't it? An example query would be helpful in understand your recommendation/approach better.
>
> On Mon, Dec 2, 2019 at 7:42 AM Rob Sargent <robjsargent(at)gmail(dot)com <mailto:robjsargent(at)gmail(dot)com>> wrote:
>

Using the update-fixed-table style:
-- Get all possible people, null their values
create table report as
select distinct patient, null::float as bmi, null::float as sysbp, null::float as diabp, null::int as height
from source_table;
create index on report(patient);
-- get the height code (8302-2 using tilde operator because the import included leading blanks)
update report r set height = last_value
from (select distinct patient, last_value(measurement) over
(partition by patient, code
order by sampletime)
from source_table
where code ~ '8302-2') as m where r.patient = m.patient
;
-- then similar for other codes. You may want to format the results, as in combining sys/dia bp readings after the update operations
-- the time drag of course is forever finding max(measurement time). A composite index might help; indeed the unique key on the source is patient,code,timestamp I think.