Solved

MYSQL Select to return most recent date and next date in same result row

Posted on 2007-03-30
6
1,742 Views
Last Modified: 2013-12-13
I have a "Roster" table which has two fields - the userid and the date of their shift.  I would like a query that returns a single row for each user, with their most recent past shift and their next future shift.  I can get one or the other (ie either past or future) but not both.  I'm sure there's some simple answer.

eg if today's date is 2007-03-30
Userid         Date
0001           2007-03-02
0002           2007-03-04
0003           2007-03-05
0002           2007-03-06
0001           2007-03-07
0003           2007-04-10
0002           2007-04-11
0002           2007-04-12
0003           2007-04-13
0004           2007-04-14

would return
userid      last-shift         next-shift
0001        2007-03-07    Null
0002        2007-03-06    2007-04-11
0003        2007-03-05    2007-04-10
0004        Null                 2007-04-14

Many thanks in advance
PS I looked at Q22445165 but there don't seem to be the right number of parentheses (brackets) so I couldn't work out how to apply that solution to my problem.
0
Comment
Question by:David Linker
  • 3
  • 2
6 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18823183
select   t1.userid, max(t1.date) as last_shift, min(t2.date) as next_shift
from  mytable t1, mytable t2
where t1.userid = t2.userid
and t1.date < now
and t2.date > now

maybe i'm wrong with the now function - you need the function that returns todays date

momi
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18823211
that should be

select   t1.userid, max(t1.date) as last_shift, min(t2.date) as next_shift
from  mytable t1, mytable t2
where t1.userid = t2.userid
and t1.date < now()
and t2.date > now()
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 18823238
i'm sorry
the query i entered may not be correct,
try this one :

select   t1.userid, t1.date  as last_shift,  t2.date  as next_shift
from  
(select userid, max(date) from mytable where date < now() group by userid)  t1,
(select userid, min(date)  from mytable where date > now() group by userid) t2
where t1.userid = t2.userid

that would work
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 4

Expert Comment

by:mukhtar2t
ID: 18824329
I create table roster,inserted one userid with 2 dates before and after now and executed this query
SELECT l.userid 'User ID', Max( l.date ) AS 'Last Shift', Min( n.date ) AS 'Next Shift'
FROM roster l
INNER JOIN roster n ON l.userid = n.userid
AND n.date > now( )
WHERE l.date <= now( )
GROUP BY l.userid

and this is the result
Showing rows 0 - 0 (1 total, Query took 0.0530 sec)
User ID  Last Shift  Next Shift  
1 2007-03-29 2007-04-01
0
 
LVL 4

Author Comment

by:David Linker
ID: 18827729
I'm giving the points to momi_sabaq as he provided an answer first - and his query runs faster (0.0115 vs 0.0374 on my real data) than mukhtar2t's solution.  However, I had to make a change (moved the "AS" statements into each sub query) to get the query to work so I only gave a B rating:
SELECT t1.userid, last_shift, next_shift
FROM (

SELECT userid, max( date ) AS last_shift
FROM roster
WHERE date <= now( )
GROUP BY userid)t1, (

SELECT userid, min( date ) AS next_shift
FROM roster
WHERE date > now( )
GROUP BY userid)t2,
WHERE t1.userid = t2.userid
0
 
LVL 4

Author Comment

by:David Linker
ID: 18827734
Thanks to both of you for the quick respons and the help.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question