CREATE TABLE [dbo].[MobileCrewTime]
(
[MobileCrewTimeID] [bigint] NOT NULL IDENTITY(1, 1),
[CrewMemberFID] [int] NOT NULL,
[LocServFID] [int] NULL,
[MobileCrewTimeActivityFID] [int] NULL,
[StartTimeUtc] [datetime] NOT NULL,
[StartTimeZone] [smallint] NOT NULL,
[EndTimeUtc] [datetime] NULL,
[EndTimeZone] [smallint] NULL,
[IsApproved] [bit] NOT NULL,
[IsOverTime] [bit] NOT NULL,
[LastComment] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartTimeLocal] [datetime] NOT NULL,
[GpsLatitudeIn] [float] NULL,
[GpsLongitudeIn] [float] NULL,
[GpsLatitudeOut] [float] NULL,
[GpsLongitudeOut] [float] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MobileCrewTime] ADD CONSTRAINT [CK_MobileCrewTime_LocServActivity] CHECK (([LocServFID] IS NOT NULL OR [MobileCrewTimeActivityFID] IS NOT NULL))
GO
ALTER TABLE [dbo].[MobileCrewTime] ADD CONSTRAINT [PK_MobileCrewTime] PRIMARY KEY NONCLUSTERED  ([MobileCrewTimeID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_MobileCrewTime_CrewMemberFID_LocServFID] ON [dbo].[MobileCrewTime] ([CrewMemberFID], [LocServFID]) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_MobileCrewTime_CrewMemberFID_StartTimeUtc] ON [dbo].[MobileCrewTime] ([CrewMemberFID], [StartTimeUtc]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_MobileCrewTime_LocServFID_CrewMemberFID] ON [dbo].[MobileCrewTime] ([LocServFID], [CrewMemberFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_MobileCrewTime_StartTimeLocal_CrewMemberFID] ON [dbo].[MobileCrewTime] ([StartTimeLocal], [CrewMemberFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MobileCrewTime] ADD CONSTRAINT [FK_MobileCrewTime_CrewMemberFID] FOREIGN KEY ([CrewMemberFID]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[MobileCrewTime] ADD CONSTRAINT [FK_MobileCrewTime_LocServFID] FOREIGN KEY ([LocServFID]) REFERENCES [dbo].[LocServ] ([PriKey])
GO
ALTER TABLE [dbo].[MobileCrewTime] ADD CONSTRAINT [FK_MobileCrewTime_MobileCrewTimeActivityFID] FOREIGN KEY ([MobileCrewTimeActivityFID]) REFERENCES [dbo].[MobileCrewTimeActivity] ([MobileCrewTimeActivityID])
GO
GRANT SELECT ON  [dbo].[MobileCrewTime] TO [MssExec]
GRANT INSERT ON  [dbo].[MobileCrewTime] TO [MssExec]
GRANT DELETE ON  [dbo].[MobileCrewTime] TO [MssExec]
GRANT UPDATE ON  [dbo].[MobileCrewTime] TO [MssExec]
GO