Dynamically selecting values - SQL help

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)

Dynamically selecting values - SQL help

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-08 : 08:16:01

I barely know where to start this requirement is so overly complex, but here goes

o If the current date is past the end date of an event, and the event does not have a completion date, the status of the event will indicate ‘late’(red) and each subsequent event will indicate ‘in danger of being late’(yellow).
o If the current date is past the end date and there is a completion date entered that is past the end date, the event will remain ‘late’(red) and all subsequent events will remain ‘in danger of being late’(yellow).

So, I have a CASE statement in my SQL, like so
CASE
WHEN ProjectEvent.EstimatedCompletionDate >= GetDate() THEN '#33cc66'
WHEN ProjectEvent.EstimatedCompletionDate < GetDate() THEN 'RED'
ELSE '#33cc66'
END AS BackGroundColor,

This only gets me so far, and will not get me to my end goal. The above takes each event separately...I need to look at them as a whole I think...

An example
Name :: Color :: EndDate :: CompletedDate
Event One :: RED :: 7/8/2005 :: NULL
Event Two :: YELLOW :: 7/28/2005 :: NULL
Event Three :: YELLOW :: 8/1/2005 :: NULL

The above SQL would give me colors of RED, GREEN, GREEN and there is no way I could even test for yellow...Is there some way I get get the status of the first event, and then set a color for all the rest while also watching out for this situation..

Name :: Color :: EndDate :: CompletedDate
Event One :: GREEN :: 7/5/2005 :: 7/4/2005
Event Two :: RED :: 7/8/2005 :: NULL
Event Three :: YELLOW :: 8/1/2005 :: NULL

Notice here that event one has a completed date that is before the end date, and now event two is late, and hence all subsequent events must be yellow. I know this is cumbersome...but any thoughts? Thanks alot

Harry C

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-08 : 08:44:11

You don't mention how events are related (which event follows the other)...

Are all events in the table representing a single string in time order collated by the EndDate or are there several separate event strings?

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-08 : 10:15:45

Here is the events schema
http://www.imagehosting.us/?id=587624&type=viewpic

ProjectEvent is the table in question...The eventOrder is populated from another table (not shown). but trust that the eventorder is always correct.

Anyway, I need to get somehow get a SQL Case(or something to that effect) that will display the data like I have in my first post...If you need anymore info, please ask. I am trying to be detailed and I really need to get this resolved today. Thanks so much

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-08 : 10:28:48

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

How do I know the order of events?

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-08 : 10:37:57

I'll be leaving soon, but the problem designing a CASE statement is that the table needs to be JOINED to itself in a manner that establishes the event sequence of every row.

It's hard to design the JOIN if I don't know the Table's columns and the relationship (StartDate?) that establishes the sequence. You have some reference to Event Name which seems to imply a simpler relationship. I can only guess with the information at hand.

I'm pretty sure you won't be able to design a CASE statement to solve your problem without first solving the JOIN / and establishing a clear Event sequence.

Good luck.

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-08 : 10:41:22

quote:
Originally posted by SamC

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

How do I know the order of events?

ProjectEvent has an EventOrder, which tells the order of the events. Make sense? Is that not what you mean?

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-08 : 16:21:23

This is close, but not quite getting it for me

The data comes back like this...not quite right!!

StatusColor :: EventName :: EstimatedCompletionDate :: CompletedDate
RED :: Event One :: 2005-06-15 :: 2005-06-16
GREEN :: Event Two :: 2005-07-01 :: 2005-06-30
RED :: Event Three :: 2005-07-08 :: 2005-07-09
GREEN :: Event Four :: 2005-08-06 :: 2005-07-30
Yellow :: Event Five :: 2005-08-12 :: NULL

All the events are rightly colored except for event five. Event 4 is green, and ontime, and hence event 5 should be green as well. But get updated to Yellow. How can I restrict that? So close. Thanks for looking!

BEGIN TRANSACTION

DECLARE @ProjectID int
SET @ProjectID = 10

CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
ProjectID int,
EventID int,
BackGroundColor varchar(20),
EventName varchar(100),
EventOrder int,
CorrectiveActionTypeID int,
EstimatedCompletionDate datetime,
CompletedDate datetime,
ModifiedBy varchar(20),
CompletedBy varchar(20),
LeadTime int,
CorrectiveActionName varchar(20),
CorrectiveActionNote varchar(1000),
CorrectiveActionCost money

)

--copy the actual project data in, along with default status
INSERT INTO #TempTable
SELECT ProjectEvent.ProjectID, ProjectEvent.EventID, 'GREEN', Event.EventName, ProjectEvent.EventOrder,
ProjectEvent.CorrectiveActionTypeID, ProjectEvent.EstimatedCompletionDate,
ProjectEvent.CompletedDate, ProjectEvent.ModifiedBy,
ProjectEvent.CompletedBy, ProjectEvent.LeadTime,
CorrectiveActionType.[Name] as CorrectiveActionName,ProjectEvent.CorrectiveActionNote, ProjectEvent.CorrectiveActionCost
FROM ProjectEvent
INNER JOIN
Event ON ProjectEvent.EventID = Event.EventID
LEFT JOIN
CorrectiveActionType ON ProjectEvent.CorrectiveActionTypeID = CorrectiveActionType.CorrectiveActionTypeID
WHERE ProjectEvent.ProjectID = @ProjectID
ORDER BY ProjectEvent.EventOrder

--set status to red where the end date is past with no
--completion, or the complete date is late
UPDATE #TempTable SET BackGroundColor = 'RED'
WHERE (CompletedDate IS NULL AND EstimatedCompletionDate < GETDATE())
OR CompletedDate > EstimatedCompletionDate

--UPDATE #TempTable SET BackGroundColor = 'LIGHTBLUE'
--WHERE (CompletedDate IS NOT NULL AND EstimatedCompletionDate < GETDATE())
--OR CompletedDate <= EstimatedCompletionDate

--the subquery gets the earliest project that is Late;
--set all projects later than this one to Yellow status
UPDATE #TempTable SET BackGroundColor = 'Yellow'
WHERE CompletedDate IS NULL
AND EstimatedCompletionDate >
(SELECT MAX(EstimatedCompletionDate) FROM #TempTable WHERE BackGroundColor IN ('RED','LIGHTBLUE'))

--Return the fruits of your labours.
SELECT ProjectID, EventID, BackGroundColor, EventName, EventOrder,
CorrectiveActionTypeID, EstimatedCompletionDate,
CompletedDate, ModifiedBy,
CompletedBy, LeadTime,
CorrectiveActionName,CorrectiveActionNote, CorrectiveActionCost FROM #TempTable
--AND EstimatedCompletionDate < GETDATE()
SELECT MAX(EstimatedCompletionDate) FROM #TempTable WHERE BackGroundColor IN ('RED','LIGHTBLUE')

ROLLBACK TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources