Application is a Work Tracking/Timesheet database.
The increments of work are stored in the TimesheetItem table. This
contains, inter alia, the Work Code, the Start and the Duration that
the employee spent that day on a particular project.
Some employees in the Network Support Department don't complete a
standard 7.5 hour day for various reasons, so for every Network Support
person I need to update these particular days with an amount to bring
the total day's hours to 7.5.
This SQL will get me a list of all TimesheetItem records for the
personnel concerned.
SELECT
TimesheetItem.TypeID,
[Work].WorkCode,
TimesheetItem.Start AS Start,
SUM(CAST(TimesheetItem.DurationMins AS float) / 60) AS Hours
FROM
TimesheetItem LEFT OUTER JOIN
[Work] ON TimesheetItem.WorkID = [Work].WorkID
WHERE
(TimesheetItem.EmployeeID IN
(SELECT EmployeeID FROM Employee WHERE DepartmentID = 2))
GROUP BY
TimesheetItem.TypeID, TimesheetItem.Start, [Work].WorkCode
HAVING
(TimesheetItem.Start >= @.FromDate) AND (TimesheetItem.Start <= @.ToDate)
ORDER BY
TimesheetItem.Start
What I need is to group these records by EmployeeID where the
accumulated hours per day are < 7.5, so that I can then insert an
increment to make up the difference.
I'm writing this from home and I don't have access to the tables to
provide a script, but there's nothing untoward there.
Hope someone can help!
Thanks
Edward(teddysnips@.hotmail.com) writes:
> This SQL will get me a list of all TimesheetItem records for the
> personnel concerned.
> SELECT
> TimesheetItem.TypeID,
> [Work].WorkCode,
> TimesheetItem.Start AS Start,
> SUM(CAST(TimesheetItem.DurationMins AS float) / 60) AS Hours
> FROM
> TimesheetItem LEFT OUTER JOIN
> [Work] ON TimesheetItem.WorkID = [Work].WorkID
> WHERE
> (TimesheetItem.EmployeeID IN
> (SELECT EmployeeID FROM Employee WHERE DepartmentID = 2))
> GROUP BY
> TimesheetItem.TypeID, TimesheetItem.Start, [Work].WorkCode
> HAVING
> (TimesheetItem.Start >= @.FromDate) AND (TimesheetItem.Start <= @.ToDate)
> ORDER BY
> TimesheetItem.Start
> What I need is to group these records by EmployeeID where the
> accumulated hours per day are < 7.5, so that I can then insert an
> increment to make up the difference.
I have problems to get a grip on this, because in the query you are
not grouping results per employee, so I can't see how you could be
able to dig out the employess that worked less than 7.5 hours.
Here is a modified version of the query that gets the employees that
worked less than 7.5 hours:
SELECT EmployeeID, convert(char(8), Start, 112), SUM(Hours)
FROM (SELECT TI.EmployeeID, TI.TypeID, W.WorkCode,
TI.Start AS Start,
SUM(CAST(TI.DurationMins AS float) / 60) AS Hours
FROM TimesheetItem TI
LEFT JOIN [Work] W ON TI.WorkID = W.WorkID
WHERE EXISTS (SELECT *
FROM Employee E
WHERE TI.EmployeeID = E.EmployeeID
AND E:DepartmentID = 2)
AND TI.Start >= @.FromDate
AND TI.Start <= @.ToDate
GROUP BY TI.EmployeeID, TI.TypeID, TI.Start, W.WorkCode) AS x
GROUP BY EmployeeID, convert(char(8), Start, 112)
HAVING 7.5 - SUM(Hours) > 1E-6
ORDER BY EmployeeID, convert(char(8), Start, 112)
But this can be simplified to:
SELECT TI.EmployeeID, convert(char(8), TI.Start, 112),
SUM(CAST(TI.DurationMins AS float) / 60) AS Hours
FROM TimesheetItem TI
WHERE EXISTS (SELECT *
FROM Employee E
WHERE TI.EmployeeID = E.EmployeeID
AND E:DepartmentID = 2)
AND TI.Start >= @.FromDate
AND TI.Start <= @.ToDate
GROUP BY TI.EmployeeID, EmployeeID, convert(char(8), Start, 112)
HAVING 7.5 - SUM(Hours) > 1E-6
ORDER BY EmployeeID, convert(char(8), Start, 112)
Then I really don't grasp what you are to do with that information.
Maybe you should come back on Monday when you have access to the
tables, so you also can post sample data (as INSERT statements)
and the desired result given the sample.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
You usually model a timesheet with start and end time for each event.
Otherwise look for a lifetime of horrible left outer self-join thaqt
run like glue and are bitch to maintain
A data element can be a type or an identifer, but there no way it can
be both.
Also, avoid FLOAT in favor of DECIMAL unless you like rounding errors.
And if you do use FLOAT, do not divide by integers.|||--CELKO-- wrote:
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
Apologies for going off half-cocked. Please find at the bottom scripts
for relevant tables.
Erland's first SQL statement as amended below (his second, simplified
version, gives an error on line 7 - "Invalid column name 'Hours') does
what I want, more or less, in that it gives a list of dates and
employees who worked less than 7.5 hours on a particular day.
SELECT
EmployeeID,
convert(char(8),
Start, 112) As TimesheetDate,
SUM(Hours)
FROM
(SELECT TI.EmployeeID, TI.TypeID, W.WorkCode, TI.Start AS Start,
SUM(CAST(TI.DurationMins AS float) / 60) AS Hours
FROM TimesheetItem TI
LEFT JOIN [Work] W ON TI.WorkID = W.WorkID
WHERE EXISTS (SELECT *
FROM Employee E
WHERE TI.EmployeeID = E.EmployeeID
AND E.DepartmentID = 2)
GROUP BY TI.EmployeeID, TI.TypeID, TI.Start, W.WorkCode) AS x
GROUP BY EmployeeID, convert(char(8), Start, 112)
HAVING 7.5 - SUM(Hours) > 1E-6
ORDER BY EmployeeID, convert(char(8), Start, 112)
This gives a table something like this:
453200110105.0
453200111267.0
453200201047.0
453200201247.25
453200202276.75
453200203087.0
453200203286.5
453200205077.0
3,900 rows removed!
What I need to do is, for each of those employees on the corresponding
day, is to add an extra record that will bring up the number of hours
worked that day to 7.5. I've thought long and hard and can't see any
way to go without a cursor.
Thanks so far
Edward
Tabledefs below:
CREATE TABLE [dbo].[Employee] (
[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Surname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DepartmentID] [int] NOT NULL ,
[JobDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NULL ,
[DefaultRatePerHour] [smallmoney] NULL ,
[EmailAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[UserGroupID] [int] NOT NULL ,
[Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastLogon] [datetime] NULL ,
[PasswordChange] [smalldatetime] NULL ,
[PreviousPassword1] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword2] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword3] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword4] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword5] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TimesheetItem] (
[ItemID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [int] NOT NULL ,
[TypeID] [int] NOT NULL ,
[Start] [smalldatetime] NOT NULL ,
[DurationMins] [int] NOT NULL ,
[WorkID] [int] NULL ,
[WorkComponentID] [int] NULL ,
[WorkItemID] [int] NULL ,
[Notes] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OffSite] [tinyint] NULL ,
[TravelTo] [smalldatetime] NULL ,
[TravelToMins] [int] NULL ,
[TravelFrom] [smalldatetime] NULL ,
[TravelFromMins] [int] NULL ,
[TravelMileage] [int] NULL ,
[NonChargeableMins] [int] NULL ,
[OTAuthorisedID] [int] NULL ,
[OTAuthorisedDate] [smalldatetime] NULL ,
[Abroad] [bit] NULL ,
[InconvAllowance] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ApprovalID] [int] NULL ,
[AprovalDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Work] (
[WorkID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkTypeID] [int] NULL ,
[WorkCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Chargeable] [bit] NOT NULL ,
[ClientID] [int] NULL ,
[ClientContactID] [int] NULL ,
[Entered] [smalldatetime] NULL ,
[ApprovalRequired] [tinyint] NULL ,
[SQ_BlockID] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[TimesheetItem] WITH NOCHECK ADD
CONSTRAINT [PK_TimesheetItem] PRIMARY KEY CLUSTERED
(
[ItemID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Work] WITH NOCHECK ADD
CONSTRAINT [PK_Work] PRIMARY KEY CLUSTERED
(
[WorkID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[TimesheetItem] WITH NOCHECK ADD
CONSTRAINT [DF_TimesheetItem_TypeID] DEFAULT (0) FOR [TypeID],
CONSTRAINT [DF_TimesheetItem_DurationMins] DEFAULT (0) FOR
[DurationMins],
CONSTRAINT [DF_TimesheetItem_OffSite] DEFAULT (0) FOR [OffSite],
CONSTRAINT [DF_TimesheetItem_TravelToMins] DEFAULT (0) FOR
[TravelToMins],
CONSTRAINT [DF_TimesheetItem_TravelFromMins] DEFAULT (0) FOR
[TravelFromMins],
CONSTRAINT [DF_TimesheetItem_NonChargeableMins] DEFAULT (0) FOR
[NonChargeableMins],
CONSTRAINT [DF_TimesheetItem_Abroad] DEFAULT (0) FOR [Abroad],
CONSTRAINT [DF_TimesheetItem_Reviewed] DEFAULT (0) FOR [ApprovalID],
CONSTRAINT [CK_TimesheetItem] CHECK ([TypeID] like '[0-3]' and
[OffSite] like '[0-1]')
GO
ALTER TABLE [dbo].[Work] WITH NOCHECK ADD
CONSTRAINT [DF_Work_Chargeable] DEFAULT (0) FOR [Chargeable],
CONSTRAINT [DF_WorkItem_ReviewRequired] DEFAULT (0) FOR
[ApprovalRequired],
CONSTRAINT [IX_WorkCode] UNIQUE NONCLUSTERED
(
[WorkCode]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_Work] CHECK ([ApprovalRequired] like '[0-1]')
GO
ALTER TABLE [dbo].[Employee] ADD
CONSTRAINT [FK_Employee_Department] FOREIGN KEY
(
[DepartmentID]
) REFERENCES [dbo].[Department] (
[DepartmentID]
),
CONSTRAINT [FK_Employee_UserGroup] FOREIGN KEY
(
[UserGroupID]
) REFERENCES [dbo].[UserGroup] (
[UserGroupID]
)
GO
ALTER TABLE [dbo].[TimesheetItem] ADD
CONSTRAINT [FK_TimesheetItem_Employee] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
),
CONSTRAINT [FK_TimesheetItem_Employee1] FOREIGN KEY
(
[OTAuthorisedID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
),
CONSTRAINT [FK_TimesheetItem_WorkCode] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
)
GO
ALTER TABLE [dbo].[Work] ADD
CONSTRAINT [FK_Work_WorkType] FOREIGN KEY
(
[WorkTypeID]
) REFERENCES [dbo].[WorkType] (
[WorkTypeID]
)
GO|||(teddysnips@.hotmail.com) writes:
> Erland's first SQL statement as amended below (his second, simplified
> version, gives an error on line 7 - "Invalid column name 'Hours')
I'm sorry for that, but that what you get when you don't post CREATE
TABLE and INSERT. When you do that you get a tested query. When you
don't, you get something which I've just typed, and it may contain
trivial syntax errors. The assumption is that you are able to fix those
yourself.
> What I need to do is, for each of those employees on the corresponding
> day, is to add an extra record that will bring up the number of hours
> worked that day to 7.5. I've thought long and hard and can't see any
> way to go without a cursor.
Add where? To the timesheet table? With what data?
I'm sorry, but since I don't know exactly what you want I deciline for
now.
I suggest that you trim down the table defintions to the columns relevant
for the problem, add some INSERT statements with sample data, and the
clearly specify the end result you are looking for.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Erland Sommarskog wrote:
> (teddysnips@.hotmail.com) writes:
> > Erland's first SQL statement as amended below (his second, simplified
> > version, gives an error on line 7 - "Invalid column name 'Hours')
> I'm sorry for that, but that what you get when you don't post CREATE
> TABLE and INSERT. When you do that you get a tested query. When you
> don't, you get something which I've just typed, and it may contain
> trivial syntax errors. The assumption is that you are able to fix those
> yourself.
Erland, in no way whatsoever was I criticising you! The service you
provide here is incredibly valuable, and you don't even get paid! So I
apologise if I appeared to be critical.
Here goes from the beginning.
Basic CREATE TABLE STUFF:
CREATE TABLE [dbo].[Employee] (
[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TimesheetItem] (
[ItemID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [int] NOT NULL ,
[Start] [smalldatetime] NOT NULL ,
[DurationMins] [int] NOT NULL ,
[WorkID] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Work] (
[WorkID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
INSERT Section:
INSERT INTO Employee (UserName) VALUES ('Smith')
INSERT INTO Work (WorkCode) VALUES ('AB01')
INSERT INTO Work (WorkCode) VALUES ('2002')
INSERT INTO TimesheetItem
(EmployeeID,
Start,
DurationMins,
WorkID)
VALUES
(1,
'17 July 2005 08:30:00',
450,
1)
INSERT INTO TimesheetItem
(EmployeeID,
Start,
DurationMins,
WorkID)
VALUES
(1,
'18 July 2005 08:30:00',
180,
1)
This will result in a single Employee 'Smith', with two WorkCodes
'AB01' and '2002'. On 17 July Smith worked for 7.5 hours on AB01, but
on 18 July only for three hours. The SQL:
SELECT
EmployeeID,
convert(char(8), Start, 112) As TimesheetDate,
SUM(Hours) as NumHours
FROM
(SELECT TI.EmployeeID, W.WorkCode,
TI.Start AS Start,
SUM(CAST(TI.DurationMins AS float) / 60) AS Hours
FROM TimesheetItem TI
LEFT JOIN [Work] W ON TI.WorkID = W.WorkID
WHERE EXISTS (SELECT *
FROM Employee E
WHERE TI.EmployeeID = E.EmployeeID )
GROUP BY TI.EmployeeID, TI.Start, W.WorkCode) AS x
GROUP BY EmployeeID, convert(char(8), Start, 112),
HAVING 7.5 - SUM(Hours) > 1E-6
ORDER BY EmployeeID, convert(char(8), Start, 112)
returns a single row:
1200507183.0
What I need to do is, for all such rows returned from the live data, to
add a record to the TimesheetItem table that would make up that day's
work to 7.5 hours for Workcode '2002'. The Start column would contain
the date and the correct offset from 08:30 - in this case 11:30 (that
is, three hours).
So, for example, the insert to make the Smith record compliant for 18
July would be
INSERT INTO TimesheetItem
(EmployeeID,
Start,
DurationMins,
WorkID)
VALUES
(1,
'18 July 2005 11:30:00',
270,
2)
Can I do this without using a cursor? I apologise if this is badly
expressed - I'm doing my best!
Edward|||Will (billrigby@.hotmail.com) writes:
> So, for example, the insert to make the Smith record compliant for 18
> July would be
> INSERT INTO TimesheetItem
> (EmployeeID,
> Start,
> DurationMins,
> WorkID)
> VALUES
> (1,
> '18 July 2005 11:30:00',
> 270,
> 2)
> Can I do this without using a cursor? I apologise if this is badly
> expressed - I'm doing my best!
Sure, no need for a cursor:
INSERT INTO TimesheetItem
(EmployeeID,
Start,
DurationMins,
WorkID)
SELECT EmployeeID, dateadd(MINUTE, DurationMins, LastStart),
7.5 * 60 - DurationMins, 2
FROM (SELECT TI.EmployeeID,
convert(char(8), TI.Start, 112) As TimesheetDate,
MAX(TI.Start) AS LastStart,
SUM(TI.DurationMins) AS DurationMins
FROM TimesheetItem TI
/* WHERE EXISTS (SELECT *
FROM Employee E
WHERE TI.EmployeeID = E.EmployeeID ) */
GROUP BY TI.EmployeeID, convert(char(8), TI.Start, 112)
HAVING SUM(TI.DurationMins) < 7.5*60) AS x
However, there are still a number of issues that are not entirely
clear to me:
1) The Work table does not seem to be relevant to the query at all.
We do need a work code to insert, but you have not mentioed how
we determine this. I've assumed that the 2 you used is a hard-coded
value.
2) The table TimesheetItem is problematic. It appears logical that
(Employee, Start) is unique, but there is no constraint saying
this as I could see. Your primary key is an IDENTITY column, but
if my assumption is correct, you would need that column.
3) In your original post, you restricted the query to employees of a
certain department. Here you seem to have relaxed that requirement.
For this reason, I've commented the part of the query that accesses
Employee.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I designed a databse for a company that makes time keeping equipment
(http://www.exaktime.com/). They sell mostly to the construction
trades because their hardware is mil spec quality and can be used by
illiterate workers if they are not color blind.
Based on actually doing such a data base, I think your design is
fundamentally flawed and you need to start over. You have no real
keys, the tables are not normalized, there are too many NULL-able
columns, repeated groups to destroy even the hope of 1NF, BIT flags,
absurd data element names like "<something>TypeID" and columns that are
far too large (ever see a CHAR(50) password?, CHAR(100) email
address?). Your temporal model is wrong (look up Rick Snodgrass and
temporal SQL for an entire book on why).
All you will get here is newgroup is some kludges to work around this
design. But that is not the right solution. Get some help and start
over; this design is useless and so flawed it will never have any data
integrity.|||
Erland Sommarskog wrote:
> Will (billrigby@.hotmail.com) writes:
> > So, for example, the insert to make the Smith record compliant for 18
> > July would be
> > INSERT INTO TimesheetItem
> > (EmployeeID,
> > Start,
> > DurationMins,
> > WorkID)
> > VALUES
> > (1,
> > '18 July 2005 11:30:00',
> > 270,
> > 2)
> > Can I do this without using a cursor? I apologise if this is badly
> > expressed - I'm doing my best!
> Sure, no need for a cursor:
> INSERT INTO TimesheetItem
> (EmployeeID,
> Start,
> DurationMins,
> WorkID)
> SELECT EmployeeID, dateadd(MINUTE, DurationMins, LastStart),
> 7.5 * 60 - DurationMins, 2
> FROM (SELECT TI.EmployeeID,
> convert(char(8), TI.Start, 112) As TimesheetDate,
> MAX(TI.Start) AS LastStart,
> SUM(TI.DurationMins) AS DurationMins
> FROM TimesheetItem TI
> /* WHERE EXISTS (SELECT *
> FROM Employee E
> WHERE TI.EmployeeID = E.EmployeeID ) */
> GROUP BY TI.EmployeeID, convert(char(8), TI.Start, 112)
> HAVING SUM(TI.DurationMins) < 7.5*60) AS x
Many thanks for this - works a treat!
> However, there are still a number of issues that are not entirely
> clear to me:
> 1) The Work table does not seem to be relevant to the query at all.
> We do need a work code to insert, but you have not mentioed how
> we determine this. I've assumed that the 2 you used is a hard-coded
> value.
See my response to CELKO below.
> 2) The table TimesheetItem is problematic. It appears logical that
> (Employee, Start) is unique, but there is no constraint saying
> this as I could see. Your primary key is an IDENTITY column, but
> if my assumption is correct, you would need that column.
> 3) In your original post, you restricted the query to employees of a
> certain department. Here you seem to have relaxed that requirement.
> For this reason, I've commented the part of the query that accesses
> Employee.
I did this for simplicity with regard to the barebones CREATE TABLE and
INSERT stuff I sent. I felt that the Department was a "red herring".
Many thanks for your help.
Edward|||
--CELKO-- wrote:
> I designed a databse for a company that makes time keeping equipment
> (http://www.exaktime.com/). They sell mostly to the construction
> trades because their hardware is mil spec quality and can be used by
> illiterate workers if they are not color blind.
> Based on actually doing such a data base, I think your design is
> fundamentally flawed and you need to start over. You have no real
> keys, the tables are not normalized, there are too many NULL-able
> columns, repeated groups to destroy even the hope of 1NF, BIT flags,
> absurd data element names like "<something>TypeID" and columns that are
> far too large (ever see a CHAR(50) password?, CHAR(100) email
> address?). Your temporal model is wrong (look up Rick Snodgrass and
> temporal SQL for an entire book on why).
I'm relieved to be able to say that I didn't design the database! The
DDL that I posted upthread was simplified to illustrate the problem.
If you have time, I should be extremely interested in your comments on
the REAL data design, which I post below.
In any case, many thanks for time taken.
Edward
CREATE TABLE [dbo].[TimesheetItem] (
[ItemID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [int] NOT NULL ,
[TypeID] [int] NOT NULL ,
[Start] [smalldatetime] NOT NULL ,
[DurationMins] [int] NOT NULL ,
[WorkID] [int] NULL ,
[WorkComponentID] [int] NULL ,
[WorkItemID] [int] NULL ,
[Notes] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OffSite] [tinyint] NULL ,
[TravelTo] [smalldatetime] NULL ,
[TravelToMins] [int] NULL ,
[TravelFrom] [smalldatetime] NULL ,
[TravelFromMins] [int] NULL ,
[TravelMileage] [int] NULL ,
[NonChargeableMins] [int] NULL ,
[OTAuthorisedID] [int] NULL ,
[OTAuthorisedDate] [smalldatetime] NULL ,
[Abroad] [bit] NULL ,
[InconvAllowance] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ApprovalID] [int] NULL ,
[AprovalDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[BankHolidays] (
[BankHoliday] [smalldatetime] NOT NULL ,
[Notes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Department] (
[DepartmentID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Notes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Employee] (
[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Surname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DepartmentID] [int] NOT NULL ,
[JobDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NULL ,
[DefaultRatePerHour] [smallmoney] NULL ,
[EmailAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[UserGroupID] [int] NOT NULL ,
[Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastLogon] [datetime] NULL ,
[PasswordChange] [smalldatetime] NULL ,
[PreviousPassword1] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword2] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword3] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword4] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword5] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[HolidayAllocation] (
[EmployeeID] [int] NOT NULL ,
[HolidayYear] [int] NOT NULL ,
[DaysAllocated] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Invoice] (
[InvoiceID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkOrderID] [int] NULL ,
[InvoiceNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InvoiceValue] [money] NULL ,
[InvoiceDate] [datetime] NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[RecurringWorkItem] (
[PKID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[WorkComponentID] [int] NULL ,
[RefID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TypeID] [int] NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AssignedEmployeeID] [int] NULL ,
[RecurStart] [smalldatetime] NULL ,
[RecurEnd] [smalldatetime] NULL ,
[RecurWeekly] [bit] NULL ,
[RecurMonthly] [bit] NULL ,
[RecurMonthlyDayNo] [int] NULL ,
[RecurMonthlyWeekDayOrder] [int] NULL ,
[RecurMonday] [bit] NULL ,
[RecurTuesday] [bit] NULL ,
[RecurWednesday] [bit] NULL ,
[RecurThursday] [bit] NULL ,
[RecurFriday] [bit] NULL ,
[RecurSaturday] [bit] NULL ,
[RecurSunday] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[UserGroup] (
[UserGroupID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccessTimesheetEntry] [int] NULL ,
[AccessTimesheetAdmin] [int] NULL ,
[AccessTimesheetReview] [int] NULL ,
[AccessWorkAdmin] [int] NULL ,
[AccessEmployeeAdmin] [int] NULL ,
[AccessContacts] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WeekProfile] (
[WeekProfileID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [int] NOT NULL ,
[PeriodStart] [smalldatetime] NOT NULL ,
[PeriodEnd] [smalldatetime] NULL ,
[Profile] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DailyHours] [decimal](18, 2) NOT NULL ,
[DailyValidation] [bit] NOT NULL ,
[StartTime] [datetime] NULL ,
[LunchStartTime] [datetime] NULL ,
[LunchDurationMins] [int] NULL ,
[WeeklyHours] [decimal](18, 2) NOT NULL ,
[WeeklyValidation] [bit] NOT NULL ,
[MondayHours] [decimal](18, 2) NOT NULL ,
[TuesdayHours] [decimal](10, 2) NOT NULL ,
[WednesdayHours] [decimal](10, 2) NOT NULL ,
[ThursdayHours] [decimal](10, 2) NOT NULL ,
[FridayHours] [decimal](10, 2) NOT NULL ,
[SaturdayHours] [decimal](10, 2) NOT NULL ,
[SundayHours] [decimal](18, 2) NOT NULL ,
[WeekDayValidation] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WeekProfileTemplate] (
[PKID] [int] IDENTITY (1, 1) NOT NULL ,
[Profile] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DailyHours] [decimal](10, 2) NOT NULL ,
[DailyValidation] [bit] NOT NULL ,
[StartTime] [datetime] NULL ,
[LunchStartTime] [datetime] NULL ,
[LunchDurationMins] [int] NULL ,
[WeeklyHours] [decimal](10, 2) NOT NULL ,
[WeeklyValidation] [bit] NOT NULL ,
[MondayHours] [decimal](18, 2) NOT NULL ,
[TuesdayHours] [decimal](18, 2) NOT NULL ,
[WednesdayHours] [decimal](18, 2) NOT NULL ,
[ThursdayHours] [decimal](18, 2) NOT NULL ,
[FridayHours] [decimal](18, 2) NOT NULL ,
[SaturdayHours] [decimal](18, 2) NOT NULL ,
[SundayHours] [decimal](18, 2) NOT NULL ,
[WeekDayValidation] [bit] NOT NULL ,
[Notes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Work] (
[WorkID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkTypeID] [int] NULL ,
[WorkCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Chargeable] [bit] NOT NULL ,
[ClientID] [int] NULL ,
[ClientContactID] [int] NULL ,
[Entered] [smalldatetime] NULL ,
[ApprovalRequired] [tinyint] NULL ,
[SQ_BlockID] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkComponent] (
[WorkComponentID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[Component] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkComponentDefaults] (
[WorkComponentDefaultID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkTypeID] [int] NOT NULL ,
[Component] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkEmployeeRate] (
[WorkEmployeeRateID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NULL ,
[EmployeeID] [int] NULL ,
[RateMultipiler] [float] NULL ,
[RatePerHour] [smallmoney] NULL ,
[OffSiteOnly] [tinyint] NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkItem] (
[WorkItemID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[WorkComponentID] [int] NULL ,
[RefID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TypeID] [int] NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Scheduled] [smalldatetime] NULL ,
[PriorityID] [int] NULL ,
[StatusID] [int] NULL ,
[ResolutionID] [int] NULL ,
[PhaseID] [int] NULL ,
[AreaID] [int] NULL ,
[VersionID] [int] NULL ,
[VersionReleasedID] [int] NULL ,
[WorkOrderID] [int] NULL ,
[AllocatedHours] [real] NULL ,
[PercentComplete] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkItemAreaLUT] (
[WorkItemAreaID] [int] IDENTITY (1, 1) NOT NULL ,
[Area] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sequence] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkItemAssignment] (
[WorkItemID] [int] NOT NULL ,
[EmployeeID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkItemPhaseLUT] (
[WorkItemPhaseID] [int] IDENTITY (1, 1) NOT NULL ,
[Phase] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sequence] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkItemPriorityLUT] (
[PriorityID] [int] IDENTITY (1, 1) NOT NULL ,
[Priority] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Sequence] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkItemResolutionLUT] (
[WorkItemResolutionID] [int] IDENTITY (1, 1) NOT NULL ,
[Resolution] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[IsFinal] [bit] NOT NULL ,
[Sequence] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkItemStatusLUT] (
[WorkItemStatusID] [int] IDENTITY (1, 1) NOT NULL ,
[Status] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[IsFinal] [bit] NULL ,
[Sequence] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkItemTypeLUT] (
[WorkItemTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkTypeID] [int] NULL ,
[Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Sequence] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkOTRate] (
[WorkOTRateID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[WorkDay] [int] NOT NULL ,
[TimeFrom] [smalldatetime] NOT NULL ,
[TimeTo] [smalldatetime] NOT NULL ,
[RateMultipiler] [float] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkOTRateDefaults] (
[PKID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkDay] [int] NOT NULL ,
[TimeFrom] [datetime] NULL ,
[TimeTo] [datetime] NULL ,
[RateMultipiler] [float] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkOrder] (
[WorkOrderID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[OrderNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[OrderDate] [datetime] NOT NULL ,
[OrderValue] [money] NOT NULL ,
[FixedPrice] [bit] NOT NULL ,
[Prepaid] [bit] NOT NULL ,
[AllocatedHours] [int] NULL ,
[RatePerHour] [money] NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SQ_BlockID] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkType] (
[WorkTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DepartmentID] [int] NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WorkItemPriority] [bit] NOT NULL ,
[WorkItemStatus] [bit] NOT NULL ,
[WorkItemResolution] [bit] NOT NULL ,
[WorkItemPhase] [bit] NOT NULL ,
[WorkItemArea] [bit] NOT NULL ,
[WorkItemVersion] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkVersion] (
[WorkVersionID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NULL ,
[Version] [float] NULL ,
[ReleaseDate] [smalldatetime] NULL ,
[Notes] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TimesheetItem] WITH NOCHECK ADD
CONSTRAINT [PK_TimesheetItem] PRIMARY KEY CLUSTERED
(
[ItemID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[BankHolidays] WITH NOCHECK ADD
CONSTRAINT [PK_BankHolidays] PRIMARY KEY CLUSTERED
(
[BankHoliday]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Department] WITH NOCHECK ADD
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
[DepartmentID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[HolidayAllocation] WITH NOCHECK ADD
CONSTRAINT [PK_HolidayAllocation] PRIMARY KEY CLUSTERED
(
[EmployeeID],
[HolidayYear]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Invoice] WITH NOCHECK ADD
CONSTRAINT [PK_Invoices] PRIMARY KEY CLUSTERED
(
[InvoiceID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[RecurringWorkItem] WITH NOCHECK ADD
CONSTRAINT [PK_RecurringWorkItem] PRIMARY KEY CLUSTERED
(
[PKID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[UserGroup] WITH NOCHECK ADD
CONSTRAINT [PK_UserGroup] PRIMARY KEY CLUSTERED
(
[UserGroupID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WeekProfile] WITH NOCHECK ADD
CONSTRAINT [PK_HoursProfile] PRIMARY KEY CLUSTERED
(
[EmployeeID],
[PeriodStart]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WeekProfileTemplate] WITH NOCHECK ADD
CONSTRAINT [PK_HoursProfileTemplate] PRIMARY KEY CLUSTERED
(
[PKID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Work] WITH NOCHECK ADD
CONSTRAINT [PK_Work] PRIMARY KEY CLUSTERED
(
[WorkID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkComponent] WITH NOCHECK ADD
CONSTRAINT [PK_WorkComponent] PRIMARY KEY CLUSTERED
(
[WorkComponentID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkComponentDefaults] WITH NOCHECK ADD
CONSTRAINT [PK_WorkTypeComponentDefaults] PRIMARY KEY CLUSTERED
(
[WorkComponentDefaultID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkEmployeeRate] WITH NOCHECK ADD
CONSTRAINT [PK_WorkEmployeeRate] PRIMARY KEY CLUSTERED
(
[WorkEmployeeRateID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItem] WITH NOCHECK ADD
CONSTRAINT [PK_WorkActivity] PRIMARY KEY CLUSTERED
(
[WorkItemID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemAreaLUT] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemAreaLUT] PRIMARY KEY CLUSTERED
(
[WorkItemAreaID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemAssignment] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemAssignment] PRIMARY KEY CLUSTERED
(
[WorkItemID],
[EmployeeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemPhaseLUT] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemPhaseLUT] PRIMARY KEY CLUSTERED
(
[WorkItemPhaseID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemPriorityLUT] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemPriorityLUT] PRIMARY KEY CLUSTERED
(
[PriorityID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemResolutionLUT] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemResolutionLUT] PRIMARY KEY CLUSTERED
(
[WorkItemResolutionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemStatusLUT] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemStatusLUT] PRIMARY KEY CLUSTERED
(
[WorkItemStatusID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemTypeLUT] WITH NOCHECK ADD
CONSTRAINT [PK_WorkActivityType] PRIMARY KEY CLUSTERED
(
[WorkItemTypeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkOTRate] WITH NOCHECK ADD
CONSTRAINT [PK_WorkOTRate] PRIMARY KEY CLUSTERED
(
[WorkOTRateID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkOTRateDefaults] WITH NOCHECK ADD
CONSTRAINT [PK_WorkOTRateDefaults] PRIMARY KEY CLUSTERED
(
[PKID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkOrder] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemOrder] PRIMARY KEY CLUSTERED
(
[WorkOrderID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkType] WITH NOCHECK ADD
CONSTRAINT [PK_WorkType] PRIMARY KEY CLUSTERED
(
[WorkTypeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkVersion] WITH NOCHECK ADD
CONSTRAINT [PK_WorkVersion] PRIMARY KEY CLUSTERED
(
[WorkVersionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[TimesheetItem] WITH NOCHECK ADD
CONSTRAINT [DF_TimesheetItem_TypeID] DEFAULT (0) FOR [TypeID],
CONSTRAINT [DF_TimesheetItem_DurationMins] DEFAULT (0) FOR
[DurationMins],
CONSTRAINT [DF_TimesheetItem_OffSite] DEFAULT (0) FOR [OffSite],
CONSTRAINT [DF_TimesheetItem_TravelToMins] DEFAULT (0) FOR
[TravelToMins],
CONSTRAINT [DF_TimesheetItem_TravelFromMins] DEFAULT (0) FOR
[TravelFromMins],
CONSTRAINT [DF_TimesheetItem_NonChargeableMins] DEFAULT (0) FOR
[NonChargeableMins],
CONSTRAINT [DF_TimesheetItem_Abroad] DEFAULT (0) FOR [Abroad],
CONSTRAINT [DF_TimesheetItem_Reviewed] DEFAULT (0) FOR [ApprovalID],
CONSTRAINT [CK_TimesheetItem] CHECK ([TypeID] like '[0-3]' and
[OffSite] like '[0-1]')
GO
ALTER TABLE [dbo].[Department] WITH NOCHECK ADD
CONSTRAINT [IX_DepartmentName] UNIQUE NONCLUSTERED
(
[Name]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[RecurringWorkItem] WITH NOCHECK ADD
CONSTRAINT [DF_RecurringWorkItem_RecurWeekly] DEFAULT (0) FOR
[RecurWeekly],
CONSTRAINT [DF_RecurringWorkItem_RecurMonthly] DEFAULT (0) FOR
[RecurMonthly],
CONSTRAINT [DF_RecurringWorkItem_RecurMonday] DEFAULT (0) FOR
[RecurMonday],
CONSTRAINT [DF_RecurringWorkItem_RecurTuesday] DEFAULT (0) FOR
[RecurTuesday],
CONSTRAINT [DF_RecurringWorkItem_RecurWednesday] DEFAULT (0) FOR
[RecurWednesday],
CONSTRAINT [DF_RecurringWorkItem_RecurThursday] DEFAULT (0) FOR
[RecurThursday],
CONSTRAINT [DF_RecurringWorkItem_RecurFriday] DEFAULT (0) FOR
[RecurFriday],
CONSTRAINT [DF_RecurringWorkItem_RecurFriday1] DEFAULT (0) FOR
[RecurSaturday],
CONSTRAINT [DF_RecurringWorkItem_RecurSaturday1] DEFAULT (0) FOR
[RecurSunday]
GO
ALTER TABLE [dbo].[UserGroup] WITH NOCHECK ADD
CONSTRAINT [DF_UserGroup_AccessTimesheetEntry] DEFAULT (0) FOR
[AccessTimesheetEntry],
CONSTRAINT [DF_UserGroup_AccessTimesheetAdmin] DEFAULT (0) FOR
[AccessTimesheetAdmin],
CONSTRAINT [DF_UserGroup_AccessTimesheetReview] DEFAULT (0) FOR
[AccessTimesheetReview],
CONSTRAINT [DF_UserGroup_AccessWorkCodeAdmin] DEFAULT (0) FOR
[AccessWorkAdmin],
CONSTRAINT [DF_UserGroup_AccessEmployeeAdmin] DEFAULT (0) FOR
[AccessEmployeeAdmin],
CONSTRAINT [DF_UserGroup_AccessContacts] DEFAULT (0) FOR
[AccessContacts],
CONSTRAINT [IX_UserGroupName] UNIQUE NONCLUSTERED
(
[Name]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_UserGroup] CHECK ([AccessTimesheetEntry] like '[0-3]'
and [AccessTimesheetAdmin] like '[0-3]' and [AccessWorkAdmin] like
'[0-3]' and [AccessEmployeeAdmin] like '[0-3]' and
[AccessTimesheetReview] like '[0-3]' and [AccessContacts] like '[0-3]')
GO
ALTER TABLE [dbo].[WeekProfile] WITH NOCHECK ADD
CONSTRAINT [DF_WeekProfile_DailyHours] DEFAULT (0) FOR [DailyHours],
CONSTRAINT [DF_WeekProfile_DailyHoursValidation] DEFAULT (0) FOR
[DailyValidation],
CONSTRAINT [DF_WeekProfile_WeekHours] DEFAULT (0) FOR [WeeklyHours],
CONSTRAINT [DF_WeekProfile_WeeklyHoursValidation] DEFAULT (0) FOR
[WeeklyValidation],
CONSTRAINT [DF_EmploymentProfile_MondayHours] DEFAULT (0) FOR
[MondayHours],
CONSTRAINT [DF_EmploymentProfile_TuesdayHours] DEFAULT (0) FOR
[TuesdayHours],
CONSTRAINT [DF_EmploymentProfile_WednesdayHours] DEFAULT (0) FOR
[WednesdayHours],
CONSTRAINT [DF_EmploymentProfile_ThursdayHours] DEFAULT (0) FOR
[ThursdayHours],
CONSTRAINT [DF_EmploymentProfile_FridayHours] DEFAULT (0) FOR
[FridayHours],
CONSTRAINT [DF_EmploymentProfile_SaturdayHours] DEFAULT (0) FOR
[SaturdayHours],
CONSTRAINT [DF_WeekProfile_SundayHours] DEFAULT (0) FOR [SundayHours],
CONSTRAINT [DF_WeekProfile_WeekDayValidation] DEFAULT (0) FOR
[WeekDayValidation]
GO
ALTER TABLE [dbo].[WeekProfileTemplate] WITH NOCHECK ADD
CONSTRAINT [DF_WeekProfileTemplate_DailyHours] DEFAULT (0) FOR
[DailyHours],
CONSTRAINT [DF_WeekProfileTemplate_DailyValidation] DEFAULT (0) FOR
[DailyValidation],
CONSTRAINT [DF_WeekProfileTemplate_WeekHours] DEFAULT (0) FOR
[WeeklyHours],
CONSTRAINT [DF_WeekProfileTemplate_WeeklyValidation] DEFAULT (0) FOR
[WeeklyValidation],
CONSTRAINT [DF_HoursProfileTemplates_MondayHours] DEFAULT (0) FOR
[MondayHours],
CONSTRAINT [DF_HoursProfileTemplates_TuesdayHours] DEFAULT (0) FOR
[TuesdayHours],
CONSTRAINT [DF_HoursProfileTemplates_WednesdayHours] DEFAULT (0) FOR
[WednesdayHours],
CONSTRAINT [DF_HoursProfileTemplates_ThursdayHours] DEFAULT (0) FOR
[ThursdayHours],
CONSTRAINT [DF_HoursProfileTemplates_FridayHours] DEFAULT (0) FOR
[FridayHours],
CONSTRAINT [DF_HoursProfileTemplates_SaturdayHours] DEFAULT (0) FOR
[SaturdayHours],
CONSTRAINT [DF_WeekProfileTemplate_SundayHours] DEFAULT (0) FOR
[SundayHours],
CONSTRAINT [DF_WeekProfileTemplate_StrictValidation] DEFAULT (0) FOR
[WeekDayValidation],
CONSTRAINT [IX_HoursProfileTemplate] UNIQUE NONCLUSTERED
(
[Profile]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Work] WITH NOCHECK ADD
CONSTRAINT [DF_Work_Chargeable] DEFAULT (0) FOR [Chargeable],
CONSTRAINT [DF_WorkItem_ReviewRequired] DEFAULT (0) FOR
[ApprovalRequired],
CONSTRAINT [IX_WorkCode] UNIQUE NONCLUSTERED
(
[WorkCode]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_Work] CHECK ([ApprovalRequired] like '[0-1]')
GO
ALTER TABLE [dbo].[WorkComponent] WITH NOCHECK ADD
CONSTRAINT [IX_WorkComponent] UNIQUE NONCLUSTERED
(
[WorkID],
[Component]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkComponentDefaults] WITH NOCHECK ADD
CONSTRAINT [IX_WorkTypeComponentDefaults] UNIQUE NONCLUSTERED
(
[WorkTypeID],
[Component]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkEmployeeRate] WITH NOCHECK ADD
CONSTRAINT [DF_WorkEmployeeRate_OffSiteOnly] DEFAULT (0) FOR
[OffSiteOnly],
CONSTRAINT [IX_WorkEmployeeRate] UNIQUE NONCLUSTERED
(
[WorkID],
[EmployeeID],
[OffSiteOnly]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_WorkEmployeeRate] CHECK ([OffSiteOnly] like '[0-1]')
GO
ALTER TABLE [dbo].[WorkItem] WITH NOCHECK ADD
CONSTRAINT [IX_WorkItemCode] UNIQUE NONCLUSTERED
(
[WorkItemID],
[RefID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_WorkItem] CHECK ([PercentComplete] like '[0-100]')
GO
ALTER TABLE [dbo].[WorkItemAreaLUT] WITH NOCHECK ADD
CONSTRAINT [IX_WorkItemAreaLUT] UNIQUE NONCLUSTERED
(
[Area]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemPhaseLUT] WITH NOCHECK ADD
CONSTRAINT [IX_WorkItemPhaseLUT] UNIQUE NONCLUSTERED
(
[Phase]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemPriorityLUT] WITH NOCHECK ADD
CONSTRAINT [IX_WorkItemPriorityLUT] UNIQUE NONCLUSTERED
(
[Priority]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemResolutionLUT] WITH NOCHECK ADD
CONSTRAINT [DF_WorkItemResolutionLUT_IsFinal] DEFAULT (0) FOR
[IsFinal]
GO
ALTER TABLE [dbo].[WorkItemStatusLUT] WITH NOCHECK ADD
CONSTRAINT [DF_WorkItemStatusLUT_IsFinal] DEFAULT (0) FOR [IsFinal],
CONSTRAINT [IX_WorkItemStatusLUT] UNIQUE NONCLUSTERED
(
[Status]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemTypeLUT] WITH NOCHECK ADD
CONSTRAINT [IX_WorkActivityType] UNIQUE NONCLUSTERED
(
[WorkTypeID],
[Type]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkOTRate] WITH NOCHECK ADD
CONSTRAINT [DF_WorkDayRate_WorkDay] DEFAULT (0) FOR [WorkDay],
CONSTRAINT [CK_WorkDayRate] CHECK ([WorkDay] like '[0-3]')
GO
ALTER TABLE [dbo].[WorkOTRateDefaults] WITH NOCHECK ADD
CONSTRAINT [DF_WorkDayRateDefaults_WorkDay] DEFAULT (0) FOR [WorkDay],
CONSTRAINT [CK_WorkDayRateDefaults] CHECK ([WorkDay] like '[0-3]')
GO
ALTER TABLE [dbo].[WorkOrder] WITH NOCHECK ADD
CONSTRAINT [DF_WorkOrder_FixedPrice] DEFAULT (0) FOR [FixedPrice],
CONSTRAINT [DF_WorkOrder_Prepaid] DEFAULT (0) FOR [Prepaid],
CONSTRAINT [IX_WorkItemOrder] UNIQUE NONCLUSTERED
(
[WorkID],
[OrderNo]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkType] WITH NOCHECK ADD
CONSTRAINT [DF_WorkType_WorkItemPriority] DEFAULT (0) FOR
[WorkItemPriority],
CONSTRAINT [DF_WorkType_WorkItemStatus] DEFAULT (0) FOR
[WorkItemStatus],
CONSTRAINT [DF_WorkType_WorkItemResolution] DEFAULT (0) FOR
[WorkItemResolution],
CONSTRAINT [DF_WorkType_WorkItemPhase] DEFAULT (0) FOR
[WorkItemPhase],
CONSTRAINT [DF_WorkType_WorkItemArea] DEFAULT (0) FOR [WorkItemArea],
CONSTRAINT [DF_WorkType_WorkItemVersion] DEFAULT (0) FOR
[WorkItemVersion],
CONSTRAINT [IX_WorkType] UNIQUE NONCLUSTERED
(
[Type]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkVersion] WITH NOCHECK ADD
CONSTRAINT [IX_WorkVersion] UNIQUE NONCLUSTERED
(
[Version]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[TimesheetItem] ADD
CONSTRAINT [FK_TimesheetItem_Employee] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
),
CONSTRAINT [FK_TimesheetItem_Employee1] FOREIGN KEY
(
[OTAuthorisedID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
),
CONSTRAINT [FK_TimesheetItem_WorkCode] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
)
GO
ALTER TABLE [dbo].[Employee] ADD
CONSTRAINT [FK_Employee_Department] FOREIGN KEY
(
[DepartmentID]
) REFERENCES [dbo].[Department] (
[DepartmentID]
),
CONSTRAINT [FK_Employee_UserGroup] FOREIGN KEY
(
[UserGroupID]
) REFERENCES [dbo].[UserGroup] (
[UserGroupID]
)
GO
ALTER TABLE [dbo].[HolidayAllocation] ADD
CONSTRAINT [FK_HolidayAllocation_Employee] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Invoice] ADD
CONSTRAINT [FK_Invoice_WorkOrder] FOREIGN KEY
(
[WorkOrderID]
) REFERENCES [dbo].[WorkOrder] (
[WorkOrderID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[RecurringWorkItem] ADD
CONSTRAINT [FK_RecurringWorkItem_Employee] FOREIGN KEY
(
[AssignedEmployeeID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
)
GO
ALTER TABLE [dbo].[WeekProfile] ADD
CONSTRAINT [FK_HoursProfile_Employee] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Work] ADD
CONSTRAINT [FK_Work_WorkType] FOREIGN KEY
(
[WorkTypeID]
) REFERENCES [dbo].[WorkType] (
[WorkTypeID]
)
GO
ALTER TABLE [dbo].[WorkComponent] ADD
CONSTRAINT [FK_WorkComponent_Work] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[WorkComponentDefaults] ADD
CONSTRAINT [FK_WorkTypeComponentDefaults_WorkType] FOREIGN KEY
(
[WorkTypeID]
) REFERENCES [dbo].[WorkType] (
[WorkTypeID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[WorkEmployeeRate] ADD
CONSTRAINT [FK_WorkEmployeeRate_Employee] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
),
CONSTRAINT [FK_WorkEmployeeRate_Work] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[WorkItem] ADD
CONSTRAINT [FK_WorkItem_Work] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_WorkItem_WorkComponent] FOREIGN KEY
(
[WorkComponentID]
) REFERENCES [dbo].[WorkComponent] (
[WorkComponentID]
),
CONSTRAINT [FK_WorkItem_WorkItemAreaLUT] FOREIGN KEY
(
[AreaID]
) REFERENCES [dbo].[WorkItemAreaLUT] (
[WorkItemAreaID]
),
CONSTRAINT [FK_WorkItem_WorkItemPhaseLUT] FOREIGN KEY
(
[PhaseID]
) REFERENCES [dbo].[WorkItemPhaseLUT] (
[WorkItemPhaseID]
),
CONSTRAINT [FK_WorkItem_WorkItemPriorityLUT] FOREIGN KEY
(
[PriorityID]
) REFERENCES [dbo].[WorkItemPriorityLUT] (
[PriorityID]
),
CONSTRAINT [FK_WorkItem_WorkItemResolutionLUT] FOREIGN KEY
(
[ResolutionID]
) REFERENCES [dbo].[WorkItemResolutionLUT] (
[WorkItemResolutionID]
),
CONSTRAINT [FK_WorkItem_WorkItemStatusLUT] FOREIGN KEY
(
[StatusID]
) REFERENCES [dbo].[WorkItemStatusLUT] (
[WorkItemStatusID]
),
CONSTRAINT [FK_WorkItem_WorkItemType] FOREIGN KEY
(
[TypeID]
) REFERENCES [dbo].[WorkItemTypeLUT] (
[WorkItemTypeID]
),
CONSTRAINT [FK_WorkItem_WorkOrder] FOREIGN KEY
(
[WorkOrderID]
) REFERENCES [dbo].[WorkOrder] (
[WorkOrderID]
),
CONSTRAINT [FK_WorkItem_WorkVersion] FOREIGN KEY
(
[VersionID]
) REFERENCES [dbo].[WorkVersion] (
[WorkVersionID]
),
CONSTRAINT [FK_WorkItem_WorkVersion1] FOREIGN KEY
(
[VersionReleasedID]
) REFERENCES [dbo].[WorkVersion] (
[WorkVersionID]
)
GO
ALTER TABLE [dbo].[WorkItemAssignment] ADD
CONSTRAINT [FK_WorkItemAssignment_Employee] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
),
CONSTRAINT [FK_WorkItemAssignment_WorkItem] FOREIGN KEY
(
[WorkItemID]
) REFERENCES [dbo].[WorkItem] (
[WorkItemID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[WorkItemTypeLUT] ADD
CONSTRAINT [FK_WorkItemTypeLUT_WorkType] FOREIGN KEY
(
[WorkTypeID]
) REFERENCES [dbo].[WorkType] (
[WorkTypeID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[WorkOTRate] ADD
CONSTRAINT [FK_WorkOTRate_Work] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[WorkOrder] ADD
CONSTRAINT [FK_Work_WorkOrder] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[WorkType] ADD
CONSTRAINT [FK_WorkType_Department] FOREIGN KEY
(
[DepartmentID]
) REFERENCES [dbo].[Department] (
[DepartmentID]
)
GO
ALTER TABLE [dbo].[WorkVersion] ADD
CONSTRAINT [FK_WorkVersion_Work] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
) ON DELETE CASCADE
GO|||It only gets worse -- repeated groups for the days of the week, numeric
codes stored as strings, etc.!
I hope you fired the moron who did this to you. But at this point,the
best thing is to design a relational schema, back the data out of this
mess as best you can (the lack of data integrity is going to make that
a bit harder than you think) and start over.
vantages you pin point in the