calling 1 row multiple times and others only once ??

meltingman
meltingman used Ask the Experts™
on
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.

Thanks!!
David
Comment
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
Commented:
Here is the basic select that should work, I would create a view with that:

SELECT * FROM
(
select event_date1 as event_date, othercols from yourtable where event_date1 is not null
UNION ALL
select event_date2 as event_date, othercols from yourtable where event_date2 is not null
UNION ALL
select event_date3 as event_date, othercols from yourtable where event_date3 is not null
) as L
ORDER BY 1 ASC


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

Commented:
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
meltingman,
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 ! --

Thanks,

graham_charles
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