• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

Top 3 records below current date and all the rest.

Hi Experts,

Given a MSSQL 2000 table:

SCHEDULE:

SCHEDULEKEY  INT
SCHEDULEDATE DATETIME
PATIENTKEY   INT

SCHEDULEKEY SCHEDULEDATE             PATIENTKEY
==================================================
2385               2008-03-02 00:00:00.000      1
2380               2008-02-01 00:00:00.000      2
2382               2006-03-02 00:00:00.000      1
2402               2006-02-08 07:30:00.000      3
2396               2006-02-08 07:30:00.000      4
2401               2006-02-08 07:30:00.000      5
565                 2006-02-08 07:30:00.000      6
2400               2006-02-08 07:30:00.000      7
2386               2005-04-30 00:00:00.000      8
2405               2005-04-29 00:00:00.000      9
2403               2005-04-29 00:00:00.000      10
2404               2005-04-28 00:00:00.000      11
2409               2005-04-28 00:00:00.000      11
2406               2005-04-27 00:00:00.000      60
2407               2005-04-27 00:00:00.000      70
2408               2005-04-27 00:00:00.000      76
2410               2005-04-27 00:00:00.000      1
===================================================

if currentdate (system date) is 04/30/05,
Please show me a query that will get the top 3 records whose
scheduledate is below the currentdate and then all
the rest of the records those scheduledate is >= to the current date

Based on the sample records above, the query result will be
something like this:

sorted by scheduledate in descending order

SCHEDULEKEY SCHEDULEDATE             PATIENTKEY
==================================================
2385                2008-03-02 00:00:00.000      1
2380                2008-02-01 00:00:00.000      2
2382                2006-03-02 00:00:00.000      1
2402                2006-02-08 07:30:00.000      3
2396                2006-02-08 07:30:00.000      4
2401                2006-02-08 07:30:00.000      5
565                  2006-02-08 07:30:00.000      6
2400                2006-02-08 07:30:00.000      7
2386                2005-04-30 00:00:00.000      8
2405                2005-04-29 00:00:00.000      9
2403                2005-04-29 00:00:00.000      10
2404                2005-04-28 00:00:00.000      11
===================================================

Please help,

yorge

0
yorge
Asked:
yorge
2 Solutions
 
zupi5Commented:
hi , try this

select top 3 * from SCHEDULE where SCHEDULEDATE < '2005-04-30'
UNION
SELECT * FROM SCHEDULE WHERE SCHEDULEDATE >= '2005-04-30'
ORDER BY SCHEDULEDATE
0
 
anthonywjones66Commented:
If you only want to work with the current date and are not interested in the current time then:-

DECLARE @now datetime
SET @now = CONVERT(datetime, FLOOR(CONVERT(float(24), GETDATE())) )

SELECT TOP 3 * FROM SCHEDULE WHERE SCHEDULEDATE <  @now
UNION ALL
SELECT * FROM SCHEDULE WHERE SCHEDULEDATE >= @now
ORDER BY SCHEDULEDATE DESC

else if current time to be acounted for then:-

DECLARE @now datetime
SET @now = GETDATE()

SELECT TOP 3 * FROM SCHEDULE WHERE SCHEDULEDATE <  @now
UNION ALL
SELECT * FROM SCHEDULE WHERE SCHEDULEDATE >= @now
ORDER BY SCHEDULEDATE DESC

Anthony.

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now