Solved

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

Posted on 2007-03-30
6
1,727 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to dynamically set the form action using jQuery.
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.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now