user2316219 user2316219 - 4 months ago 7
SQL Question

Cant combine two selects together

I have a table containing timestamps and the direction(In/Out)

CASE 1:in, 2016-07-06 08:00:00, I
CASE 1:out, 2016-07-06 17:00:00, O
CASE 2:in, 2016-07-12 08:00:00, I
CASE 2:out, 2016-07-13 17:00:00, O
CASE 3:in, 2016-07-14 08:00:00, I
CASE 3:out, 2016-07-18 17:00:00, O


I would like to create a select that shows me all rows that have been In at 03am every morning. It should also show me all days of the months it was in.

As you see from the example, Case 1 should not be returned because it was not In at 03:00 am.

So far i have created a select statement to check for a particular day:

Select t.* from
(SELECT * FROM movement
where scan_date < '2016-07-06 03:00:00'
order by scan_date desc limit 1) t
WHERE t.direction='I';


So far so good. I would now like to check that against the whole month.
For simplicity i have created a table with all 3am entiries for the whole month:

Select day from month


returns

2017-07-01 03:00:00
...and so on...

For some reason i am not able to combine these two selects.
I cant use the second table in the < clause because it tells me that it returns more then one row.

The problem sounds simple but didnt find a solution so far.
If anybody has any ideas i would be happy to hear it.

Answer

Given your updated problem statement, the following should get you started:

SELECT
  check_time
  , CAST(LEFT(checktime_direction, 19) AS DATETIME) scan_time
  , RIGHT(checktime_direction, 1) direction
FROM (
  SELECT
    CP.p check_time
    , (SELECT CONCAT(DATE_FORMAT(scan_date, '%Y-%m-%d %H:%i:%s'), direction)
       FROM Movement
       WHERE scan_date = (SELECT MAX(scan_date)
                          FROM Movement
                          WHERE scan_date <= CP.p
                         )
      ) checktime_direction
  FROM Checkpoint CP
) T
WHERE RIGHT(checktime_direction, 1) = 'I'
ORDER BY check_time
;

For your sample data it returns:

|             check_time |              scan_time | direction |
|------------------------|------------------------|-----------|
| July, 13 2016 03:00:00 | July, 12 2016 08:00:00 |         I |
| July, 15 2016 03:00:00 | July, 14 2016 08:00:00 |         I |
| July, 16 2016 03:00:00 | July, 14 2016 08:00:00 |         I |
| July, 17 2016 03:00:00 | July, 14 2016 08:00:00 |         I |
| July, 18 2016 03:00:00 | July, 14 2016 08:00:00 |         I |

See it in action: SQL Fiddle.
NB: The helper table has been called Checkpoint rather than month as the latter is a MySQL reserved word.

How does it work?

The overall solution approach is to use a correlated (or "synchronized") subquery.
Generally speaking, the inner query pulls up the most recent direction change for the respective outer checkpoint. This (intermediate) result set in turn is filtered to retain just the "I" records.

In detail:

SELECT
  CP.p check_time
  [...]
FROM Checkpoint CP

is the outer query, setting the scene. (In the context of the mentioned synch, that is. To get the wanted result, this "outer" query is wrapped itself. - But this involves no correlating, just filtering.)

    SELECT MAX(scan_date)
    FROM Movement
    WHERE scan_date <= CP.p

is linked to it via CP.p.
As you asked for both scan_date and direction to be pulled up for your final result, another level is introduced in between the two:

  SELECT CONCAT(DATE_FORMAT(scan_date, '%Y-%m-%d %H:%i:%s'), direction)
  FROM Movement
  WHERE scan_date = ([...]
                    )

This query gets two columns - but its output needs to fit into a single column in the outer query. Therefore, the two columns are combined into one - in a way ensuring correct disassembly later on.

From here, all that's left to do, is to get rid of the "O" records (and put the remaining one's into the correct order). Well - the earlier combined into one column scan_time and direction are waiting to get separated (and the former to be returned to DATETIME):

SELECT
  check_time
  , CAST(LEFT(checktime_direction, 19) AS DATETIME) scan_time
  , RIGHT(checktime_direction, 1) direction
FROM (
  [...]
) T
WHERE RIGHT(checktime_direction, 1) = 'I'
ORDER BY check_time
;

You might want to use the Fiddle, break apart the query, play with the pieces, and watch the show...
Mind the pair of round brackets / parentheses surrounding each sub-select. These are required by the SQL syntax.

Please comment if and as this requires adjustment / further detail.