I have an issue that I am trying to work through that requires me to evaluate date fields in the database to determine the next most recent date. For instance, in a query I would do a case statement as such...
SELECT
CASE
WHEN EventDate1 IS NOT NULL AND EventDate1 <= GETDATE()
EventDate1
WHEN EventDate2 IS NOT NULL AND EventDate2 <= GETDATE()
EventDate2
END AS CalculatedEventDate
FROM SomeTable
I have tried several ways to add this field and then sort a repeater by it but I have had no success.
-
The first thing I tried was to leverage a custom query datasource that returned both BookingEvents and Events sorted by the CalculatedEventDate. I then setup a repeater that would list the events. This worked correctly but the issue is that when I click on the event link, it would simply go to the linked url but still show the event listing, not the specific event detailed page. A couple things to note, the repeater is on the main page and the booking events and events are underneath this main page. All share the same template which I am not sure is correct or not.
-
The next thing I tried was to utilize the page data source but overriding the selectall method to include the calculatedeventdate sql. It seems however, that the page data source does not use the select all event for bookingevents or events.
So, I am not sure how best to add the calculatedeventdate to BookingEvents and Events and still have the repeater display a list which allows for the details page to be displayed. Could this be that the main page and details page use the same template? This works fine if I simply use a page data source without the calculatedeventdate field. But I cannot get the Page Data Source to run a query with the calculatedeventdetail.
Any help is much appreciated.