MIME-Version: 1.0
From: Shaozhong SHI <shishaozhong@gmail.com>
Date: Mon, 12 Dec 2022 22:43:08 +0000
Message-ID: 
 <CA+i5JwbWP5CpuQ5v=orYUUW-r0mLdrNpgGy4fUeC4UhWkAYYZQ@mail.gmail.com>
Subject: Tom changed his job role many times
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e016e305efa93aef"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2Bi5JwbWP5CpuQ5v%3DorYUUW-r0mLdrNpgGy4fUeC4UhWkAYYZQ%40mail.gmail.com>
Precedence: bulk

--000000000000e016e305efa93aef
Content-Type: text/plain; charset="UTF-8"

Tom has changed his job role many times.

We want to pick up only the records before and after his role change to
show what happened.

select *, coalesce(lag(department) over(partition by name order by year),
department) <> department Changed
from (Values (1, 'Tom', 'Sales', 1990),(2, 'Tom', 'Sales', 1991),(3, 'Tom',
'Sales', 1991),(4, 'Tom', 'Management', 1992),(4, 'Tom', 'Management',
1992), (4, 'Tom', 'Sales', 1993),(4, 'Tim', 'Finance', 1981), (4, 'Tim',
'Finance', 1982), (4, 'Tim', 'Management', 1983), (4, 'Tim', 'Management',
1984)) as x(Staff_ID, Name, Department, Year);

Regards,

David

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

<div dir=3D"ltr"><div dir=3D"ltr"><div>Tom has changed his job role many ti=
mes.</div><div><br></div><div>We want to pick up only the records before an=
d after his role change to show what happened.</div><div><br></div><div>sel=
ect *, coalesce(lag(department) over(partition by name order by year), depa=
rtment) &lt;&gt; department Changed=C2=A0</div><div>from (Values (1, &#39;T=
om&#39;, &#39;Sales&#39;, 1990),(2, &#39;Tom&#39;, &#39;Sales&#39;, 1991),(=
3, &#39;Tom&#39;, &#39;Sales&#39;, 1991),(4, &#39;Tom&#39;, &#39;Management=
&#39;, 1992),(4, &#39;Tom&#39;, &#39;Management&#39;, 1992), (4, &#39;Tom&#=
39;, &#39;Sales&#39;, 1993),(4, &#39;Tim&#39;, &#39;Finance&#39;, 1981), (4=
, &#39;Tim&#39;, &#39;Finance&#39;, 1982), (4, &#39;Tim&#39;, &#39;Manageme=
nt&#39;, 1983), (4, &#39;Tim&#39;, &#39;Management&#39;, 1984)) as x(Staff_=
ID, Name, Department, Year);</div><div><br></div><div>Regards,</div><div><b=
r></div><div>David</div></div></div>

--000000000000e016e305efa93aef--