sanjshah12
asked on
Order By Date
Hi,
I've been asked a strange request to see if I can use the Order By Date in a query but display the list closet to today first - is this possible?
SELECT id, company, requestor
From tbl_oders
Order By StartDate
Thanks
I've been asked a strange request to see if I can use the Order By Date in a query but display the list closet to today first - is this possible?
SELECT id, company, requestor
From tbl_oders
Order By StartDate
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Greg,
Maybe :)
Depends on just what "closest to today" means, and whether all of the dates are in the future, all in the past, or a mix. For example, theoretically it could be this:
That orders everything by "distance from today", without caring about the "direction" of the difference...
Patrick
Maybe :)
Depends on just what "closest to today" means, and whether all of the dates are in the future, all in the past, or a mix. For example, theoretically it could be this:
SELECT id, company, requestor
From tbl_oders
Order By ABS(DATEDIFF(day, StartDate, GETDATE()))
That orders everything by "distance from today", without caring about the "direction" of the difference...
Patrick
ASKER
matthewspatrick,
yes I think yours solution is what I am looking for, what is the difference between the ABS and DateDiff query?
yes I think yours solution is what I am looking for, what is the difference between the ABS and DateDiff query?
sanjshah12,
First things first.
1) Please define exactly how you want the sorting to be done. What are the rules?
2) Is StartDate always in the future, always in the past, or a mix?
3) Please provide a few sample rows of data, and indicate how those sample data should be sorted
Patrick
First things first.
1) Please define exactly how you want the sorting to be done. What are the rules?
2) Is StartDate always in the future, always in the past, or a mix?
3) Please provide a few sample rows of data, and indicate how those sample data should be sorted
Patrick
ASKER
Patirck,
1. the listing (sorting) should be form displayed from how many days it is away from todays date
2. the StartDate contains a mixture of dates
3. below is some sample data
Hop this makes sense.
1. the listing (sorting) should be form displayed from how many days it is away from todays date
2. the StartDate contains a mixture of dates
3. below is some sample data
Hop this makes sense.
1 user1 31/05/2011 00:00
2 user2 31/05/2011 00:00
3 user3 14/06/2011 00:00
4 user4 14/06/2011 00:00
5 user5 20/07/2011 00:00
6 user6 13/07/2011 00:00
7 user7 14/07/2011 00:00
8 user8 13/07/2011 00:00
9 user9 19/07/2011 00:00
10 user10 04/09/2011 00:00
11 user11 04/09/2011 00:00
12 user12 04/09/2011 00:00
13 user13 04/09/2011 00:00
14 user14 12/09/2011 00:00
15 user15 13/09/2011 00:00
16 user16 13/09/2011 00:00
17 user17 13/09/2011 00:00
18 user18 13/09/2011 00:00
19 user19 13/09/2011 00:00
20 user20 11/09/2011 00:00
21 user21 27/09/2011 00:00
22 user22 27/09/2011 00:00
23 user23 30/09/2011 00:00
24 user24 29/09/2011 00:00
25 user25 29/09/2011 00:00
As matthewspatrick,wrote,
SELECT id, company, requestor
From tbl_oders
Order By DATEDIFF(day, StartDate, GETDATE())
I believe that what you are looking for..
The ABS stands for Absolute, which means it removes the sign from the calculation..
I.e. if you had an order tomorrow, it would give the value -1 and so on..
Applying ABS turns this -1 into 1, giving tomorrow the same position as yesterday.
But i dont believe you have any orders for tomorrow, so you dont need this.
Hope it helps
ASKER
Yes SRM Patrick's answer is correct.
Patrick,
Good point about the direction. I was assuming only past dates, which is probably a bad assumption. Nice solution!
Greg
Good point about the direction. I was assuming only past dates, which is probably a bad assumption. Nice solution!
Greg
SELECT id, company, requestor
From tbl_oders
Order By StartDate DESC
Greg