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

--0000000000003fa0a70598b26b2c
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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 <[email protected]> wrote:

>
>
> On Dec 1, 2019, at 4:38 PM, Iaam Onkara <[email protected]> wrote:
>
> =EF=BB=BF
> @Rob: There is no time window. It is the latest values for given set of
> attributes regardless of timestamp. If some attributes have multiple valu=
es
> 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 no=
t
> be performant specially when the dataset grows, I am looking for a soluti=
on
> which doesn't require one sub select per column.
>
> On Sun, Dec 1, 2019 at 5:28 PM Rob Sargent <[email protected]> wrote:
>
>>
>>
>> On Dec 1, 2019, at 3:54 PM, Iaam Onkara <[email protected]> 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 s=
et
>> 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 =3D '8480-6' or v.code=3D'8462-4' or v.code=3D'39156-5' or
>> v.code=3D'8302-2')
>> )
>> SELECT c.display, uom, val, created_on
>> from v_max v inner join codes c on v.code=3Dc.code
>> where r =3D 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 colum=
n
>> or (usually faster) temporary tables built up in separate selects with e=
ach
>> 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 als=
o
> 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.
>

--0000000000003fa0a70598b26b2c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Yes indexes on Code and Timestamp column may also be neede=
d even though Patient_ID column will be indexed.<div><br></div><div>By incr=
emental tables do you mean tables with Auto Increment primary key for ID ?<=
/div><div><br></div><div>What I am having tough time figuring out is how to=
 transform the result into=C2=A0<a href=3D"https://gist.github.com/daya/0cb=
7f8682520a1dd4cdda8c0266f77f6">this</a> even after using multiple CTEs</div=
></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr"=
>On Sun, Dec 1, 2019 at 10:58 PM Rob Sargent &lt;<a href=3D"mailto:robjsarg=
[email protected]">[email protected]</a>&gt; wrote:<br></div><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex"><div dir=3D"auto"><div dir=3D"ltr"><br>=
</div><div dir=3D"ltr"><br><blockquote type=3D"cite">On Dec 1, 2019, at 4:3=
8 PM, Iaam Onkara &lt;<a href=3D"mailto:[email protected]" target=3D"_bla=
nk">[email protected]</a>&gt; wrote:<br><br></blockquote></div><blockquot=
e type=3D"cite"><div dir=3D"ltr">=EF=BB=BF<div dir=3D"ltr">@Rob: There is n=
o time window. It is the latest values for given set of attributes regardle=
ss of timestamp. If some attributes have multiple values then multiple rows=
 can be returned with other attributes having blank values.<div><br></div><=
div>Creating=C2=A0one 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&#39;t require one sub select per column.<br></div></d=
iv><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On =
Sun, Dec 1, 2019 at 5:28 PM Rob Sargent &lt;<a href=3D"mailto:robjsargent@g=
mail.com" target=3D"_blank">[email protected]</a>&gt; wrote:<br></div><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex"><div><br><div><br><blockqu=
ote type=3D"cite"><div>On Dec 1, 2019, at 3:54 PM, Iaam Onkara &lt;<a href=
=3D"mailto:[email protected]" target=3D"_blank">[email protected]</a>&g=
t; wrote:</div><br><div><div dir=3D"ltr">Hi Friends,<div><br></div><div>I h=
ave a table with data like this gist <a href=3D"https://gist.github.com/day=
a/d0794efcd4278fc5dce6e7339d03a8fd" target=3D"_blank">https://gist.github.c=
om/daya/d0794efcd4278fc5dce6e7339d03a8fd</a> and I want to fetch the latest=
 values for a given set of attributes so the result set looks like this gis=
t=C2=A0<a href=3D"https://gist.github.com/daya/0cb7f8682520a1dd4cdda8c0266f=
77f6" target=3D"_blank">https://gist.github.com/daya/0cb7f8682520a1dd4cdda8=
c0266f77f6</a></div><div><br></div><div>Please note in the desired result s=
et=C2=A0</div><div><ol><li>There is an assumed mapping of Code to display i=
.e. code=C2=A039156-5 is BMI.</li><li>&quot;Oxygen Saturation&quot; has onl=
y one value and &quot;Pulse&quot; has no value</li></ol><div>In my attempts=
 and with some help I have=C2=A0 this query=C2=A0</div></div><div><br></div=
><div><font face=3D"monospace">with v_max as <br>(SELECT <br>	code, uom, va=
l, created_on, dense_rank() over ( partition by code order by created_on de=
sc) as r <br>FROM vitals v<br>where (v.code =3D &#39;8480-6&#39; or v.code=
=3D&#39;8462-4&#39; or v.code=3D&#39;39156-5&#39; or v.code=3D&#39;8302-2&#=
39;)<br>) <br>SELECT c.display, uom, val, created_on <br>from v_max v inner=
 join codes c on v.code=3Dc.code<br>where r =3D 1;=C2=A0<br></font></div><d=
iv><font face=3D"monospace"><br></font></div>which gives <a href=3D"https:/=
/gist.github.com/daya/19ca22a837ed9998c117f38ff3cce3f2" target=3D"_blank">t=
his result</a>=C2=A0<div><br></div><div>But the <a href=3D"https://gist.git=
hub.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6" target=3D"_blank">result set=
 that I want</a>=C2=A0I am unable to get. Or if is it even possible to get?=
</div><div><br></div><div>Thanks for your help</div><div><br></div></div>
</div></blockquote></div><br><div>I take it the last value by timestamp per=
 code per patient is the one to be reported? Or is there a time window?</di=
v><div>Turning rows into columns can be done with sub-selects per derived c=
olumn or (usually faster) temporary tables built up in separate selects wit=
h each adding usually one column (but possibly more).</div><div><br></div><=
/div></blockquote></div>
</div></blockquote><br><div>I agree the sub select per column can easily be=
come slow. Incremental tables does not, in my experience, suffer the same p=
roblem. =C2=A0(One can also repeatedly update a single table with the prede=
fined 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 tha=
n to incremental tables.=C2=A0</div><div>From your further description of t=
he problem it will be critical to have an index on the code AND time stamp =
columns of the source table.=C2=A0</div></div></blockquote></div>

--0000000000003fa0a70598b26b2c--