Search code examples
mysqlsqldatetimesubquerywhere-clause

SQL select inside select using a datetime column as reference

I have a table that looks like the one below:

╔═══════════╦═════════╦═════════════╦═══════════════╦═════════════════════╦═════════════════════╗
║ id_object ║ Name    ║ Activity    ║ Object_Prefix ║ Start_DateTime      ║ End_DateTime        ║
╠═══════════╬═════════╬═════════════╬═══════════════╬═════════════════════╬═════════════════════╣
║ 213       ║ Junior  ║ Fixing      ║ G35           ║ 06-11-2020 09:35:06 ║ 06-11-2020 16:39:34 ║
╠═══════════╬═════════╬═════════════╬═══════════════╬═════════════════════╬═════════════════════╣
║ 213       ║ Junior  ║ End_turn    ║               ║ 06-11-2020 17:01:25 ║                     ║
╠═══════════╬═════════╬═════════════╬═══════════════╬═════════════════════╬═════════════════════╣
║ 213       ║ Richard ║ Auxiliating ║ G35           ║ 06-11-2020 09:35:06 ║ 06-11-2020 16:39:34 ║
╠═══════════╬═════════╬═════════════╬═══════════════╬═════════════════════╬═════════════════════╣
║ 213       ║ Richard ║ End_turn    ║               ║ 06-11-2020 17:04:07 ║                     ║
╠═══════════╬═════════╬═════════════╬═══════════════╬═════════════════════╬═════════════════════╣
║ 456       ║ Philip  ║ Fixing      ║ G08           ║ 02-11-2020 13:35:09 ║ 02:11:2020 21:58:59 ║
╠═══════════╬═════════╬═════════════╬═══════════════╬═════════════════════╬═════════════════════╣
║ 456       ║ Philip  ║ End_turn    ║               ║ 02-11-2020 22:37:51 ║                     ║
╠═══════════╬═════════╬═════════════╬═══════════════╬═════════════════════╬═════════════════════╣
║ 102       ║ Julia   ║ Auxiliating ║ N901          ║ 31-10-2020 20:01:30 ║ 01-11-2020 08:09:41 ║
╠═══════════╬═════════╬═════════════╬═══════════════╬═════════════════════╬═════════════════════╣
║ 102       ║ Julia   ║ End_turn    ║               ║ 01-11-2020 08:13:45 ║                     ║
╚═══════════╩═════════╩═════════════╩═══════════════╩═════════════════════╩═════════════════════╝

I've been trying to select the End turn Times as a new column, although so far what i tried with no success was:

 select  
  t1.id,
  t1.name,
  t1.activity,
  DATE_FORMAT( t1.Start_DateTime, "%d/%m-%Y %H:%i:%s") as Start,
  (Select DATE_FORMAT(t1.Start_DateTime, "%d/%m-%Y %H:%i:%s")
    from Activities t2 
     WHERE t2.Activities = "End_turn" AND t2.id_object = t1.id_object) as end_activity
  FROM 
   Activities t1 
 WHERE 
 t1.Object_Prefix != ""
GROUP BY
t.Start_DateTime

The result i was expecting was something like this: ExpecteResult

but the column end_turn is always coming filled with null values. I didnt figured it out where i missing it.

Solution
    1. there are inconsistencies between your example data and your query.
    select  
  t1.id, <--- there is no column named as `id`. Instead it was `id_object`
  t1.name,
  t1.activity,
  DATE_FORMAT( t1.Start_DateTime, "%d/%m-%Y %H:%i:%s") as Start,
  (Select DATE_FORMAT(t1.Start_DateTime, "%d/%m-%Y %H:%i:%s")
    from Activities t2 
     WHERE t2.Activities = "End_turn" <---- there is no column named as `activities`. Instead it was `activity`.
   AND t2.id_object = t1.id_object) as end_activity
  FROM 
   Activities t1 
 WHERE 
 t1.Object_Prefix != ""
GROUP BY
t1.Start_DateTime
    1. I've tried your query with the example data you've given and I got:

    Error Code: 1242 Subquery returns more than 1 row

    1. The GROUP BY is on the t1.Start_DateTime column doesn't make sense since it seem to clearly show in your expected results that you want them to group them by id_product and name.

    2. Your date format in Start_DateTime and End_DateTime columns is dd-mm-yyyy whereas the standard MySQL date for date related data type is yyyy-mm-dd.

    Lastly, if the Start_DateTime and End_DateTime columns data type is date/datetime, I suggest this query:

    SELECT id_object, `Name`, 
       SUBSTRING_INDEX(GROUP_CONCAT(Activity ORDER BY Start_DateTime ASC),',',1) ac, 
       MAX(CASE WHEN Object_Prefix <> '' THEN Object_Prefix END) op,
       MIN(CASE WHEN activity <> 'End_turn' THEN Start_DateTime END) sdt,
       MAX(CASE WHEN activity = 'End_turn' THEN Start_DateTime END) edt
FROM activities
GROUP BY id_object, `Name`;

    And you can see the demo here: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=de2af89c39e6636cfb45ee179cfb1461