July 7, 2005 at 10:23 pm
I barely know where to start this requirement is so overly complex, but here goes
oIf 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).
oIf 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
July 8, 2005 at 10:26 am
How do you tell that you have later dates? Is it just by the date? Is there some other linkage? If it's just by date, then how would you ever have a green date if one date was red? all subsequent dates would be yellow then, wouldn't they?
July 8, 2005 at 11:11 am
http://www.imagehosting.us/?id=587624&type=viewpic - Here is the schema...
I need to get back all the events for a project. Say I have five events. If event 1 is late(EndDate > CompletedDate), then event 1 needs to be red, and all other events need to be yellow. But, if event 1 is ontime, or complete, then the next event to look at would be event 2. Then determine the status of that event(for example late='red'), and then all subsequent events will be yellow.
I could have a green event like the following
Name :: Color :: EndDate :: CompletedDate
Event One :: GREEN :: 7/2/2005 :: 7/1/2005
Event Two :: RED :: 7/5/2005 :: 7/6/2005
Event Three :: YELLOW :: 8/1/2005 :: NULL
Event Four :: YELLOW :: 8/10/2005 :: NULL
See, the first event was completed Ontime(Event One), so that one is green, but now I need to look at the next event(Event Two), if that one is defined as Completed Late(RED). Once I hit an event that is NOT green, then ALL other events are now Yellow.
Thanks
HC
July 8, 2005 at 11:24 am
What you are trying to do is Time Interval Arithmetic, it is not implemented in SQL Server only Oracle and PostgreSQL have it implemented. But Microsoft have resources that can help you. The Time Tracker starter kit have most of the code you need but not all is in T-SQL because the .NET FCL(framework class library) Calendar class is more comprehensive. The other two links created Timespan for SQL Server which will enable you to get time as you want it. Yes it is complicated but I can help, I Asp.net users with similar problems all the time. Timespan changes a day into seconds. Hope this helps.
http://www.stanford.edu/~bsuter/sql-datecomputations.html
http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-38-udf_DT_AddTime.htm
http://asp.net/TimeTrackerStarterKit/Docs/Docs.htm
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
July 8, 2005 at 2:37 pm
This is close, but not quite getting it for me. Anyone want to take another crack! Thanks alot
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 EstimatedCompletionDate
--UPDATE #TempTable SET BackGroundColor = 'LIGHTBLUE'
--WHERE (CompletedDate IS NOT NULL AND EstimatedCompletionDate < GETDATE())
--OR CompletedDate
(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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply