Dynamic date comparisons - 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)

Dynamic date comparisons

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2002-05-17 : 06:59:24

I have a table: dbo.Progress
ProjCode, char(10) - (Project Code)
PStart, smalldatetime - (Planned Start date)
AStart, smalldatetime - (Actual Start date)
PSurvey, smalldatetime - (Planned Survey date)
ASurvey, smalldatetime - (Actual Survey date)
PDesign, smalldatetime - (Planned Design date)
ADesign, smalldatetime - (Actual Design date)
PConstruct, smalldatetime - (Planned Construction date)
AConstruct, smalldatetime - (Actual Construction date)
PEnd, smalldatetime - (Planned End date)
AEnd, smalldatetime - (Actual End date)

I need to be able to get a list of all projects that are behind schedule by a specified time in weeks. ie if any actual date is greater than the planned date by the specified time then that project code must be listed as behind schedule.

What complicates the issue is that this table gets populated piece meal and a project that has made up time, ie the last captured milestone is within the planned date and specified time, then must not be flagged even though a previous milestone was behind schedule.
ie The date comparison must only be made for the last milestone, per project, where and actual date has been filled in.

The idea is to create a stored proc with one parameter(int) which is a weekly time period to do the comparison on and it will list those projects that are behind schedule. ie Show me all the projects who's latest captured milestone is behind schedule by more than 1 week, 2 weeks etc.

Any help would be greatly appreciated.
Thanks

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-17 : 07:41:30

Something like this, maybe:

DECLARE @WeeksBehind int
SET @WeeksBehind=2

SELECT ProjCode, BehindSchedule =
FROM #Progress
WHERE DateDiff(week, AStart, PStart) = @WeeksBehind

You will have to filter out the project milestones.
I cannot see what you consider a milestone from the table definition.

Edited by - YellowBug on 05/17/2002 07:44:57

Scott
Posting Yak Master

145 Posts

Posted - 2002-05-17 : 08:20:31

Sorry the milestones are:
Start, Survey, Design, Construction, End

That statement would for the base of something a little more complex, I would presume.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-17 : 08:35:28

CREATE PROCEDURE OffScheduleByWeeks
@WeeksBehind int
AS
SELECT ProjCode,
BehindSchedule = DateDiff(week, AStart, PStart)
FROM (SELECT ProjCode,....) as milestone
WHERE DateDiff(week, AStart, PStart) = @WeeksBehind
GO

The SELECT statement for the milestone "table"
will be something like:
SELECT max(Dates) FROM Progress GROUP BY ProjCode

Basically
1. Get the contents of an "as-is" milestone table
2. Then extract those not on schedule.

HTH.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources