3

I am writing a statement to pull accounts that are due the soonest. My query currently sorts on month and day as year is irrelevant. Is there anyway to set the current month and day as the top most values? rather than jan 01?

SELECT *
FROM clients
WHERE LiveDate IS NOT NULL
Order by DATEPART(month, LiveDate), DATEPART(day, LiveDate)

Would I just need to split it into two queries one looking for after the current month day, one looking for before, and join them so that they are in the right order

EXAMPLE:

date founded || Annual function
01/01/2011   || beach outing
11/03/2010   || family day
23/03/2009   || Movies
05/04/2000   || Girls night out
10/05/2005   || Cricket function
29/07/2011   || candle lit formal dining
30/07/2008   || childrens day
04/08/2005   || board games day
03/012/2006  || pizza night
20/012/2001  || camping trip

As this is an annual outing the year does not matter. I want to pull them in order of the event closest to now through to the end of the year, then from the start of the year through to today.

so the results would be in this order based on a current day of: 23/06/2011

date founded || Annual function 
29/07/2011   || candle lit formal dining 
30/07/2008   || childrens day 
04/08/2005   || board games day 
03/012/2006  || pizza night 
20/012/2001  || camping trip              _ _ _ _ _ _ _ New year _ _ _ _ _  
01/01/2011   || beach outing 
11/03/2010   || family day 
23/03/2009   || Movies 
05/04/2000   || Girls night out 
10/05/2005   || Cricket function
Rumpleteaser
  • 4,142
  • 6
  • 39
  • 52
  • Well, your own proposal is probably is good enough. But you need to use UNION, not JOIN for 2 queries. – Petr Abdulin May 10 '11 at 05:19
  • I'm not sure I understand completely what you mean. You want to return clients after the current date, ordered by `LiveDate`? Because the comment below your question suggests that you also want to get clients before the current date. What happens when the current date is Dec 31? – Ronald Wildenberg May 10 '11 at 05:46
  • I want to return all clients in order from today through to yesterday next year. I think I will just use a union as Petr corrected me. It works. – Rumpleteaser May 10 '11 at 20:53
  • no it doesn't because it orders by at the end. not within each select. I might just need to run two queries. – Rumpleteaser May 10 '11 at 21:06

2 Answers2

9
SELECT *
FROM clients
WHERE LiveDate IS NOT NULL
ORDER BY ABS(DATEDIFF(day, LiveDate, GETDATE())

UPDATE

Sorry, didn't get it at first that only the proximity to the current day and month, but not year, should be taken into account when sorting.

So maybe like this:

SELECT *
FROM clients
WHERE LiveDate IS NOT NULL
ORDER BY
  ABS(
    DATEDIFF(
      day,
      DATEADD(year, DATEDIFF(year, LiveDate, GETDATE()), LiveDate),
      GETDATE()
    )
  )

?

UPDATE 2

Based on the examples provided, this should do the job:

…
ORDER BY
  (MONTH(LiveDate) - MONTH(GETDATE()) + 12) % 12,
  DATEADD(year, YEAR(GETDATE()) - YEAR(LiveDate), LiveDate),
  YEAR(LiveDate)
Andriy M
  • 76,112
  • 17
  • 94
  • 154
  • this just orders by the date closest to today. changing day to month doesn't help either. Its still sorting taking year into account. I can see what you were trying to do though. – Rumpleteaser May 10 '11 at 20:52
  • @inKit: I think I've got it now. Please have a look at my update. – Andriy M May 11 '11 at 04:37
  • No sorry, this is ordering it by the date that is closest yes. but its looking in the past and the future. so if yesterday is closer than two day in the future. even though two days in the future is closer than waiting a year for yesterday. – Rumpleteaser May 19 '11 at 22:14
  • @inKit: Sorry, but now I'm confused entirely! :) It now seems to me that my former solution should suit you. But as you've turned down both of them, I would really like you to show several *various* examples that convey your idea precisely. Even though your question looks simple, it's turned out difficult for me to understand. So maybe the examples can help. (Add them to your question, please. Maybe someone other could come up with the proper solution sooner than I.) – Andriy M May 20 '11 at 04:13
  • @inKit: Thank you. I've updated my answer. I must confess, though, that the solution has already [been accepted](http://stackoverflow.com/questions/6195818/sort-by-date-in-sql/6196465#6196465) once. But because your question was asked earlier, I thought it would be all right if I incorporated the solution into this answer rather than just posting the link. (The author has no objections.) – Andriy M Jun 22 '11 at 21:48
  • My apologies, I did ask the question earlier though, so I did check to see if the question had been answered before initially posting it. – Rumpleteaser Jun 22 '11 at 22:03
  • @inKit: It's all right. Actually it was I who was sorry for having to 'steal' the already accepted solution for using it in this answer to your question. I stated my reasons for doing so, though, hoping you wouldn't mind. And seems like you don't, thank you very much. :) – Andriy M Jun 22 '11 at 22:12
0

If you dont want historic results, just add this:

WHERE LiveDate >= GETDATE()...
Artem Kulikov
  • 2,250
  • 19
  • 32
Ketil
  • 1