Creative query assistance needed

[t8ntboy]

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.

[--CELKO--]

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.

>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_locatio n VARCHAR(25) NOT NULL,
meeting_start_t ime DATETIME NOT NULL,
meeting_end_tim e DATETIME NOT NULL,
CHECK (meeting_start_ time < meeting_end_tim e),
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_s tart_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;

[Erland Sommarskog]

Re: Creative query assistance needed

t8ntboy (t8ntboy@gmail. com) writes:

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?

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@sommarsk og.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

[Iain Sharp]

Re: Creative query assistance needed

On Thu, 14 Aug 2008 11:15:45 -0700 (PDT), t8ntboy <t8ntboy@gmail. com>
wrote:

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

Well, one solution (not necessarily the best) would be.

select RecordID,min(ti me)
from
(select recordid,Monday start as time
from table where not isnull(mondayst art)
union
select recordid,Tuesda ystart as time
from table where not isnull(Tuesdays tart)
union
select recordid,Wednes daystart as time
from table where not isnull(Wednesda ystart)
union
select recordid,Thursd aystart as time
from table where not isnull(Thursday start)
union
select recordid,Friday start as time
from table where not isnull(Fridayst art)
union
select recordid,Saturd aystart as time
from table where not isnull(Saturday start)
union
select recordid,Sunday start as time
from table where not isnull(Sundayst art)
union
select recordid,null as time
from table where isnull(mondayst art) and isnull(tuesdays tart) and
isnull(wednesda ystart) and isnull(thursday start) and
isnull(fridayst art) and isnull(saturday start) and isnull(sundayst art)
) 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