|
|
Posted by Dan Guzman on 12/22/05 19:25
> So, is there any way of retaining the requirement that the
> fldStartTimeCollectedID and the fldEndTimeCollectedID columns may not
> contain the same value in a single row, UNLESS that value is NULL in
> which case all is hunky dory. I should add that the clients don't much
> care for Triggers (and neither do I for that matter).
There are a couple of methods to accomplish this. One method is with a
trigger. Another, with SQL 2000 and above, is using an index view including
non-null values instead of a unique constraint:
CREATE VIEW v_tblTSRuleApplied
WITH SCHEMABINDING
AS
SELECT fldStartTimeCollectedID, fldEndTimeCollectedID
FROM dbo.tblTSRuleApplied
WHERE fldStartTimeCollectedID IS NOT NULL AND
fldEndTimeCollectedID IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX v_tblTSRuleApplied_cdx
ON v_tblTSRuleApplied(fldStartTimeCollectedID, fldEndTimeCollectedID)
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
<teddysnips@hotmail.com> wrote in message
news:1135265109.464713.76030@f14g2000cwb.googlegroups.com...
> Table DDL below:
>
> The tables I have contain Timesheet information. Each row in the
> tblTSCollected table contains an entry for an employee into the
> timesheet system, specifically by scanning the barcode on their badge.
>
> A whole bunch of business logic periodically attempts to "pair" these
> into logically matched scans. For example, some employees will scan in
> and out of a single place of work. For these there will be a row
> written to the tblTSRuleApplied table which contains, inter alia and
> some redundant data, the fldCollectedID for the two rows. The earlier
> will be put into the fldStartTimeCollectedID, and the later into the
> fldEndTimeCollectedID. Some employees will clock on at their base,
> then perform sub-duties at different locations during the day, and
> clock off at their home base at the end of their shift. For these, the
> system would identify the outer records as a matching pair, and then
> pair up inner records by location.
>
> However, if the employee fails to enter a valid "clocking in and out"
> pair (for example, if they clock in at the wrong location) the system
> needs to generate a "dummy" "clocking in and out" record for the
> payroll department. Ideally, this would have NULL values in the
> fldStartTimeCollectedID and fldEndTimeCollectedID columns. This would
> alert a user in a different part of the system, where missing
> timesheets were being arbitrated, that an employee appeared to have
> failed to clock in for that day. Of course, the user could see
> on-screen that they had clocked in, but at an incorrect location.
>
> Unfortunately, the database designer is not here for the moment (he was
> knocked off his bicycle recently), but he put a unique index on the
> tblTSRuleApplied table that prevents the same value being entered into
> the fldStartTimeCollectedID and fldEndTimeCollectedID columns. This is
> generally A Good Thing, since we don't want the same timesheet scan to
> form both a "clocking on" event and a "clocking off" event.
>
> So, is there any way of retaining the requirement that the
> fldStartTimeCollectedID and the fldEndTimeCollectedID columns may not
> contain the same value in a single row, UNLESS that value is NULL in
> which case all is hunky dory. I should add that the clients don't much
> care for Triggers (and neither do I for that matter).
>
> Many thanks if you are able to help.
>
> Edward
>
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINT
> FK_tblTSRuleApplied_tblTSCollected
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected1]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINT
> FK_tblTSRuleApplied_tblTSCollected1
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblTSArbAccept_tblTSRuleApplied]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblTSArbAccept] DROP CONSTRAINT
> FK_tblTSArbAccept_tblTSRuleApplied
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblTSCollected_tblTSRuleApplied]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblTSCollected] DROP CONSTRAINT
> FK_tblTSCollected_tblTSRuleApplied
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblTSCollected]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblTSCollected]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblTSRuleApplied]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblTSRuleApplied]
> GO
>
> CREATE TABLE [dbo].[tblTSCollected] (
> [fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,
> [fldEmployeeID] [int] NULL ,
> [fldLocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [fldTimeStamp] [datetime] NULL ,
> [fldRuleAppliedID] [int] NULL ,
> [fldBarCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [fldProcessed] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[tblTSRuleApplied] (
> [fldEmpRuleID] [int] NOT NULL ,
> [fldRuleAppliedID] [int] IDENTITY (1, 1) NOT NULL ,
> [fldStartTime] [datetime] NULL ,
> [fldEndTime] [datetime] NULL ,
> [fldStartTimeCollectedID] [int] NULL ,
> [fldEndTimeCollectedID] [int] NULL ,
> [fldStartArbStatus] [smallint] NULL ,
> [fldEndArbStatus] [smallint] NULL ,
> [fldDurationArbStatus] [smallint] NULL ,
> [fldPrimary] [smallint] NOT NULL ,
> [fldDateEntered] [datetime] NULL ,
> [fldEnteredBy] [int] NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tblTSCollected] WITH NOCHECK ADD
> CONSTRAINT [DF_tblTSCollected_fldProcessed] DEFAULT (0) FOR
> [fldProcessed],
> CONSTRAINT [PK_tblTimesheetCollected] PRIMARY KEY CLUSTERED
> (
> [fldCollectedID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tblTSRuleApplied] WITH NOCHECK ADD
> CONSTRAINT [DF_tblTSRuleApplied_fldPrimary] DEFAULT (1) FOR
> [fldPrimary],
> CONSTRAINT [PK_tblTSRuleApplied] PRIMARY KEY CLUSTERED
> (
> [fldRuleAppliedID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> CONSTRAINT [IX_tblTSRuleApplied_1] UNIQUE NONCLUSTERED
> (
> [fldStartTimeCollectedID],
> [fldEndTimeCollectedID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tblTSCollected] ADD
> CONSTRAINT [FK_tblTSCollected_tblEmployee1] FOREIGN KEY
> (
> [fldEmployeeID]
> ) REFERENCES [dbo].[tblEmployee] (
> [fldEmployeeID]
> ),
> CONSTRAINT [FK_tblTSCollected_tblLocation] FOREIGN KEY
> (
> [fldLocationCode]
> ) REFERENCES [dbo].[tblLocation] (
> [fldLocationCode]
> ),
> CONSTRAINT [FK_tblTSCollected_tblTSRuleApplied] FOREIGN KEY
> (
> [fldRuleAppliedID]
> ) REFERENCES [dbo].[tblTSRuleApplied] (
> [fldRuleAppliedID]
> )
> GO
>
> ALTER TABLE [dbo].[tblTSRuleApplied] ADD
> CONSTRAINT [FK_tblTSRuleApplied_tblTSCollected] FOREIGN KEY
> (
> [fldStartTimeCollectedID]
> ) REFERENCES [dbo].[tblTSCollected] (
> [fldCollectedID]
> ),
> CONSTRAINT [FK_tblTSRuleApplied_tblTSCollected1] FOREIGN KEY
> (
> [fldEndTimeCollectedID]
> ) REFERENCES [dbo].[tblTSCollected] (
> [fldCollectedID]
> ),
> CONSTRAINT [FK_tblTSRuleApplied_tblTSDurationStatus] FOREIGN KEY
> (
> [fldDurationArbStatus]
> ) REFERENCES [dbo].[tblTSDurationStatus] (
> [fldStatus]
> ),
> CONSTRAINT [FK_tblTSRuleApplied_tblTSEmpRules] FOREIGN KEY
> (
> [fldEmpRuleID]
> ) REFERENCES [dbo].[tblTSEmpRules] (
> [fldEmpRuleID]
> ),
> CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus] FOREIGN KEY
> (
> [fldStartArbStatus]
> ) REFERENCES [dbo].[tblTSTimeStatus] (
> [fldStatus]
> ),
> CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus1] FOREIGN KEY
> (
> [fldEndArbStatus]
> ) REFERENCES [dbo].[tblTSTimeStatus] (
> [fldStatus]
> )
> GO
>
[Back to original message]
|