StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>OK, using your concrete example as a basis, I came up with a solution different from the others posted (although I think I like your solution better). This was tested on MS SQL Server 2005 - changes may be needed for your SQL dialect.</p> <p>First, some DDL to set the stage:</p> <pre><code>CREATE TABLE [dbo].[StandardSchedule]( [scheduledate] [datetime] NOT NULL, [shift] [varchar](25) NOT NULL, CONSTRAINT [PK_StandardSchedule] PRIMARY KEY CLUSTERED ( [scheduledate] ASC )); CREATE TABLE [dbo].[HolidaySchedule]( [holidaydate] [datetime] NOT NULL, [shift] [varchar](25) NOT NULL, CONSTRAINT [PK_HolidaySchedule] PRIMARY KEY CLUSTERED ( [holidaydate] ASC )); CREATE TABLE [dbo].[ExceptionSchedule]( [exceptiondate] [datetime] NOT NULL, [shift] [varchar](25) NOT NULL, CONSTRAINT [PK_ExceptionDate] PRIMARY KEY CLUSTERED ( [exceptiondate] ASC )); INSERT INTO ExceptionSchedule VALUES ('2008.01.06', 'ExceptionShift1'); INSERT INTO ExceptionSchedule VALUES ('2008.01.08', 'ExceptionShift2'); INSERT INTO ExceptionSchedule VALUES ('2008.01.10', 'ExceptionShift3'); INSERT INTO HolidaySchedule VALUES ('2008.01.01', 'HolidayShift1'); INSERT INTO HolidaySchedule VALUES ('2008.01.06', 'HolidayShift2'); INSERT INTO HolidaySchedule VALUES ('2008.01.09', 'HolidayShift3'); INSERT INTO StandardSchedule VALUES ('2008.01.01', 'RegularShift1'); INSERT INTO StandardSchedule VALUES ('2008.01.02', 'RegularShift2'); INSERT INTO StandardSchedule VALUES ('2008.01.03', 'RegularShift3'); INSERT INTO StandardSchedule VALUES ('2008.01.04', 'RegularShift4'); INSERT INTO StandardSchedule VALUES ('2008.01.05', 'RegularShift5'); INSERT INTO StandardSchedule VALUES ('2008.01.07', 'RegularShift6'); INSERT INTO StandardSchedule VALUES ('2008.01.09', 'RegularShift7'); INSERT INTO StandardSchedule VALUES ('2008.01.10', 'RegularShift8'); </code></pre> <p>Using these tables/rows as a basis, this SELECT statement retrieves the desired data:</p> <pre><code>SELECT DISTINCT COALESCE(e2.exceptiondate, e.exceptiondate, holidaydate, scheduledate) AS ShiftDate, COALESCE(e2.shift, e.shift, h.shift, s.shift) AS Shift FROM standardschedule s FULL OUTER JOIN holidayschedule h ON s.scheduledate = h.holidaydate FULL OUTER JOIN exceptionschedule e ON h.holidaydate = e.exceptiondate FULL OUTER JOIN exceptionschedule e2 ON s.scheduledate = e2.exceptiondate ORDER BY shiftdate </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload