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 2 2102
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
--
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Marco Alting |
last post by:
Hi
I'm trying to create an input screen that allows users to create a
hierarchical structure as the one below, but have no clue where to start or
how to construct it. Can anyone help me out...
|
by: Mike |
last post by:
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...
|
by: Max |
last post by:
Suppose we have an object hierarchy in XSD.
For example, let us take Control, Label, TextBox and LinkLabel windows
controls.
Each control has some properties which are represented as elements....
|
by: tony.fountaine |
last post by:
I am working on a project to read a Bosch Measurement Data File (MDF).
The file contains a number of blocks that can be read from the file
using a baisc structure.
For example the ID BLOCK is as...
|
by: Sam Kong |
last post by:
Hi,
JavaScript hides its memory structure.
I know that numbers, booleans, null and undefined are value types
(value is directed saved in a variable).
I want to know:
- How JavaScript...
|
by: arnuld |
last post by:
problem: define a /struct Date/ to keep track of dates. provide
functions that read Dates from input, write Dates to output &
initialize a date with date.
solution: i thought of a /vector/ of...
|
by: Asm23 |
last post by:
I am doing research on a vision based real-time motion control
system.
The software aspect I will develop was based on windows XP and visual
studio6.
The project has three parts.
The main...
|
by: John Dann |
last post by:
Trying to learn Python here, but getting tangled up with variable
scope across functions, modules etc and associated problems. Can
anyone advise please?
Learning project is a GUI-based...
|
by: pereges |
last post by:
Hi, I'm trying to deallocate a kd tree which I created dynamically.
There were no problems
creating the structure and I can access it easily but there is a
problem while trying to free
it. Here's...
|
by: rymonator |
last post by:
Hello, I was wondering how I would convert an existing structure to a tree. What I want is basically a family tree. What I have is the following:
array (
array(id => 123, p1 => 567, p2 => 765),...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |