Posted by pb648174 on 10/01/43 11:42

Ok, so here is the final solution as I have it now, sans the start/stop
stuff and references to other tables:

CREATE TABLE [dbo].[TimeSpentItem](
[Id] [int] IDENTITY(1,1) NOT NULL,
[PunchlistItemId] [int] NULL,
[ScheduleTaskId] [int] NULL,
CONSTRAINT [PK_TimeSpentItem] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[TimeSpentItem] WITH CHECK ADD CONSTRAINT
[CK_TimeSpentItem_ValidReference] CHECK (([PunchlistItemId] IS NOT
NULL OR [ScheduleTaskId] IS NOT NULL))
GO

CREATE TABLE [dbo].[TimeSpentActual](
[UserOrContactId] [int] NOT NULL,
[ForDate] [smalldatetime] NOT NULL,
[TimeSpentItemId] [int] NOT NULL,
[Minutes] [int] NOT NULL,
[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CreateUser] [int] NOT NULL,
[CreateTime] [smalldatetime] NOT NULL,
[UpdateUser] [int] NOT NULL,
[UpdateTime] [smalldatetime] NOT NULL,
CONSTRAINT [PK_TimeSpentActual_1] PRIMARY KEY CLUSTERED
(
[UserOrContactId] ASC,
[ForDate] ASC,
[TimeSpentItemId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[TimeSpentActual] WITH CHECK ADD CONSTRAINT
[FK_TimeSpentActual_TimeSpentItemId] FOREIGN KEY([TimeSpentItemId])
REFERENCES [dbo].[TimeSpentItem] ([Id])
GO
ALTER TABLE [dbo].[TimeSpentActual] WITH CHECK ADD CONSTRAINT
[CK_TimeSpentActual_ValidForDate] CHECK ((datepart(hour,[ForDate])=(0)
AND datepart(minute,[ForDate])=(0) AND datepart(second,[ForDate])=(0)))
GO
ALTER TABLE [dbo].[TimeSpentActual] WITH CHECK ADD CONSTRAINT
[CK_TimeSpentActual_ValidTime] CHECK ((isnull([Minutes],(0))<(1439)))

[Back to original message]