MariaDB/Mysql SELECT from – to with Subquery

I have a table ‘Device’ with an installed date and a customer:

Id	DeviceName	Installed	Customer
1	A	2021-10-10	1
2	B	2021-10-10	1
3	A	2021-10-20	2
4	A	2021-10-30	3

I need following results

DeviceName	InstalledFrom	InstalledTo	Customer
A	2021-10-10	2021-10-20	1
B	2021-10-10	NULL	1
A	2021-10-20	2021-10-30	2
A	2021-10-30	NULL	3

Tried it with following SQL

select a.DeviceName, a.Installed as InstalledFrom, b.installed as InstalledTo, a.customer 
from Device a 
left outer join Device b on ( b.DeviceName = ( SELECT DeviceName 
                                               FROM Device 
                                               where DeviceName = a.DeviceName 
                                                 AND Installed > a.Installed 
                                               order by installed limit 1) )

but this does not give the desired result.

Thanks for any help!

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Test this:

SELECT t1.DeviceName,
       t1.Installed InstalledFrom,
       t2.Installed InstalledTo,
       t1.Customer
FROM Device t1
LEFT JOIN Device t2 ON t1.DeviceName = t2.DeviceName
                   AND t1.Installed < t2.Installed
WHERE NOT EXISTS ( SELECT NULL
                   FROM Device t3
                   WHERE t1.DeviceName = t3.DeviceName
                   AND t1.Installed < t3.Installed
                   AND t3.Installed < t2.Installed )

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating