Thread: Funny date-sorting task
Hi, I've got a stack of tasks to show in a list. Every task has a timestamp X that may be NULL or a date. It contains the date when this tasks should be done. Sometimes it has date and the time-part, too. The list should be like this: 1) X sometime today should come first in ascending time order. 2) X in the past should show up after (1) in descending order so that not so long back dates come first 3) X = NULL 4) X sometime in the future The point is, I like to do the skeduled tasks for today as planned. = (1) Those allready lost appointments should not defer those today that are still in time but I like to get them after the today-tasks in an order where there is a chance that a nearer lost appointment might be still rescued even though it's a bit late. The dates longer back might be lost for good anyway so they can wait a bit longer. = (2) Provided I get through (1) and (2) I'd venture the unknown where there wasn't a date until now. = (3) Well, and future dates will be minded when their time is there. = (4) For now I do this by having a sorting-column in the tasks-table that gets updated in 4 steps where my application has to select every group (1) - (4) then sequentially walk through the recordset and update the sort-order-column by a counter. Later I sort ascending by the sort-order-column. It kind of works but I consider it ugly. Could you provide a clever solution?
At 07:40 PM 5/12/07, Andreas wrote: >I've got a stack of tasks to show in a list. >Every task has a timestamp X that may be NULL or a date. It contains the >date when this tasks should be done. >Sometimes it has date and the time-part, too. > > >The list should be like this: >1) X sometime today should come first in ascending time order. >2) X in the past should show up after (1) in descending order so that >not so long back dates come first >3) X = NULL >4) X sometime in the future > >Could you provide a clever solution? ORDER BY CASE WHEN X=today THEN 1 ELSE CASE WHEN X<today THEN 2 ELSE CASE WHEN X IS NULL THEN 3 ELSE CASE WHEN X>today THEN 4 ELSE 5 END END END END
On 5/12/07, Frank Bax <fbax@sympatico.ca> wrote: > At 07:40 PM 5/12/07, Andreas wrote: > >I've got a stack of tasks to show in a list. > >Every task has a timestamp X that may be NULL or a date. It contains the > >date when this tasks should be done. > >Sometimes it has date and the time-part, too. > > > > > >The list should be like this: > >1) X sometime today should come first in ascending time order. > >2) X in the past should show up after (1) in descending order so that > >not so long back dates come first > >3) X = NULL > >4) X sometime in the future > > > >Could you provide a clever solution? > > > ORDER BY CASE WHEN X=today THEN 1 ELSE > CASE WHEN X<today THEN 2 ELSE > CASE WHEN X IS NULL THEN 3 ELSE > CASE WHEN X>today THEN 4 ELSE 5 END END END END Less verbose: ORDER BY CASE WHEN x = today THEN 1 WHEN x < today THEN 2 WHEN x IS NULL THEN 3 WHEN x > today THEN 4 ELSE 5 END
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
> Every task has a timestamp X that may be NULL or a date.
> ...
> Sometimes it has date and the time-part, too.
>
> The list should be like this:
> 1) X sometime today should come first in ascending time order.
> 2) X in the past should show up after (1) in descending order
> so that not so long back dates come first
> 3) X = NULL
> 4) X sometime in the future
Assuming you mean the literal sense of "today", and that future
dates show with the least furthest away first:
SELECT * FROM yourtable
ORDER BY
CASE WHEN X::date = now()::date THEN 1 WHEN X::date < now()::date THEN 2 WHEN X IS NULL THEN 3 ELSE
4
END,
CASE WHEN X::date-now()::date < 0 THEN now()-X ELSE X-now()
END;
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200705130942
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFGRxZIvJuQZxSWSsgRAwrsAJ9HzZXzf3sQs0FVNSrhxN5UpGhc+wCcDygQ
obe5G3b58+pXhqy4Ybh/OM8=
=rJpn
-----END PGP SIGNATURE-----