I have a table ‘Device’ with an installed date and a customer:
I need following results
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!
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.
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