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

Yes indexes on Code and Timestamp column may also be needed even though
Patient_ID column will be indexed.

By incremental tables do you mean tables with Auto Increment primary key
for ID ?

What I am having tough time figuring out is how to transform the result
into this <https://gist.github.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6>
even after using multiple CTEs

On Sun, Dec 1, 2019 at 10:58 PM Rob Sargent <robjsargent(at)gmail(dot)com> wrote:

>
>
> On Dec 1, 2019, at 4:38 PM, Iaam Onkara <iamonkara(at)gmail(dot)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.
>
> On Sun, Dec 1, 2019 at 5:28 PM Rob Sargent <robjsargent(at)gmail(dot)com> wrote:
>
>>
>>
>> On Dec 1, 2019, at 3:54 PM, Iaam Onkara <iamonkara(at)gmail(dot)com> wrote:
>>
>> Hi Friends,
>>
>> I have a table with data like this gist
>> https://gist.github.com/daya/d0794efcd4278fc5dce6e7339d03a8fd and I want
>> to fetch the latest values for a given set of attributes so the result set
>> looks like this gist
>> https://gist.github.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6
>>
>> Please note in the desired result set
>>
>> 1. There is an assumed mapping of Code to display i.e. code 39156-5
>> is BMI.
>> 2. "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
>> <https://gist.github.com/daya/19ca22a837ed9998c117f38ff3cce3f2>
>>
>> But the result set that I want
>> <https://gist.github.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6> I am
>> unable to get. Or if is it even possible to get?
>>
>> 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.
>