Creative query assistance needed

I have a table that contains a field for the start time for each day
of the week (e.g., MondayStart, TuesdayStart, WednesdayStart,
FridayStart, SaturdayStart, SundayStart).

I need a query that yields the first start time from any of the days.
The problem is that, in many cases/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 value should no
start time exist at all for any of the days.

Ideally, the results would look something like.


RecordID Time
2342 1300
3452 0900
3432 null
5634 8900


Does anyone have a creative solution?

Thanks.

re: Creative query assistance needed

> 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.
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;

re: Creative query assistance needed

t8ntboy (t8ntboy@gmail.com) writes:I'm sorry, but I am completely lost. I can only respond with the
standard suggestion that you post:

o CREATE TABLE statements for your table(s).
o INSERT statements with sample data.
o The desired result from the sample.
o Which version of SQL Server you are using.

This makes it simple to copy and paste into query tool to develop a
tested solution. It also helps to clarify your description.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

re: Creative query assistance needed

On Thu, 14 Aug 2008 11:15:45 -0700 (PDT), t8ntboy <t8ntboy@gmail.com>
wrote:
Well, one solution (not necessarily the best) would be.

select RecordID,min(time)
from
(select recordid,Mondaystart as time
from table where not isnull(mondaystart)
union
select recordid,Tuesdaystart as time
from table where not isnull(Tuesdaystart)
union
select recordid,Wednesdaystart as time
from table where not isnull(Wednesdaystart)
union
select recordid,Thursdaystart as time
from table where not isnull(Thursdaystart)
union
select recordid,Fridaystart as time
from table where not isnull(Fridaystart)
union
select recordid,Saturdaystart as time
from table where not isnull(Saturdaystart)
union
select recordid,Sundaystart as time
from table where not isnull(Sundaystart)
union
select recordid,null as time
from table where isnull(mondaystart) and isnull(tuesdaystart) and
isnull(wednesdaystart) and isnull(thursdaystart) and
isnull(fridaystart) and isnull(saturdaystart) and isnull(sundaystart)
) as starttimes

This will effectively return a dataset with two columns, recordID and
time, with an entry for each meeting start time, or a null if there is
no meeting start time set. Then it will get the minimum start time
from that set for each record ID.

Not going to be quick though.

Regards

Iain