calling 1 row multiple times and others only once ??

meltingman used Ask the Experts™
Before I post all my existing code (nothing seems to work yet) hopefully I'll explain myself well enough that someone (ANYONE!) can help me.

I'm working with VBScript, a table of events in a Microsoft SQL Database, creating ASP pages.  The page was previously built by another programmer and it loops through all records with "event_date" field of today or greater.  Now we've added 5 more event_date columns so we can have one row for an event with the same title, same description, etc, just recurring/multiple events dates.  So I added columns "event_date1" "event_date2" "event_date3" etc.

Is there ANY way to continue to loop and pull in my event rows, listed in chronological order of the event dates, pulling in the rows with multiple event_dates multiple times but only pulling in the events with one date once?  I actually did this simply with a bunch of if/thens, but they don't stay in chronological/date order.  The events with multiple dates just stack, then move on to the next event_date1.  

I've tried creating a few stored procedures, lots of loops, and now I'm dead in the water, tons of if/then statements, and I feel a bit logically dead...  Please help, any advice or tips on where to look for the answer would be GREATLY appreciated.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Here is the basic select that should work, I would create a view with that:

select event_date1 as event_date, othercols from yourtable where event_date1 is not null
select event_date2 as event_date, othercols from yourtable where event_date2 is not null
select event_date3 as event_date, othercols from yourtable where event_date3 is not null
) as L

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

obviously, I have only created for 3 event_date columns, you can easily enlarge to 5...
good answer angellll!

meltingman - othercols must be in the same order fo the UNION to work properly
No comment has been added lately (256 days), so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:

RECOMMENDATION: Award points to angelIII http:#7662036

Please leave any comments here within 7 days.

-- Please DO NOT accept this comment as an answer ! --


EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial