Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <pgsql-sql-owner+archive@lists.postgresql.org>)
	id 1i6FKj-0001hY-4n
	for pgsql-sql@arkaria.postgresql.org; Fri, 06 Sep 2019 14:35:29 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-sql-owner+archive@lists.postgresql.org>)
	id 1i6FKi-000741-0G
	for pgsql-sql@arkaria.postgresql.org; Fri, 06 Sep 2019 14:35:28 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <jj08@drivehq.com>)
	id 1i6FKh-0006o3-HM
	for pgsql-sql@lists.postgresql.org; Fri, 06 Sep 2019 14:35:27 +0000
Received: from mail02.mbizgroup.biz ([66.220.9.54] helo=mail.drivehq.com)
	by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.92)
	(envelope-from <jj08@drivehq.com>)
	id 1i6FKb-0004yS-0i
	for pgsql-sql@lists.postgresql.org; Fri, 06 Sep 2019 14:35:26 +0000
Received: from  (192.168.10.210)
	by EM244o80 with SMTP User ID0; Fri, 06 Sep 2019 14:35:17 GMT
Received: from 60.111.238.211 (60.111.238.211)
	by mail.drivehq.com with HTTPUserID9736706; Fri, 06 Sep 2019 14:35:17 GMT
X-Priority: 3
Message-ID: <2259433721000000009736706@www>
From: "jj08" <jj08@drivehq.com>
To: jj08@drivehq.com,
	pgsql-sql@lists.postgresql.org,
	janis@puris.lv
Subject: RE:Re: A complex SQL query
Date: Fri, 06 Sep 2019 07:35:17 -0700
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_225943372_9736706_637033521174417679"
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Precedence: bulk

This is a multi-part message in MIME format.

------=_NextPart_225943372_9736706_637033521174417679
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable



Yes, that's exactly what works for me.

 Thanks a bunch!!!

  =


 =

--From: janis@puris.lv
 --To: pgsql-sql@lists.postgresql.org,jj08@drivehq.com
 --Date: 9/6/2019 5:56:10 AM --Subject: Re: A complex SQL query  Would s=
omething like this work for you ? http://www.sqlfiddle.com/#!17/2e45eb/9=
    select     user_id,     max(start_date) as start_date,     case when=
 max(end_date) < max(start_date) then null else max(end_date) end as end=
_date</div> from     employment where     employer =3D 'Micro' group by =
    user_id ;    On 6 Sep 2019, 14:31 +0200, jj08 <jj08@drivehq.com>
, wrote:  =


  I hope someone can give me some pointers.

 Here is my table.

 +--------+----------+------------+-----------+

 | usr_id | employer | start_date | end_date  |

 +--------+----------+------------+-----------+

 | A      | Goo      | 201904     | -         |

 | A      | Micro    | 201704     | 201903    |

  =


 | B      | Micro    | 201706     | -         |

 | B      | Goo      | 201012     | 201705    |

 | B      | Micro    | 201001     | 201011    |

 +--------+----------+------------+-----------+

  =


 I am trying to list up people working for a company called &quot;Micro&=
quot;.

 Some people work for a company multiple times, like user B.

 I only need one line per user, displaying only the latest affiliation d=
ate.

  =


 For user_id &quot;B&quot;, I could do Select user_id, MAX(start_date), =
end_date where employer =3D 'Micro',

 but that would fail to get record for user_id &quot;A&quot;.

  =


 If multiple records exist, I want to do MAX, but if only a single recor=
d exists, I don't need MAX.

 How do I do that? =


 From the above data, I would like to see only two lines:

 | A      | Micro    | 201704     | 201903    |

 | B      | Micro    | 201706     | -         |

 Thank you. =

 =

 -------------------------
 Online Storage & Sharing, Online Backup, FTP / Email Server Hosting and=
 More.
 Drive Headquarters. Top quality services designed for business! Sign up=
 free at: www.DriveHQ.com
. =

 =

 -------------------------
 Online Storage & Sharing, Online Backup, FTP / Email Server Hosting and=
 More. =

 Drive Headquarters. Top quality services designed for business!  Sign u=
p free at: www.DriveHQ.com
.  =



------=_NextPart_225943372_9736706_637033521174417679
Content-Type: text/html;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable

