> I have a table that contains a field [sic: columns are not fields] for the start time for each day of the week (e.g., MondayStart, TuesdayStart, WednesdayStart, FridayStart, SaturdayStart, SundayStart). <<
There is no such thing as "THE week", unless you mean the magazine by
that title. There infinitely many weeks which contain infinitely many
Mondays, etc. You have the wrong mental model of time. Download a
free copy of the Rick Snodgrass book on TEMPORAL QUERIES IN SQL from
the University of Arizona website.
Quote:
Quote: |
>I need a query that yields the first start time from any of the days. The problem is that, in many cases/records [sic: rows are not records], there is not start time at all, or there are start times on multiple days. Some meetings may only be on Monday where others may be on Tuesday and Thursday, or there may be no meeting at all. I want the query to find the first start time (if one exists) and report it back, or provide a NULL should no start time exist at all for any of the days. <<
| |
Where is the DDL? What is a meeting? Why do they have no durations in
this vague narrative?
The usual approach to this is the have a Calendar table which has all
the temporal data for the enterprise, such as holidays. You easily
create 100 years of data with a spreadsheet in a day or two. Now,
let's fix that missing DDL:
CREATE TABLE MeetingSchedule
(meeting_id INTEGER NOT NULL PRIMARY KEY,
meeting_title VARCHAR(25) NOT NULL,
meeting_location VARCHAR(25) NOT NULL,
meeting_start_time DATETIME NOT NULL,
meeting_end_time DATETIME NOT NULL,
CHECK (meeting_start_time < meeting_end_time),
etc.);
Your calendar table will have julianized weeks (week_nbr) and you will
need to look it up for your queries; it also has the ISO weekday codes
or names.
SELECT @my_week, C.weekday_name, MIN(S.meeting_start_time)
FROM (SELECT cal_date, weekday_name
FROM Calendar
WHERE week_nbr = @my_week_nbr
) AS C
LEFT OUTER JOIN
MeetingSchedule AS S
ON C.cal_date = EXTRACT (DATE FROM S.meeting_start_time)
GROUP BY C.weekday_name;
Creative query assistance needed 
