CODEWITHSUNDEEP
sqlsql-servert-sqlpagination
user460114
user460114

Reputation: 1848

SQL Server : get range of records depending on compeletion of match fixture

We have a Fixtures jQuery carousel roller, which displays completed and upcoming fixtures. See http://www.allblacks.com/ above the News Roller. Currently this is hardcoded.

We want a maximum of 5 records to be scrollable. The roller should display the most recently completed fixture, with the forward arrow displaying the next two upcoming fixtures and the back arrow displaying the two most recently completed fixtures. This automatically happens via the roller js, i.e. if five records are returned, the roller will display the central record, i.e. record # 3.

So, how would the query be written to extract five records with the THIRD record being the most recently completed fixture?

The following is the database structure. Completion of a fixture is determined by eventDateTime.

Table events:

eventID   Event                      eventDateTime
-------------------------------------------------
2016      Australia v All Blacks     2012-12-11 22:00:00.000
2015      South Africa v Australia   2012-12-04 03:00:00.000
2014      South Africa v Australia   2012-11-28 03:00:00.000
2013      South Africa v All Blacks  2012-11-22 03:00:00.000
2012      All Blacks v Australia     2012-11-07 19:35:00.000
2011      Australia v All Blacks     2012-10-31 22:00:00.000
2010      Australia v South Africa   2012-10-24 22:00:00.000
....

EDIT

So, as eventID 2012 is the most recently completed match as at current date (18 nov 2012), I want the records returned to be as follows, with eventID 2012 being in the middle, i.e. the third record:

eventID   Event                      eventDateTime
-------------------------------------------------
2010      Australia v South Africa   2012-10-24 22:00:00.000  
2011      Australia v All Blacks     2012-10-31 22:00:00.000  
2012      All Blacks v Australia     2012-11-07 19:35:00.000
2013      South Africa v All Blacks  2012-11-22 03:00:00.000
2014      South Africa v Australia   2012-11-28 03:00:00.000

Upvotes: 1

Views: 205

Answers (2)

fthiella
fthiella

Reputation: 49079

You probably need just this:

select eventID, Event, eventDateTime
from
  (select top 3 *
   from events
   where DATEADD(dd, DATEDIFF(dd,0,eventDateTime), 0) <= getdate()
   order by eventDateTime) first
union all
select eventID, Event, eventDateTime
from
  (select top 2 *
   from events
   where DATEADD(dd, DATEDIFF(dd,0,eventDateTime), 0) > getdate()
   order by eventDateTime) second

Upvotes: 1

bummi
bummi

Reputation: 27377

Select * from
(
select top 3 * from #event where eventDateTime<=(getdate()) order by eventDateTime desc
UNION
select top 2 * from #event where eventDateTime>(getdate())
) a
order by eventDateTime

an addition could be

Select * from
(
select top 3 *,1 - row_number() over (order by GetDate() - eventDateTime)  as Sequence from #event where eventDateTime<=(getdate()) order by eventDateTime desc
UNION
select top 2 *, row_number() over (order by eventDateTime - GetDate() ) from #event where eventDateTime>(getdate())
) a
order by  eventDateTime

Upvotes: 0

Related Questions