I am working with mariadb server version 10.6.4.
This is my table definition:
CREATE TABLE `tmp_dba` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `case_id` bigint(20) DEFAULT NULL, `client_id` bigint(20) NOT NULL, `arrival` date DEFAULT NULL, `departure` date DEFAULT NULL, PRIMARY KEY (`ID`) )
Here is some example data:
INSERT INTO `tmp_dba` VALUES (1,10,1000,'2018-10-02','2019-04-25'), (2,10,1000,'2019-04-26','2019-05-01'), (3,10,1000,'2019-05-02',NULL), (4,20,2000,'2018-11-21',NULL), (5,20,2001,'2018-11-21',NULL), (6,20,2002,'2018-11-21',NULL), (7,30,3000,'2019-03-04','2022-01-01'), (8,30,3001,'2019-03-04','2022-01-01'), (9,30,3002,'2019-03-04','2022-01-01'), (10,30,3003,'2019-03-04','2022-01-01'), (11,30,3004,'2019-03-04','2022-01-01');
What I would like to achieve is determine the MIN(arrival) of each group of case_id and client_id and in case departure is not null MAX(departure) should be displayed otherwise null.
I want to end up with just one row per client providing the the data mentioned above.
For example for case_id = 10 I want to see 1 row like that:
For case_id = 20 the result should be 4 rows, because of 4 different combinations of case_id and client_id.
For case_id = 30 there should be 5 rows shown, because of 5 different combinations of case_id and client_id.
I must be doing something wrong when using group by.
- Data is consecutive in the meaning that a new record entered will have a higher ID than the old one. Also a new record will be - relative to the one before - in the future, always. This does not mean that the next record always would have an arrival the day after last departure.
- A new record in the table for the same customer will always set the departure date of the previous record. The previous record departure in that case can not be null.
- When there is no departure and the client is "in" still, departure will be null always.
- Departure must be less than arrival unless departure is null.
- Arrival can not be null.
Is this doable?
SELECT -- First solution, but wrong, GROUP_CONCAT(ID) avoids error below -- ID, case_id, client_id, <-- only_full_group_by - error GROUP_CONCAT(ID), case_id, client_id, MIN(arrival) AS arrival, IF(COUNT(departure) = COUNT(*), MAX(departure), NULL) AS departure FROM tmp_dba GROUP BY case_id, client_id ORDER BY case_id, client_id
Thank you very much for your help.