displaying date depending on events

[alex11343]

Another question:

the report I'm working on needs to display the date of an event depending on whether it has taken place already or not yet and whether it's planned or not at the time the report is run

4 cases are to be taken into account:

- event has occurred in the past, at least once, a next one is planned in the future
-> display of the last one's date

- event has occured in the past, at least once, not any next one planned in the future
-> display of the next one's date

- event has never occured in the past,  one is planned in the future
-> display remains empty/ display of the next one's date (not yet decided)

- event has never occured in the past and is not planned to take place in the future
-> display remains empty

so far I guess I shall start on a a CASE WHEN basis something like

CASE
WHEN ([Event_Date] IS NULL
   AND [Last_Event_Date] IS NOT NULL
   AND getDate()> [Event_Date])
   THEN [Last_Event_Date]
...


Am I on the right track or am I to hit the wall?

any other idea I shal test?

Any help would be most appreciated here again,

thanks for any advice

Alx

[MDXpressor]

You seem to be on the right track, but I don't envy you for having to code out the possibilities.  I hope you don't end up adding more possible dates, 'cause if you do, your calc is going to increase in possibilities exponentially.

[alex11343]

Thanks for your answer.
I was wondering whether I was heading towards a dead end about to hit the wall or  more likely on the right track.

Your answer gives me hope and motivation to end this.

I will post my code if I manage to get it to work, in case it helps someone else.

If someone has some already written code matching my goal, I take as well  ;)