A tricky query

SQL Apprentice Question
I have to write a query to generate a report over some interesting
data. It's basically scheduling which days people are working. The
data looks like this:

Employee StartDate EndDate Roster
---------------- ---------------- ---------------- ------------
Bob 12-Jun-06 24-Jun-06 _*___**
Mary 12-Jun-06 24-Jun-06 *_*__*_


The trick is, the roster field contains a string with a _ or *
depending on wether the person is scheduled to work that day or not,
but the first character always starts on the sunday. The startdate and
enddate can be any day of the week.


In the example above, the 12-jun is a monday, so monday corresponds to
the second character in the roster string, so Bob's working and Mary's
not. The roster string wraps around, so the first character of the
roster string actually corresponds with the enddate here! Now, this
roster string could be 7, 10, 14 days long. The startDate -> endDate
could be the length of the roster string or less (only show a subset of
the roster data).


So! I need to write a query to feed a report to format this into
something like:


Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
Friday 16-Jun
----------------------- -----------------------
----------------------------- -------------------------
---------------------
Bob Mary
Bob


Mary


I could get the report out if I can write a query to get it to this:


Employee DateWorking
---------------- -------------------
Bob 12-Jun
Bob 16-Jun
Mary 13-Jun
Mary 16-Jun


Any ideas?



Celko Answers

>> I have to write a query to generate a report over some interesting data. <<


First of all, you are not using ISO-8601 format dates. You might want
to do that, since what you did post was ambigous as well as
non-standard, I hope you do not think that ORACLE is a standard.

Next, you might want to read a book on programming principles. We do
not do reports in the database in a tiered architecture. This is more
fundamental than SQL.



>> It's basically scheduling which days people are working. The data looks like this: <<


Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. I
would love to see the LOGICAL definition of that silly bar chart you
labeled "roster" in your narrative since it is pure display.

CREATE TABLE Roster
(employee_name VARCHAR(20) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (employee_name, start_date));



>> I could get the report out if I can write a query to get it to this: ..<<


This is usually done with a Calendar table:

SELECT R.employee_name, C.cal_date
FROM Calendar AS C
LEFT OUTER JOIN
Roster AS R
ON C.cal_date BETWEEN R.start_date AND R.end_date
WHERE C.cal_date BETWEEN @my_start_date AND @my_end_date;


Google for other uses of the Calendar and auxiliary tables.