How to SQL Select by Date.....from now to future...and then past

I need to do a select statement with an ORDER BY date  where it shows the the present to future records first, in order by earliest date, then after all future records are shown, continues to show past records with the very last record being the one furthest in the past.

How can I do this?  I have a StartDate column that contains date data.
arthurh88Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee SavidgeCommented:
Hi,

I would do a union with 2 select statements. So for example:

select * from mytable where dtDate > getdate() order by dtDate asc
union
select * from mytable where dtDate <= getdate() order by dtDate desc

Regards,

Lee
0
dis1931Commented:
Hello arthurh88,

I don't think this is possible...you can't sort half a recordset in a query.  

Let me see if I understand:

You want to have the present date at top and then future dates below it....once the future dates are done you want past records below that going down to the oldest record?

Thanks,

dis1931
0
dis1931Commented:
I think lsavidge is right!!!
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Lee SavidgeCommented:
Hi,

With my code snippet replace dtDate with StartDate for the column name and myTable with whatever table you're selecting from.

Cheers,

Lee
0
ee_rleeCommented:
try this in the order by

ORDER BY (CASE WHEN urdate>=getdate() THEN 0 ELSE 1 END), abs(datediff(day,urdate,getdate()))
0
TextReportCommented:
From what I can make out the date is sorted in the normal Ascending way, its just you wan the future ones ahead on the past ones, in that case the code below will work.

Cheers, Andrew

PS if you want to eliminate the time part of the date then you can use
CONVERT(VarChar(8),dtmDate, 112)
ORDER BY CASE WHEN dtmDate>=getdate() THEN 0 ELSE 1 END
       , dtmDate

Open in new window

0
ursangelCommented:
i think there is a small mistake in 'lsavidge:'s statement.
SQl server will not support order by clause in each statement in the union. Order by can be provided only to the last select statement in the union.

I dont think its possible in a single select. You will have to go for selecting each statements first in the Desc order, insert into a Temp table.
Then select in the ASC order and insert in the same Temp table and finally select from the Temp table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lee SavidgeCommented:
Hi,

ursangel is correct. You can't do an order by on each select when doing a union. You can only order the final recordset.

I tried the order by put forward by ee_rlee and TextReport. These don't provide the results on my tests. However, this does work:

select * into #myTable from myTable where StartDate > getdate() order by StartDate asc
select * into #myTable2 from myTable where StartDate <= getdate() order by StartDate desc
select * from #myTable
union
select * from #myTable2

drop table #myTable
drop table #myTable2

It does use a couple of temp tables which is not the best for performance. Alternatively I would create a stored procedure that creates a table variable and the returns a select from it at the end.

Cheers,

Lee
0
TextReportCommented:
lsavidge, there is no need for temp tables here and ee_rlee's solution does work
My solution doesn't handle the last part of the sort which is the change from ASCENDING to DESCENDING on teh DATE

The example below from NORTHWIND

Cheers, Andrew
SELECT EmployeeID, Hiredate 
FROM employees
ORDER BY CASE WHEN BirthDate>='19600101' THEN 0 ELSE 1 END
       , abs(datediff(day,BirthDate,'19600101'))
 
Returns
7	1960-05-29 00:00:00.000
6	1963-07-02 00:00:00.000
3	1963-08-30 00:00:00.000
9	1966-01-27 00:00:00.000
11	NULL
8	1958-01-09 00:00:00.000
5	1955-03-04 00:00:00.000
2	1952-02-19 00:00:00.000
1	1948-12-08 00:00:00.000
4	1937-09-19 00:00:00.000

Open in new window

0
Lee SavidgeCommented:
Hi,

Strange. I tried the order by statements you both suggested on one of my tables. When doing the queries separately there were 297 records in the first and 4799 in the second which should give rise to 5096 rows returned. When I tried the method you put forward I got 144682 rows which is obviously wrong.

Either way my first attempt was wrong but the second does work on my test table. Not sure where I am going wrong with your example on my test table.

Regards,

Lee
0
TextReportCommented:
Sounds like you have done a cartesian join, all records from table 1 joined to all records in table 2 which is the result you get if you dont do a JOIN or WHERE to link the tables.
Cheers, Andrew
0
Lee SavidgeCommented:
Hi,

I was just testing a select from a single table. No joins or anything. I substituted the data column name you put with the column name in question and substituted the table name for mine and that was about it. I do however think that I made a mistake somewhere. No real time to go and investigate though.

Cheers,

Lee

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.