Creating a diary - structure problems!

[Mike]

Hi all,

I am writing a database using SQL as the back end and Access as the
front end. My project is to create a rota for when people have to
work. The problem I have is setting up the tables so I can query them
correctly.

I currently have a staff table which holds the staff_id and staff_name
fields. I have a worktime table that has the staff_id, date, start
time, end time as fields. What I would like to do able to do is have a
query that returns the times a person is working in date order along
the row
i.e

staff_id, date, day1_start, day1_end, day2_start, day2_end etc..
1, 8/1/04, 9:00, 18:00, 9:00, 18:00, etc..

I can't seem to work out a query that will do this easily. I can
think of ways of manipulating the data heavily but that defeats the
object. I wonder if my table structure is incorrect. Is it good
practice to have a table where each field is a date? Seeing this will
need to be referred to for year on year comparison it seems a bad way
of doing it. Basically is this the correct structure for a diary
application?

Any help from you great people out there would be superb! Thanks in
anticipation.

Cheers
Mike

[David Portas]

Try this out (TSQL):

CREATE TABLE WorkTime (staff_id INTEGER, start_datetime DATETIME,
end_datetime DATETIME, PRIMARY KEY (staff_id, start_datetime))

INSERT INTO WorkTime VALUES (1,'2003-01-08T09:00:00','2003-01-08T18:00:00')
INSERT INTO WorkTime VALUES (1,'2003-01-09T10:00:00','2003-01-09T19:00:00')
INSERT INTO WorkTime VALUES (1,'2003-01-10T09:00:00','2003-01-10T18:00:00')
INSERT INTO WorkTime VALUES (2,'2003-01-08T09:00:00','2003-01-08T18:00:00')

DECLARE @dt DATETIME
SET @dt = '20030108'

SELECT staff_id,
MIN(CASE DATEDIFF(DAY,@dt,start_datetime) WHEN 0 THEN
CONVERT(CHAR(8),start_datetime,8) END) AS day1_start,
MAX(CASE DATEDIFF(DAY,@dt,end_datetime) WHEN 0 THEN
CONVERT(CHAR(8),end_datetime,8) END) AS day1_end,
MIN(CASE DATEDIFF(DAY,@dt,start_datetime) WHEN 1 THEN
CONVERT(CHAR(8),start_datetime,8) END) AS day2_start,
MAX(CASE DATEDIFF(DAY,@dt,end_datetime) WHEN 1 THEN
CONVERT(CHAR(8),end_datetime,8) END) AS day2_end,
MIN(CASE DATEDIFF(DAY,@dt,start_datetime) WHEN 2 THEN
CONVERT(CHAR(8),start_datetime,8) END) AS day3_start,
MAX(CASE DATEDIFF(DAY,@dt,end_datetime) WHEN 2 THEN
CONVERT(CHAR(8),end_datetime,8) END) AS day3_end,
MIN(CASE DATEDIFF(DAY,@dt,start_datetime) WHEN 3 THEN
CONVERT(CHAR(8),start_datetime,8) END) AS day4_start,
MAX(CASE DATEDIFF(DAY,@dt,end_datetime) WHEN 3 THEN
CONVERT(CHAR(8),end_datetime,8) END) AS day4_end,
MIN(CASE DATEDIFF(DAY,@dt,start_datetime) WHEN 4 THEN
CONVERT(CHAR(8),start_datetime,8) END) AS day5_start,
MAX(CASE DATEDIFF(DAY,@dt,end_datetime) WHEN 4 THEN
CONVERT(CHAR(8),end_datetime,8) END) AS day5_end
FROM WorkTime
WHERE start_datetime >= @dt AND end_datetime < DATEADD(DAY,5,@dt)
GROUP BY staff_id

--
David Portas
------------
Please reply only to the newsgroup
--

[Mike]

Thats great I understand what you are doing and everything. Although
ultimatly I want to be able to update the database. So by altering
that table then it won't pass the changes on to the other tables. This
is why I was questioning my structure in the inital question. Cheers
for replying though I learnt a technique there.

Mike

"David Portas" <RE****************************@acm.org> wrote in message news:<SY********************@giganews.com>...

Try this out (TSQL):

CREATE TABLE WorkTime (staff_id INTEGER, start_datetime DATETIME,
end_datetime DATETIME, PRIMARY KEY (staff_id, start_datetime))

INSERT INTO WorkTime VALUES (1,'2003-01-08T09:00:00','2003-01-08T18:00:00')
INSERT INTO WorkTime VALUES (1,'2003-01-09T10:00:00','2003-01-09T19:00:00')
INSERT INTO WorkTime VALUES (1,'2003-01-10T09:00:00','2003-01-10T18:00:00')
INSERT INTO WorkTime VALUES (2,'2003-01-08T09:00:00','2003-01-08T18:00:00')

DECLARE @dt DATETIME
SET @dt = '20030108'

SELECT staff_id,
MIN(CASE DATEDIFF(DAY,@dt,start_datetime) WHEN 0 THEN
CONVERT(CHAR(8),start_datetime,8) END) AS day1_start,
MAX(CASE DATEDIFF(DAY,@dt,end_datetime) WHEN 0 THEN
CONVERT(CHAR(8),end_datetime,8) END) AS day1_end,
MIN(CASE DATEDIFF(DAY,@dt,start_datetime) WHEN 1 THEN
CONVERT(CHAR(8),start_datetime,8) END) AS day2_start,
MAX(CASE DATEDIFF(DAY,@dt,end_datetime) WHEN 1 THEN
CONVERT(CHAR(8),end_datetime,8) END) AS day2_end,
MIN(CASE DATEDIFF(DAY,@dt,start_datetime) WHEN 2 THEN
CONVERT(CHAR(8),start_datetime,8) END) AS day3_start,
MAX(CASE DATEDIFF(DAY,@dt,end_datetime) WHEN 2 THEN
CONVERT(CHAR(8),end_datetime,8) END) AS day3_end,
MIN(CASE DATEDIFF(DAY,@dt,start_datetime) WHEN 3 THEN
CONVERT(CHAR(8),start_datetime,8) END) AS day4_start,
MAX(CASE DATEDIFF(DAY,@dt,end_datetime) WHEN 3 THEN
CONVERT(CHAR(8),end_datetime,8) END) AS day4_end,
MIN(CASE DATEDIFF(DAY,@dt,start_datetime) WHEN 4 THEN
CONVERT(CHAR(8),start_datetime,8) END) AS day5_start,
MAX(CASE DATEDIFF(DAY,@dt,end_datetime) WHEN 4 THEN
CONVERT(CHAR(8),end_datetime,8) END) AS day5_end
FROM WorkTime
WHERE start_datetime >= @dt AND end_datetime < DATEADD(DAY,5,@dt)
GROUP BY staff_id