[flekso]

Hi,

I have a little problem that goes beyond my understanding of SQL.

I have web-based gui that presents daily tasks that must be done. Every task has starting and ending point (stored as datetime-fields in mysql db)

e.g.

-----------------------------------------

--[date: 15.06.2004 - page 1 ]---

task 1. start: xxxxx end: xxxx
task 2. start: xxxxx end: xxxx

--[date: 16.06.2004 - page 2 ]---

task 3. start: xxxxx end: xxxx
task 4. start: xxxxx end: xxxx

------------------------------------------

Ok, no big deal selecting tasks that start on the same day.
e.g. SELECT * FROM foobar WHERE TO_DAYS(start) = TO_DAYS('2004-06-28');

... but how can I include to my query tasks that have started earlier (eg. 2004-06-27) and continue to another day ?

Thanks for help.

[marklar]

Oops, didn't read this properly 1st time through so I've deleted my original post.

You can search both start and end fields and then order by one of them. I'm guessing you want all tasks that have started but not finished.

This sorts by start

SELECT * FROM foobar WHERE TO_DAYS(start) < TO_DAYS('2004-06-28') AND TO_DAYS(end) > TO_DAYS('2004-06-28') ORDER BY TO_DAYS(start) ASC;

[Worm]

Originally Posted by flekso

Hi,

I have a little problem that goes beyond my understanding of SQL.

I have web-based gui that presents daily tasks that must be done. Every task has starting and ending point (stored as datetime-fields in mysql db)

e.g.

-----------------------------------------

--[date: 15.06.2004 - page 1 ]---

task 1. start: xxxxx end: xxxx
task 2. start: xxxxx end: xxxx

--[date: 16.06.2004 - page 2 ]---

task 3. start: xxxxx end: xxxx
task 4. start: xxxxx end: xxxx

------------------------------------------

Ok, no big deal selecting tasks that start on the same day.
e.g. SELECT * FROM foobar WHERE TO_DAYS(start) = TO_DAYS('2004-06-28');

... but how can I include to my query tasks that have started earlier (eg. 2004-06-27) and continue to another day ?

Thanks for help.

I think you will have to decide... either you want to sort it by date of beginning or by date of ending. The list you have pasted above must categorize the tasks bei any date, either beginning or ending. And if you select each row of the database, you can put a WHERE on the beginning date = '2004-06-28' which should also return the ending date. If you now sort your list, you could have something like this:

-----------------------------------------

--[date: 15.06.2004 - page 1 ]---

task 1. start: 15.06.2004 end: 15.06.2004
task 2. start: 15.06.2004 end: 23.06.2004

--[date: 16.06.2004 - page 2 ]---

task 3. start: 16.06.2004 end: 16.06.2004
task 4. start: 16.06.2004 end: 27.06.2004

------------------------------------------

Where is your problem, if i got you wrong...?

[marklar]

I don't know what's wrong with me today, you don't want what I said do you, so you have 2 choices as pointed out by Worm, either all tasks starting today but not finished OR all tasks started before and finishing today.

Starting today:
SELECT * FROM foobar WHERE TO_DAYS(start) = TO_DAYS('2004-06-28') AND TO_DAYS(end) => TO_DAYS('2004-06-28') ORDER BY TO_DAYS(start) ASC;

Finishing today:
SELECT * FROM foobar WHERE TO_DAYS(start) <= TO_DAYS('2004-06-28') AND TO_DAYS(end) = TO_DAYS('2004-06-28') ORDER BY TO_DAYS(start) ASC;