0

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: 10;1000;2018-10-02;NULL.

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.

Further Information:

  • 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?

Solution

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.

Steffi

10
  • You only have 3 rows for client_id = 20! So, for client_id = 10, you only want the first arrival date and NULL for departure date since there isn't one for the last record? Do the previous arrival and departure dates have to be consecutive or can they be anything? I presume that arrival must be less than departure unless departure is NULL? I also presume that you can't have a NULL arrival date? Please put all information back into the question and not as an answer to my comment!. You can let me know by putting the @ symbol followed by my handle in a comment below! Jan 14 at 15:49
  • @Vérace - get VACCINATED NOW - lucky me, you are around again :) I have updated my question with the answers to yours. I tried @... last time also but it got cut every time. Hope this time it will work. Thanks.
    – Stefanie
    Jan 14 at 17:34
  • "archive" or "achieve"??
    – Rick James
    Jan 14 at 22:05
  • Please provide a case with multiple rows being output, but with some of the dates being different. (I am unclear on how the goals interact.)
    – Rick James
    Jan 14 at 22:08
  • @Stefanie - take a look here - trying to reply! Look in particular at what happens if you change the sql_mode by adding ONLY_FULL_GROUP_BY - MySQL (and derivatives) incredibly will return rubbish if this isn't set - only became default in MySQL 8. Even more incredibly MariaDB 10.6 doesn't have this as its default - make it your default! See what works with PostgreSQL - normally a guide to good behaviour! I've given up working with MySQL and its derivatives and trying to figure out its bugs! Jan 15 at 12:23
0

What code do you have so far? I assume you are using GROUP BY case_id, client_id.

To discover whether any departure is NULL (across a group) is with COUNT(*) = COUNT(departure). This is because COUNT(*) counts all rows; the other counts only rows where departure IS NOT NULL. So, this is how you might make the departure expression:

IF(COUNT(departure) = COUNT(*), MAX(departure), NULL)

More

Your proposed solution has the "only_full_group_by" problem since it asks for multiple things. Change ID to GROUP_CONCAT(ID).

Also, it would be a slight optimization to change the ORDER BY to match the GROUP BY.

6
  • Thank you very much for your time and answer. Based on your answer, I extended my question with a solution part. Does this make sense to you? Thanks again. Apologizes - I cannot manage it to copy your name using at Rick James at the beginning of that message. It is cut off always when saved. @Rick James
    – Stefanie
    Jan 15 at 7:51
  • @Stefanie - (StackExchange removes spaces when generating "@" handles.)
    – Rick James
    Jan 15 at 16:46
  • @Stefanie - I added some more to my Answer.
    – Rick James
    Jan 15 at 16:51
  • Thank you very much for the tips and your review of my solution. I changed it according to your advise and learned again. @Rick James
    – Stefanie
    Jan 16 at 18:27
  • Just trying to put notification in the right format at the right place and omitting spaces. Still it does not work. I am using tor browser with restrictions to java script or I am too dumb. No way, (at)Rick does not work, (at)RickJames (one word) doesn't. Trying (at)rickjames also... nothing is working (all tried at the beginning of the comment. I give up, hopefully (at)Rick James at the end of my previous comment did reach you.
    – Stefanie
    Jan 16 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.