RE:Re: A complex SQL query - Mailing list pgsql-sql

From jj08
Subject RE:Re: A complex SQL query
Date
Msg-id 2259433721000000009736706@www
Whole thread Raw
In response to A complex SQL query  ("jj08" <jj08@drivehq.com>)
List pgsql-sql

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 something 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
from
    employment
where
    employer = 'Micro'
group by
    user_id
;
On 6 Sep 2019, 14:31 +0200, jj08 , 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 "Micro".

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

I only need one line per user, displaying only the latest affiliation date.

 

For user_id "B", I could do Select user_id, MAX(start_date), end_date where employer = 'Micro',

but that would fail to get record for user_id "A".

 

If multiple records exist, I want to do MAX, but if only a single record 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 up free at: www.DriveHQ.com.

pgsql-sql by date:

Previous
From: Jānis Pūris
Date:
Subject: Re: A complex SQL query
Next
From: Karen Goh
Date:
Subject: How do I enabled Windows 10 to be able to run PSQL etc