<html><body><div id=3D'content' style=3D'font-family: Arial'><p>Yes, tha=
t's exactly what works for me.</p>
<p>Thanks a bunch!!!</p>
<p>&nbsp;</p>
<p>--From: janis@puris.lv<br />
--To: pgsql-sql@lists.postgresql.org,jj08@drivehq.com<br />
--Date: 9/6/2019 5:56:10 AM<br />
--Subject: Re: A complex SQL query</p>
<div name=3D"messageBodySection">
<div dir=3D"auto">Would something like this work for you ?&nbsp;<a href=3D=
"http://www.sqlfiddle.com/#!17/2e45eb/9">http://www.sqlfiddle.com/#!17/2=
e45eb/9</a>
<div dir=3D"auto">&nbsp;</div>
<div dir=3D"auto">
<div dir=3D"auto">select</div>
<div dir=3D"auto">&nbsp; &nbsp; user_id,</div>
<div dir=3D"auto">&nbsp; &nbsp; max(start_date) as start_date,</div>
<div dir=3D"auto">&nbsp; &nbsp; case when max(end_date) < max(start_date=
) then null else max(end_date) end as end_date</div>
<div dir=3D"auto">from</div>
<div dir=3D"auto">&nbsp; &nbsp; employment</div>
<div dir=3D"auto">where</div>
<div dir=3D"auto">&nbsp; &nbsp; employer =3D 'Micro'</div>
<div dir=3D"auto">group by</div>
<div dir=3D"auto">&nbsp; &nbsp; user_id</div>
<div dir=3D"auto">;</div>
</div>
</div>
</div>
<div name=3D"messageReplySection">On 6 Sep 2019, 14:31 +0200, jj08 <jj08=
@drivehq.com>, wrote:<br />
<blockquote type=3D"cite" class=3D"spark_quote" style=3D"margin: 5px 5px=
; padding-left: 10px; border-left: thin solid #1abc9c;">
<div id=3D"content" style=3D"font-family: Arial">
<p>&nbsp;I hope someone can give me some pointers.</p>
<p>Here is my table.</p>
<p>+--------+----------+------------+-----------+</p>
<p>| usr_id | employer | start_date | end_date&nbsp; |</p>
<p>+--------+----------+------------+-----------+</p>
<p>| A&nbsp; &nbsp; &nbsp; | Goo&nbsp; &nbsp; &nbsp; | 201904&nbsp; &nbs=
p; &nbsp;| -&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|</p>
<p>| A&nbsp; &nbsp; &nbsp; | Micro&nbsp; &nbsp; | 201704&nbsp; &nbsp; &n=
bsp;| 201903&nbsp; &nbsp; |</p>
<p>&nbsp;</p>
<p>| B&nbsp; &nbsp; &nbsp; | Micro&nbsp; &nbsp; | 201706&nbsp; &nbsp; &n=
bsp;| -&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|</p>
<p>| B&nbsp; &nbsp; &nbsp; | Goo&nbsp; &nbsp; &nbsp; | 201012&nbsp; &nbs=
p; &nbsp;| 201705&nbsp; &nbsp; |</p>
<p>| B&nbsp; &nbsp; &nbsp; | Micro&nbsp; &nbsp; | 201001&nbsp; &nbsp; &n=
bsp;| 201011&nbsp; &nbsp; |</p>
<p>+--------+----------+------------+-----------+</p>
<p>&nbsp;</p>
<p>I am trying to list up people working for a company called &quot;Micr=
o&quot;.</p>
<p>Some people work for a company multiple times, like user B.</p>
<p>I only need one line per user, displaying only the latest affiliation=
 date.</p>
<p>&nbsp;</p>
<p>For user_id &quot;B&quot;, I could do Select user_id, MAX(start_date)=
, end_date where employer =3D 'Micro',</p>
<p>but that would fail to get record for user_id &quot;A&quot;.</p>
<p>&nbsp;</p>
<p>If multiple records exist, I want to do MAX, but if only a single rec=
ord exists, I don't need MAX.</p>
<p>How do I do that?&nbsp;</p>
<p>From the above data, I would like to see only two lines:</p>
<p>| A&nbsp; &nbsp; &nbsp; | Micro&nbsp; &nbsp; | 201704&nbsp; &nbsp; &n=
bsp;| 201903&nbsp; &nbsp; |</p>
<p>| B&nbsp; &nbsp; &nbsp; | Micro&nbsp; &nbsp; | 201706&nbsp; &nbsp; &n=
bsp;| -&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|</p>
<p>Thank you.</p>
</div>
<br />
-------------------------<br />
Online Storage & Sharing, Online Backup, FTP / Email Server Hosting and =
More.<br />
Drive Headquarters. Top quality services designed for business!<br />
Sign up free at: <a target=3D"_top" href=3D"https://www.drivehq.com/?ref=
ID=3D178949&refEmails=3D&refCode=3D">www.DriveHQ.com</a>.<br />
</blockquote></div></div>
<br />
-------------------------<br />

Online Storage & Sharing, Online Backup, FTP / Email Server Hosting and =
More. <br />
Drive Headquarters. Top quality services designed for business! <br />
Sign up free at: <a target=3D"_top" href=3D"https://www.drivehq.com/?ref=
ID=3D178949&refEmails=3D&refCode=3D">www.DriveHQ.com</a>. =

<br />

</body></html>



------=_NextPart_225943372_9736706_637033521174417679--