Internal SQL Server error on query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Internal SQL Server error on query

Author

Topic

dr00bie
Starting Member

1 Post

Posted - 2012-05-16 : 11:47:20

I have a database that records overtime worked. The table looks like this,


CREATE TABLE [OvertimeTracking] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[EmpID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OTDate] [smalldatetime] NULL ,
	[OTStartDate] [datetime] NULL ,
	[OTEndDate] [datetime] NULL ,
	[OTLocation] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OTHoursAdmin] [decimal](8, 2) NULL ,
	[OTHours] [decimal](8, 2) NULL ,
	[Comments] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Mandatory] [bit] NULL ,
	[Voluntary] [bit] NULL ,
	[FibData] [bit] NULL ,
	[Active] [bit] NULL CONSTRAINT [DF_OvertimeTracking_Active] DEFAULT (1),
	[InsertedDate] [datetime] NULL ,
	[InsertedBy] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[UpdatedDate] [datetime] NULL ,
	[UpdatedBy] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	CONSTRAINT [PK_OvertimeTracking] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

We use this database to find who has worked overtime recently and who has not.

Management has asked me to modify the database to track refusals to work overtime. I built a table,


CREATE TABLE [Refusals] (
	[UID] [int] IDENTITY (1, 1) NOT NULL ,
	[EmpID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RefusalDate] [smalldatetime] NULL ,
	[RLU] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Shift] [int] NULL ,
	CONSTRAINT [PK_Refusals] PRIMARY KEY  CLUSTERED 
	(
		[UID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

Now I need to write a query that shows the number of refusals since the last date that the employee worked overtime. I have the query built, but it is returning a "Internal SQL Server error on query."


SELECT E.EmpID, E.EmpLName + ', ' + EmpFName AS EmpName, 
ISNULL(SUM(CASE WHEN Active = 1 THEN OT.OTHours ELSE NULL END),0) As TotalOT, 
ISNULL(AVG(CASE WHEN Active = 1 THEN OT.OTHours ELSE NULL END),0) AS AverageOT, 
ISNULL(Max(OTDate),'1/1/1900') AS LastDate, P.PosDeptID, EmpSWVTCBegDate,
(SELECT COUNT(EmpID) FROM Refusals WHERE EmpID = E.EmpID AND RefusalDate > ISNULL(Max(OTDate),'1/1/1900')) AS RefusalCount
FROM OvertimeTracking OT 
RIGHT JOIN EmpCore.dbo.vuActiveEmployees E ON OT.EmpID = E.EmpID 
INNER JOIN EmpCore.dbo.tblPosition P ON E.PosID = P.PosID 
INNER JOIN EmpPersonal.dbo.tblEmpPersonalInfo EP ON E.EmpID = EP.EmpID
GROUP BY E.EmpID, E.EmpLName, E.EmpFName, P.PosDeptID, EmpSWVTCBegDate

Any ideas on what I can do to get around this issue?

Thanks,
Drew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources