我想避免使用光标！ [英] I want to avoid using a cursor, please!

问题描述

应用程序是工作跟踪/时间表数据库。


工作增量存储在TimesheetItem表中。这个

除其他外，包含员工当天在特定项目上花费的工作代码，开始和持续时间

。


由于各种原因，网络支持部门的一些员工没有完成标准的7.5小时标准，所以对于每个网络支持我需要b
更新这些特定的日子，金额为

，总天数为7.5小时。


此SQL将为我提供所有TimesheetItem记录的列表对于

有关人员。


SELECT

TimesheetItem.TypeID，

[工作]。 WorkCode，

TimesheetItem.Start AS开始，

SUM（CAST（TimesheetItem.DurationMins AS float）/ 60）AS Hours

FROM

TimesheetItem LEFT OUTER JOIN

[工作] ON TimesheetItem.WorkID = [工作] .WorkID

WHERE

（TimesheetItem .EmployeeID IN

（SELECT EmployeeID FROM Employee WHERE DepartmentID = 2 ））

GROUP BY

TimesheetItem.TypeID，TimesheetItem.Start，[工作] .WorkCode

HAVING

（TimesheetItem.Start> = @FromDate）AND（TimesheetItem.Start< = @ToDate）

ORDER BY

TimesheetItem.Start


我需要的是按EmployeeID对这些记录进行分组，其中

每天累积的小时数是< 7.5，这样我就可以插入一个

增量来弥补差异。


我在家写这个，我不是可以访问表格来提供一个脚本，但是那里没有什么不好的。


希望有人可以提供帮助！


谢谢


爱德华

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

推荐答案

（te *** *****@hotmail.com）写道：

(te********@hotmail.com) writes:

这个SQL将为我提供有关
人员的所有TimesheetItem记录的列表。

SELECT
TimesheetItem.TypeID，
[工作] .WorkCode，
TimesheetItem.Start AS开始，
SUM（CAST（TimesheetItem.DurationMins AS float）/ 60）AS Hours
来自
TimesheetItem LEFT OUTER JOIN
[工作] ON TimesheetItem.WorkID = [工作] .WorkID
WHERE
（TimesheetItem.EmployeeID IN
（SELECT EmployeeID FROM Employee WHERE） DepartmentID = 2））
GROUP BY
TimesheetItem.TypeID，TimesheetItem .Start，[工作] .WorkCode
（TimesheetItem.Start> = @FromDate）和（TimesheetItem.Start< = @ToDate）
时间表。开始

我需要的是按EmployeeID对这些记录进行分组，其中
每天的累计小时数是< 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.

我有问题要抓住这个，因为在查询中你不是每个员工的分组结果，所以我看不出你怎么能够挖掘出工作时间少于7.5小时的员工。


以下是查询的修改版本，它使得员工 b $ b工作时间少于7.5小时：


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

来自TimesheetItem TI

LEFT JOIN [工作] W ON TI.WorkID = W.WorkID

WHERE EXISTS（SELECT *

来自员工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）

有7.5 - SUM（小时）> 1E-6

ORDER BY EmployeeID，convert（char（8），Start，112）


但这可以简化为：


SELECT TI.EmployeeID，convert（char（8），TI.Start，112），

SUM（CAST（TI.DurationMins AS float）/ 60）AS Hours

来自TimesheetItem TI

WHERE EXISTS（SELECT *

来自员工E

WHERE TI.EmployeeID = E.EmployeeID

和E：DepartmentID = 2）

AND TI.Start> = @FromDate

AND TI.Start< = @ToDate

GROUP BY TI.EmployeeID，EmployeeID，convert（char（8），Start，112）

有7.5 - SUM（小时）> 1E-6

ORDER BY EmployeeID，convert（char（8），Start，112）


然后我真的不明白你的意思使用这些信息。


也许你应该在星期一回来时可以访问

表，这样你也可以发布样本数据（如INSERT）声明）

和给出样本的预期结果。


-

Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se


SQL Server SP3联机丛书 http://www.microsoft.com/sql /techinf...2000/books.asp

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

请发帖DDL，这样人们就不用猜猜钥匙了，

约束，声明性参照完整性，数据类型等

您的架构是什么。样本数据也是一个好主意，同时还有明确的

规格。当你不让我们这么做时，你很难调试代码

看到它。


你通常会为每个事件建立一个包含开始和结束时间的时间表。

否则寻找一辈子可怕的左外自我加入thaqt

像胶水一样运行并且是婊子维持


A数据元素可以是一种类型或一种标识符，但它不可能两种情况都可以这两种情况。


另外，除非你喜欢，否则请避免使用DECOAL的FLOAT舍入错误。

如果你使用FLOAT，不要用整数除。

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--写道：

--CELKO-- wrote:

请发布DDL，以便人们不必猜测您的架构中的密钥，限制，声明性参照完整性，数据类型等。样本数据也是一个好主意，同时还有明确的规范。当你不让我们看到它时，调试代码非常困难。

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.

抱怨半翘起来。请查看底部脚本

相关表格。


Erland的第一个SQL语句修改如下（他的第二个，简化为

版本，在第7行给出错误 - 无效的列名称''小时''）

