Solved

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

Posted on 2007-03-30
6
1,735 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

770 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