(OT) Complex SQL Statement

boblawblah has asked for the wisdom of the Perl Monks concerning the following question:

(I know there are SQL forums out there, but everyone here is so helpful)

I am writing scheduling software. There is a database of projects that have a ProjectedStart and ActualStart, and ProjectedFinish and ActualFinish variables.

With one sql statement I want to get the projects that are (potentially) active for a given date. I have my logic sorted out for the most part, but I need to translate into SQL and had trouble with the help docs I found.

So basically I need to check that the given date is between two dates. DATE1 and DATE2.

DATE1 = ActualStart or ProjectedStart;
DATE2 = ActualFinish or ProjectedFinish;
(Use the actual date if there is one, if not use the projected.)

However, if there is no actual or projected start, the record should be selected as long as the given date is before projected/actual finish date.

If there is no finish, the record should be selected as long as the given date is after the projected/actual start date.

If there is neither a start nor finish date, then the record should be selected.

THANK YOU for any input you can give me!

Comment on (OT) Complex SQL Statement

Replies are listed 'Best First'.
Re: (OT) Complex SQL Statement by pjotrik (Friar) on Jul 23, 2008 at 19:48 UTC
Really not the right place here, but OK. It would help to specify your db engine as well. It should go roughly like this: `select * from tbl where coalesce(actual_start, projected_start, some_deep_past) <= curre +nt_date and coalesce(actual_finish, projected_finish, some_far_future) >= curr +ent_date` [download]	[reply] [d/l]
Re^2: (OT) Complex SQL Statement by massa (Hermit) on Jul 23, 2008 at 20:35 UTC
even clearer (IMHO) and shorter: `select * from tbl where current_date between coalesce(actual_start, projected_start, some_deep_past) and coalesce(actual_finish, projected_finish, some_far_future)` [download] []s, HTH, Massa (κς,πμ,πλ)	[reply] [d/l]
Re^2: (OT) Complex SQL Statement by Pancho (Pilgrim) on Jul 23, 2008 at 19:55 UTC
Ahh, the beauty of an elegant and concise solution...	[reply]
Re^2: (OT) Complex SQL Statement by boblawblah (Scribe) on Jul 24, 2008 at 15:23 UTC
THANK YOU!	[reply]
Re: (OT) Complex SQL Statement by leocharre (Priest) on Jul 23, 2008 at 20:35 UTC
If you're using mysqld and have shell access, you may be able to use the mysql prompt directly. Here you can perform queries and see what happens right away. It's useful for learning. (Keep in mind, simple queries should work across different databases, but more complicated ones will not- or you will get unexpected results.)	[reply]