SQL Help - Determine Status dynamically

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)

SQL Help - Determine Status dynamically

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2006-05-26 : 13:25:23

I actually have SQL that works for this, but the problem is that the SQL I write begins to bog down bigtime the more projects I need to pullback. So, I am looking to smarter people for some insight. Thanks for looking.

Ok, so here is what I am trying to accomplish. I have a table (ddl below) that holds Projects and Events, and Planned Completion Dates and Completed Dates for each Event. I need to Dynamically determine a projects status. Project Status is not stored, it should be a result.

What I need to do is this.

I need to pull the last CompletedDate for a given Project.

1. If last CompletedDate <= PlanCompletionDate Then Project Status = 'Ontime'
2. If last CompletedDate > PlanCompletionDate Then Project Status = 'Late'
3. If Todays Date > Current PlanCompletionDate (MIN(CompletedDate) Is Null) Then Project Status = 'Late'
4. If last CompletedDate <= PlanCompletionDate But ANY PlanCompletionDate > its Corresponding EstimatedCompletionDate Then Project Status = 'Danger'

I not only need to do this for single Projects, but I need to get totals by Project.CompanyID or Project.CustomerID. Meaning I need to dynamically determine a projects given status, and return how many are Ontime, Late, or Danger for Company A, Company B

CREATE TABLE [dbo].[ProjectEvent](
[ProjectID] [int] NOT NULL,
[EventID] [int] NOT NULL,
[EventOrder] [int] NULL,
[CorrectiveActionTypeID] [int] NULL,
[EstimatedCompletionDate] [datetime] NULL,
[PlanCompletionDate] [datetime] NULL,
[CompletedDate] [datetime] NULL,
[LeadTime] [int] NOT NULL,
[CorrectiveActionNote] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CorrectiveActionCost] [money] NULL,
[CorrectiveActionPartyResponsible] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [PK__ProjectEvent__48EFCE0F] PRIMARY KEY CLUSTERED
(
[ProjectID] ASC,
[EventID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Project](
[ProjectID] [int] IDENTITY(1,1) NOT NULL,
[ProjectName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SeasonID] [int] NULL,
[CustomerID] [int] NOT NULL,
[ProductCategoryID] [int] NULL,
[CompanyID] [int] NOT NULL,
[ShipDate] [datetime] NULL,
[DateCreated] [datetime] NULL,
[SalesAmount] [money] NULL,
[ItemNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateModified] [datetime] NULL,
[ModifiedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ItemDescription] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CostAmount] [money] NULL,
[MarginAmount] [money] NULL,
[CustomerOrderNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VendorOrderNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CompletedDate] [datetime] NULL,
CONSTRAINT [PK__Project__451F3D2B] PRIMARY KEY CLUSTERED
(
[ProjectID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [IX_Project] UNIQUE NONCLUSTERED
(
[ProjectName] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-26 : 14:13:23

U have done only the Point 2 of the following link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Please state the Question clearly. And provide some DML as in the Point 3 of the above doc.
And give the desired Output.

Its not clear whether u need
last CompletedDate
or
Status

and that also based on what ?

Srinika

Harry C
Posting Yak Master

148 Posts

Posted - 2006-05-26 : 15:07:40

Here is the whole shabang

Adn reiterated, here is what I need to do.. I need to get s ProjectStatus as the result from the below Stored procedure..

Ok, so here is what I am trying to accomplish. I have a table (ddl below) that holds Projects and Events, and Planned Completion Dates and Completed Dates for each Event. I need to Dynamically determine a projects status. Project Status is not stored, it should be a result.

What I need to do is this.

I need to pull the last CompletedDate for a given Project.

1. If last CompletedDate <= PlanCompletionDate Then Project Status = 'Ontime'
2. If last CompletedDate > PlanCompletionDate Then Project Status = 'Late'
3. If Todays Date > Current PlanCompletionDate (MIN(CompletedDate) Is Null) Then Project Status = 'Late'
4. If last CompletedDate <= PlanCompletionDate But ANY PlanCompletionDate > its Corresponding EstimatedCompletionDate Then Project Status = 'Danger'

I not only need to do this for single Projects, but I need to get totals by Project.CompanyID or Project.CustomerID. Meaning I need to dynamically determine a projects given status, and return how many are Ontime, Late, or Danger for Company A, Company B

BEGIN TRANSACTION

CREATE TABLE [#Project](
[ProjectID] [int] IDENTITY(1,1) NOT NULL,
[ProjectName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SeasonID] [int] NULL,
[CustomerID] [int] NOT NULL,
[ProductCategoryID] [int] NULL,
[CompanyID] [int] NOT NULL,
[ShipDate] [datetime] NULL,
[DateCreated] [datetime] NULL,
[SalesAmount] [money] NULL,
[ItemNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateModified] [datetime] NULL,
[ModifiedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ItemDescription] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CostAmount] [money] NULL,
[MarginAmount] [money] NULL,
[CustomerOrderNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VendorOrderNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CompletedDate] [datetime] NULL,
CONSTRAINT [PK__#Project__451F3D2B] PRIMARY KEY CLUSTERED
(
[ProjectID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [IX_#Project] UNIQUE NONCLUSTERED
(
[ProjectName] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [#ProjectEvent](
[ProjectID] [int] NOT NULL,
[EventID] [int] NOT NULL,
[EventOrder] [int] NULL,
[CorrectiveActionTypeID] [int] NULL,
[EstimatedCompletionDate] [datetime] NULL,
[PlanCompletionDate] [datetime] NULL,
[CompletedDate] [datetime] NULL,
[LeadTime] [int] NOT NULL,
[CorrectiveActionNote] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CorrectiveActionCost] [money] NULL,
[CorrectiveActionPartyResponsible] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [PK__#ProjectEvent__48EFCE0F] PRIMARY KEY CLUSTERED
(
[ProjectID] ASC,
[EventID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO [#Project]
([ProjectName]
,[SeasonID]
,[CustomerID]
,[ProductCategoryID]
,[CompanyID]
,[ShipDate]
,[DateCreated]
,[SalesAmount]
,[ItemNumber]
,[DateModified]
,[ModifiedBy]
,[ItemDescription]
,[CostAmount]
,[MarginAmount]
,[CustomerOrderNumber]
,[VendorOrderNumber]
,[CompletedDate])
VALUES
('TestProject'
,1
,2
,3
,4
,'9/18/2006'
,GetDate()
,100.00
,'Item-1001'
,GetDate()
,'test'
,NULL
,50.00
,50.00
,NULL
,NULL
,NULL)

INSERT INTO #ProjectEvent ([ProjectID],[EventID],[EventOrder],[CorrectiveActionTypeID],[EstimatedCompletionDate],[CompletedDate],[LeadTime],[CorrectiveActionNote],[CorrectiveActionCost],[CorrectiveActionPartyResponsible],[PlanCompletionDate]) SELECT (SELECT ProjectID FROM #Project),13, 3, NULL, '2006-04-28 00:00:00.000', '2006-04-27 00:00:00.000', 1, NULL,NULL, NULL, '2006-04-28 00:00:00.000'
INSERT INTO #ProjectEvent ([ProjectID],[EventID],[EventOrder],[CorrectiveActionTypeID],[EstimatedCompletionDate],[CompletedDate],[LeadTime],[CorrectiveActionNote],[CorrectiveActionCost],[CorrectiveActionPartyResponsible],[PlanCompletionDate]) SELECT (SELECT ProjectID FROM #Project), 14, 6, NULL, '2006-05-04 00:00:00.000', '2006-05-05 00:00:00.000', 6, NULL, NULL, NULL, '2006-05-04 00:00:00.000'
INSERT INTO #ProjectEvent ([ProjectID],[EventID],[EventOrder],[CorrectiveActionTypeID],[EstimatedCompletionDate],[CompletedDate],[LeadTime],[CorrectiveActionNote],[CorrectiveActionCost],[CorrectiveActionPartyResponsible],[PlanCompletionDate]) SELECT (SELECT ProjectID FROM #Project), 15, 9, NULL, '2006-06-05 00:00:00.000', NULL, 5, NULL, 32, NULL, '2006-06-05 00:00:00.000'
INSERT INTO #ProjectEvent ([ProjectID],[EventID],[EventOrder],[CorrectiveActionTypeID],[EstimatedCompletionDate],[CompletedDate],[LeadTime],[CorrectiveActionNote],[CorrectiveActionCost],[CorrectiveActionPartyResponsible],[PlanCompletionDate]) SELECT (SELECT ProjectID FROM #Project), 16, 12, NULL, '2006-06-12 00:00:00.000', NULL, 3, NULL, 7, NULL, '2006-06-12 00:00:00.000'
INSERT INTO #ProjectEvent ([ProjectID],[EventID],[EventOrder],[CorrectiveActionTypeID],[EstimatedCompletionDate],[CompletedDate],[LeadTime],[CorrectiveActionNote],[CorrectiveActionCost],[CorrectiveActionPartyResponsible],[PlanCompletionDate]) SELECT (SELECT ProjectID FROM #Project), 17, 15, NULL, '2006-06-14 00:00:00.000', NULL, 3, NULL, 2, NULL, '2006-06-14 00:00:00.000'
INSERT INTO #ProjectEvent ([ProjectID],[EventID],[EventOrder],[CorrectiveActionTypeID],[EstimatedCompletionDate],[CompletedDate],[LeadTime],[CorrectiveActionNote],[CorrectiveActionCost],[CorrectiveActionPartyResponsible],[PlanCompletionDate]) SELECT (SELECT ProjectID FROM #Project), 20, 18, NULL, '2006-08-13 00:00:00.000', NULL, 6, NULL, 60, NULL, '2006-08-13 00:00:00.000'
INSERT INTO #ProjectEvent ([ProjectID],[EventID],[EventOrder],[CorrectiveActionTypeID],[EstimatedCompletionDate],[CompletedDate],[LeadTime],[CorrectiveActionNote],[CorrectiveActionCost],[CorrectiveActionPartyResponsible],[PlanCompletionDate]) SELECT (SELECT ProjectID FROM #Project), 21, 21, NULL, '2006-09-17 00:00:00.000', NULL, 8, NULL, 35, NULL, '2006-09-17 00:00:00.000'
INSERT INTO #ProjectEvent ([ProjectID],[EventID],[EventOrder],[CorrectiveActionTypeID],[EstimatedCompletionDate],[CompletedDate],[LeadTime],[CorrectiveActionNote],[CorrectiveActionCost],[CorrectiveActionPartyResponsible],[PlanCompletionDate]) SELECT (SELECT ProjectID FROM #Project), 22, 24, NULL, '2006-09-18 00:00:00.000', NULL, 2, NULL, 1, NULL, '2006-09-18 00:00:00.000'

SELECT * FROM #ProjectEvent
DROP Table #Project
DROP Table #ProjectEvent

ROLLBACK TRANSACTION

Harry C
Posting Yak Master

148 Posts

Posted - 2006-05-26 : 15:19:51

/* Param List */
DECLARE @CustomerID int
DECLARE @CompanyID int
DECLARE @StatusCode varchar(20)

SET @CustomerID = 398
SET @CompanyID = 5
SET @StatusCode = NULL

CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
ProjectID int,
ProjectName varchar(50),
EventName varchar(50),
EventOrder int,
LastCompleteCorrectiveActionID int,
LastCompletedDate datetime,
BackGroundColor varchar(20),
LastCompleteEstimatedDate datetime,
FirstIncompleteEstimatedDate datetime,
PlanCompletionDate datetime,
FirstIncompleteCorrectiveActionID int,
CorrectiveActionName varchar(50),
PartyResponsible varchar(50),
CompanyID int,
CustomerID int,
CostAmount decimal
)
--copy the actual project data in, along with default status
INSERT INTO #TempTable
(
ProjectID,
ProjectName, --*
EventName, --*
EventOrder,
LastCompleteCorrectiveActionID,
LastCompletedDate,
BackGroundColor,
LastCompleteEstimatedDate,
FirstIncompleteEstimatedDate,
PlanCompletionDate,
FirstIncompleteCorrectiveActionID,
CorrectiveActionName, --*
PartyResponsible, --*
CompanyID,
CustomerID,
CostAmount
)
SELECT PE.ProjectId,
P.ProjectName,
E.EventName,
PE.EventOrder,
PE.CorrectiveActionTypeId AS LastCompleteCorrectiveActionID,
PE.CompletedDate AS LastCompletedDate,
'#33ff00' AS BackGroundColor,
EstimatedCompletionDate AS LastCompleteEstimatedDate,
(SELECT TOP 1 EstimatedCompletionDate FROM ProjectEvent PE2
WHERE PE2.ProjectId = PE.ProjectId AND EventOrder > PE.EventOrder
Order By EventOrder) AS FirstIncompleteEstimatedDate,
(SELECT TOP 1 PlanCompletionDate FROM ProjectEvent PE2
WHERE PE2.ProjectId = PE.ProjectId AND CompletedDate Is Null
AND PlanCompletionDate > EstimatedCompletionDate) AS PlanCompletionDate,
(SELECT TOP 1 CorrectiveActionTypeId FROM ProjectEvent PE3
WHERE PE3.ProjectId = PE.ProjectId AND EventOrder > PE.EventOrder
Order By EventOrder) AS FirstIncompleteCorrectiveActionID,
C.[Name] AS CorrectiveActionName,
E.PartyResponsible,
(SELECT CompanyID FROM Project WHERE Project.ProjectID=PE.ProjectID) AS CompanyID,
(SELECT CustomerID FROM Project WHERE Project.ProjectID=PE.ProjectID) AS CustomerID,
(SELECT CostAmount FROM Project WHERE Project.ProjectID=PE.ProjectID) AS CostAmount
FROM ProjectEvent PE
INNER JOIN Project P
ON P.ProjectID = PE.ProjectID
INNER JOIN Event E
ON E.EventID = PE.EventID
LEFT JOIN CorrectiveActionType C
ON C.CorrectiveActionTypeID = PE.CorrectiveActionTypeID
WHERE --PE.CompletedDate = (SELECT MAX(CompletedDate) FROM ProjectEvent WHERE ProjectId = PE.ProjectId)
--AND
PE.EventOrder = (SELECT MAX(EventOrder) FROM ProjectEvent WHERE ProjectId = PE.ProjectId AND CompletedDate Is Not Null)
AND PE.ProjectID IN (SELECT ProjectID FROM Project WHERE CompanyID=@CompanyID AND CustomerID=@CustomerID)
AND p.CompanyID=@CompanyID AND p.CustomerID=@CustomerID

INSERT INTO #TempTable
(
ProjectID,
ProjectName, --*
EventName, --*
EventOrder,
LastCompleteCorrectiveActionID,
LastCompletedDate,
BackGroundColor,
LastCompleteEstimatedDate,
FirstIncompleteEstimatedDate,
PlanCompletionDate,
FirstIncompleteCorrectiveActionID,
CorrectiveActionName, --*
PartyResponsible, --*
CompanyID,
CustomerID,
CostAmount
)
SELECT PE.ProjectId,
P.ProjectName,
E.EventName,
PE.EventOrder,
NULL as FirstIncompleteCorrectiveActionID,
PE.CompletedDate as LastCompletedDate,
'#33ff00' AS BackGroundColor,
NULL as FirstIncompleteEstimatedDate,
NULL as PlanCompletionDate,
EstimatedCompletionDate as LastCompleteEstimatedDate,
PE.CorrectiveActionTypeId as LastCompleteCorrectiveActionID,
C.[Name] AS CorrectiveActionName,
E.PartyResponsible,
(SELECT CompanyID FROM Project WHERE Project.ProjectID=PE.ProjectID) AS CompanyID,
(SELECT CustomerID FROM Project WHERE Project.ProjectID=PE.ProjectID) AS CustomerID,
(SELECT CostAmount FROM Project WHERE Project.ProjectID=PE.ProjectID) AS CostAmount
FROM ProjectEvent PE
INNER JOIN Project P
ON P.ProjectID = PE.ProjectID
INNER JOIN Event E
ON E.EventID = PE.EventID
LEFT JOIN CorrectiveActionType C
ON C.CorrectiveActionTypeID = PE.CorrectiveActionTypeID
WHERE PE.CompletedDate IS NULL
AND EventOrder = (SELECT MIN(EventOrder) FROM ProjectEvent WHERE ProjectId = PE.ProjectId)
AND PE.ProjectID IN (SELECT ProjectID FROM Project WHERE CompanyID=@CompanyID AND CustomerID=@CustomerID)
AND p.CompanyID=@CompanyID AND p.CustomerID=@CustomerID
AND p.ProjectID NOT IN (SELECT #TempTable.ProjectID FROM #TempTable)
ORDER BY PE.ProjectId

DECLARE @Date datetime
SET @Date = convert(varchar(20),GetDate(),101) --Returns JUST the Date portion of GetDate()

--set status to #ff3366 where the end date is past with no
--completion, or the complete date is late
UPDATE #TempTable SET BackGroundColor = '#ff3366'
WHERE FirstIncompleteEstimatedDate<@Date--(MaxCompletedDate > MaxCompletedEstimatedCompletionDate
--AND MinEstCompletedDate>@Date)
OR
(LastCompletedDate > LastCompleteEstimatedDate )
--AND MinEstCompletedDate>@Date)

UPDATE #TempTable SET BackGroundColor = '#ccffff'
WHERE (FirstIncompleteEstimatedDate>=@Date
AND FirstIncompleteCorrectiveActionID Is Not Null AND BackGroundColor <> '#ff3366')

--UPDATE #TempTable SET BackGroundColor = '#ffff99'
--WHERE (FirstIncompleteEstimatedDate>=@Date
-- AND LastCompletedDate>LastCompleteEstimatedDate AND FirstIncompleteCorrectiveActionID Is Null)

UPDATE #TempTable SET BackGroundColor = '#ffff99'
WHERE PlanCompletionDate Is Not Null

IF @StatusCode Is Null
BEGIN
SELECT ProjectID,
ProjectName, --*
EventName, --*
EventOrder,
LastCompleteCorrectiveActionID,
LastCompletedDate,
BackGroundColor,
LastCompleteEstimatedDate,
FirstIncompleteEstimatedDate,
PlanCompletionDate,
FirstIncompleteCorrectiveActionID,
CorrectiveActionName, --*
PartyResponsible, --*
CompanyID,
CustomerID,
CostAmount
FROM #TempTable ORDER BY ProjectName
END

IF @StatusCode Is Not Null
DECLARE @BGColor varchar(20)
IF @StatusCode = 'ontime' SET @BGColor='#33ff00'
IF @StatusCode = 'actionset' SET @BGColor='#ccffff'
IF @StatusCode = 'danger' SET @BGColor='#ffff99'
IF @StatusCode = 'late' SET @BGColor='#ff3366'
BEGIN
SELECT ProjectID,
ProjectName, --*
EventName, --*
EventOrder,
LastCompleteCorrectiveActionID,
LastCompletedDate,
BackGroundColor,
LastCompleteEstimatedDate,
FirstIncompleteEstimatedDate,
PlanCompletionDate,
FirstIncompleteCorrectiveActionID,
CorrectiveActionName, --*
PartyResponsible, --*
CompanyID,
CustomerID,
CostAmount
FROM #TempTable
WHERE BackGroundColor = @BGColor ORDER BY ProjectName
END

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
BEGIN
DROP TABLE #TempTable
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources