is there a better way? - SQL Server Forums

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

SQL Server Development (2000)

is there a better way?

Author

Topic

gsnk
Starting Member

24 Posts

Posted - 2002-06-11 : 14:30:57

I just inherited this query from a colleague. It looks so inefficient but I can't think of a better way to construct it either. Does somebody have an idea?

--================================
-- CONSTRUCT TABLES:

CREATE TABLE dbo.Tasks (
TaskID int IDENTITY (1, 1) NOT NULL ,
TaskListID int NOT NULL ,
TransferTaskID int NULL ,
ParentTaskID int NULL ,
StartDate smalldatetime NULL ,
ActualStartDate smalldatetime NULL ,
EndDate smalldatetime NULL ,
ActualEndDate smalldatetime NULL ,
Deviation AS (datediff(day,EndDate,ActualEndDate)) ,
Notes varchar (1000) NULL ,
PercentComplete smallint NOT NULL ,
TaskLevel int NULL ,
TaskLabel varchar (255) NULL ,
TaskDescr varchar (255) NULL ,
TaskLevelSortOrder int NOT NULL ,
Duration int NULL ,
DurationTypeID int NULL
) ON PRIMARY
GO

CREATE TABLE dbo.TaskLists (
TaskListID int IDENTITY (1, 1) NOT NULL ,
ProjectID int NULL ,
CompanyID int NOT NULL ,
SiteProjectID int NULL ,
EmployeeID int NULL ,
DateReleased smalldatetime NULL ,
Name varchar (50) NULL ,
Descr varchar (1000) NULL ,
IsTemplate bit NOT NULL
) ON PRIMARY
GO

--================================
-- QUERY:

DECLARE @id INT
SET @id = 1

SELECT TaskListID
,Name
,Descr
,(SELECT MIN(StartDate)
FROM Tasks
WHERE TaskListID = @id) AS StartDate
,(SELECT MAX(EndDate)
FROM Tasks
WHERE TaskListID = @id) AS EndDate
,(SELECT SUM(Deviation)
FROM Tasks
WHERE TaskListID = @id) AS Deviation
,(SELECT COUNT(TaskListID)
FROM Tasks
WHERE TaskListID = @id) AS TotalTasks
,(SELECT AVG(PercentComplete)
FROM Tasks
WHERE TaskListID = @id) AS AvgComplete
FROM TaskLists
WHERE TaskListID = @id

Gero

**It's better to light a candle than to curse the darkness.**

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-11 : 15:08:16

SELECT TL.TaskListID, TL.Name, TL.descr,
MIN(T.StartDate) AS StartDate,
MAX(T.EndDate) AS EndDate,
SUM(T.Deviation) AS Deviation,
COUNT(T.TaskListID) AS TotalTasks,
AVG(T.PercentComplete) AS AvgComplete
FROM TaskLists TL INNER JOIN Tasks T
ON TL.TaskListID=T.TaskListID
WHERE T.TaskListID = @id

izaltsman
A custom title

1139 Posts

Posted - 2002-06-11 : 15:52:05

Rob's statement will need a GROUP BY clause tacked onto it like so:


SELECT 
    TL.TaskListID, TL.Name, TL.descr, 
    MIN(T.StartDate) AS StartDate, 
    MAX(T.EndDate) AS EndDate, 
    SUM(T.Deviation) AS Deviation, 
    COUNT(T.TaskListID) AS TotalTasks,
    AVG(T.PercentComplete) AS AvgComplete 
FROM 
    TaskLists TL 
    INNER JOIN Tasks T 
        ON TL.TaskListID=T.TaskListID
WHERE 
    T.TaskListID = 1
GROUP BY 
    TL.TaskListID, TL.Name, TL.descr

gsnk
Starting Member

24 Posts

Posted - 2002-06-11 : 16:37:09

rob, thanks... I must have been brain-dead when I looked at that query. And, izaltsman, thanks! I had already figured that one out... ;)

Thanks for all your help!

Gero

**It's better to light a candle than to curse the darkness.**

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources