Combining two columns into one for only required data

Question

Can I get some data fro each column and then store it in one result column using mysql queries?

Table
Truck Number, Depart time, Arrive time
0001,'2016-10-05 07:15:00','2016-10-05 10:10:00' 
0002,'2016-10-05 08:15:00','2016-10-05 09:30:00'
0003,'2016-10-04 19:15:00','2016-10-05 08:45:00'

I only want the trucks that leave or arrive from '2016-10-05 08:00:00' to '2016-10-05 08:50:00'

I want the output to be

Truck, Time
0002  '2016-10-05 08:15:00'
0003  '2016-10-05 08:45:00'

And if possible have the column the time came from as well.

That will not combine the times at the select stage as I need to have only truck number and time. I am only wanting the depart or arrive time not both. The getting the times into three columns is simple and I can use between. — Richard Lawton, Oct 08 '16 at 06:43
The time column output is not clearly defined. We are left to imagine if both times on a row qualify — Drew, Oct 08 '16 at 06:58
Strawberry I have not tried anything right now, as I can not see the way to get just the correct part of that row into the result row. Drew is you look at the data in the table it is easy to see that only the required part of that row is displayed. That is what I am after. Just if that truck is there and the depart or arrive time that falls within the bounds. — Richard Lawton, Oct 08 '16 at 07:09
Richard (oh make sure to ping us with an @ sign directly next to our name else we get no inbox notification). But I write code for edge conditions and that is not described or defined in words. Just guesses for the datetimes. Just because a measly set of two sample rows works or imagined for a rule not written, doesn't mean other data for edge will work — Drew, Oct 08 '16 at 07:11
@Drew I want it to work like that, so I don't understand what you are talking about sorry. — Richard Lawton, Oct 08 '16 at 07:29
Truck 002 and 003 are two entities. If it was only for a truck, then I would say, thar's fine to do. Why are you trying to merge two different entities? — AT-2017, Oct 08 '16 at 07:55
On the face of it this appears to pretty straightforward - but I don't understand how truck 3 appears in the result set since neither of the times appear in the range '2016-10-05 08:00:00' to '2016-10-05 08:30:00'. It is also not clear to me how you decide which to select if both times fall within the date time range. — P.Salmon, Oct 08 '16 at 08:06
@P.Salmon sorry I fixed so it would be there, my typo error. That is what I want, if one of the two times is right, select that time and display it with the truck number. — Richard Lawton, Oct 08 '16 at 08:18
@Jayvee yes that got it, thanks...no trying to sort out the order by for the entire lot and not each one separately.... — Richard Lawton, Oct 08 '16 at 09:06
@RichardLawton what order do you need, Truck/Time ascending? I added this order to my answer but it can be tweaked as required — Jayvee, Oct 08 '16 at 09:38

Jayvee · Answer 1 · 2016-10-08T09:41:42.917

0

Would a UNION work for you?, like this:

select truck, departtime as time , 'Depart' as direction
where departtime between '2016-10-05 08:00:00' and '2016-10-05 08:50:00' 

UNION 

select truck, arrivetime as time , 'Arrive' as direction
where arrivetime between '2016-10-05 08:00:00' and '2016-10-05 08:50:00'

ORDER BY TRUCK, TIME

edited Oct 08 '16 at 09:41

answered Oct 08 '16 at 08:30

Jayvee

10,670
3
29
40

score 0 · Answer 2 · answered Oct 08 '16 at 09:30

SELECT  T.TRUCKNUMBER,
            CASE WHEN DEPART BETWEEN '2016-10-05 08:00:00' AND '2016-10-05 08:50:00' THEN DEPART
            ELSE ARRIVE 
            END AS TIME ,
            CASE WHEN DEPART BETWEEN '2016-10-05 08:00:00' AND '2016-10-05 08:50:00' THEN 'DEPART'
            ELSE 'ARRIVE' 
            END AS SourceColumn 

FROM    T   WHERE DEPART BETWEEN '2016-10-05 08:00:00' AND '2016-10-05 08:50:00' OR
                    ARRIVE BETWEEN '2016-10-05 08:00:00' AND '2016-10-05 08:50:00'

result

+-------------+---------------------+--------------+
| TRUCKNUMBER | TIME                | SourceColumn |
+-------------+---------------------+--------------+
|           2 | 2016-10-05 08:15:00 | DEPART       |
|           3 | 2016-10-05 08:45:00 | ARRIVE       |
+-------------+---------------------+--------------+

Combining two columns into one for only required data

2 Answers2