我想要的，或多或少，因为它给出了一个日期列表和

特定日工作时间少于7.5小时的员工。


SELECT

EmployeeID，

convert（char（8），

开始，112）作为TimesheetDate，

SUM（小时）

来自

（选择TI.EmployeeID，TI.TypeID，W.WorkCode，TI.Start AS Start，

SUM（CAST（TI.DurationMins AS float）/ 60）AS Hours

FROM TimesheetItem TI

LEFT JOIN [工作] 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）

有7.5 - SUM（小时）> 1E-6

ORDER BY EmployeeID，convert（char（8），Start，112）


这给出了这样的表格：


453 20011010 5.0

453 20011126 7.0

453 20020104 7.0

453 20020124 7.25

453 20020227 6.75

453 20020308 7.0

453 20020328 6.5

453 20020507 7.0

删除3,900行！


我需要做的是，对于每个相应的b / b
日的员工，是要添加一个额外的记录来提高小时

那天工作到7.5。我想了很久很难，没有光标就看不到任何

的方式。


到目前为止感谢


爱德华


下面的表定义：


CREATE TABLE [dbo]。[员工]（

[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，

[姓] ] [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，

[密码] [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，

[开始] [smalldat etime] NOT NULL，

[DurationMins] [int] NOT NULL，

[WorkID] [int] NULL，

[WorkComponentID] [ int] NULL，

[WorkItemID] [int] NULL，

[注释] [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] 。[工作]（

[WorkID] [int] IDENTITY（1,1）NOT NULL，

[WorkTypeID] [int] NULL，

[WorkCode] [varchar]（10）COLLATE SQL_Latin1_General_CP1_CI_AS NOT

NULL，

[摘要] [ varchar]（50）COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

，

[注释] [varchar]（200）COLLATE SQL_Latin1_General_CP1_CI_AS NULL，

[收费] [bit] NOT NULL，

[ClientID] [int] NULL，

[ClientContactID] [int] NULL，

[已输入] [ smalldatetime] NULL，

[ApprovalRequired] [tinyint] NULL，

[SQ_BlockID] [int] NULL

）ON [PRIMARY]

GO


ALTER TABLE [dbo]。[员工]没有检查添加

CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED

（

[员工ID]

）带有FILLFACTOR = 90 ON [PRIMARY]

GO


ALTER TABLE [dbo]。[TimesheetItem] WITH NOCHECK ADD

CONSTRAINT [PK_TimesheetItem] PRIMARY KEY CLUSTERED

（

[ItemID] ]

）与FILLFACTOR = 90 ON [主要]

GO


ALTER TABLE [dbo]。[工作]添加NOCHECK

CONSTRAINT [PK_Work ] PRIMARY KEY CLUSTERED

（

[工作ID]

）有FILLFACTOR = 90 ON [主要]

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 [国外]，

CONSTRAINT [DF_TimesheetItem_Reviewed] DEFAULT（0） FOR [ApprovalID]，

CONSTRAINT [CK_TimesheetItem]检查（[TypeID]如''[0-3]''和

[OffSite]如''[0- 1]''）

GO


ALTER TABLE [dbo]。[工作]添加NOCHECK

CONSTRAINT [DF_Work_Chargeable] DEFAULT（0）FOR [Chargeable]，

CONSTRAINT [DF_WorkItem_ReviewRequired] DEFAULT（0）FOR

[ApprovalRequired]，

CONSTRAINT [IX_WorkCode]独一无二的

（

[工作代码]

）带有FILLFACTOR = 90 ON [主要]，

CONSTRAINT [ CK_Work] CHECK（[ApprovalRequired]喜欢''[0-1]''）

GO


ALTER TABLE [dbo]。[Employee] ADD < br $>
CONSTRAINT [FK_Employee_Department] FOREIGN KEY

（

[部门代理]

）参考文献[dbo]。[部门] （

[部门ID]

），

CONSTRAINT [FK_Employee_UserGroup]外键

（

[UserGroupID]

）参考[dbo]。[UserGro up（

[UserGroupID]

）

GO


ALTER TABLE [dbo]。 [TimesheetItem] ADD

CONSTRAINT [FK_TimesheetItem_Employee] FOREIGN KEY

（

[EmployeeID]

）参考文献[dbo ]。[员工]（

[员工ID]

），

CONSTRAINT [FK_TimesheetItem_Employee1]外键

（

[OTAuthorisedID]

）参考[dbo]。[员工]（

[员工ID]

），

CONSTRAINT [FK_TimesheetItem_WorkCode]外键

（

[工作ID]

）参考[dbo]。[工作]（

[工作ID]

）

GO


ALTER TABLE [dbo]。[工作] ADD

CONSTRAINT [FK_Work_WorkType] FOREIGN KEY

（

[WorkTypeID]

）参考文献[dbo] 。[WorkType]（

[WorkTypeID]

）

GO

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:

453 20011010 5.0
453 20011126 7.0
453 20020104 7.0
453 20020124 7.25
453 20020227 6.75
453 20020308 7.0
453 20020328 6.5
453 20020507 7.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

这篇关于我想避免使用光标！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！