UPDATE Query Help - 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

Transact-SQL (2000)

UPDATE Query Help

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2006-06-02 : 10:00:45

I just cant figure out this calculation..I basically need to grab the prior tasks CompletedDate, or PRojectedCompletionDate and recalc all thje subsequent ProjectedCompletionDates...Thoughts??

Here is an example of what my table looks like. In the Update Statement below, I bolded the SET statement with Psuedo Code to shwo what I am trying to do.

http://img269.imageshack.us/img269/3548/projectevent1xs.jpg

The Before Data. In this, all the RequiredCompletionDates and ProjectedCompletionDates are exactly the same, and there are no CompletedDates

EventID :: Lead Time :: Required Completion Date :: Projected Completion Date :: Completed Date
1 :: 1 :: 4/21/2006 :: 4/21/2006 :: NULL

2 :: 6 :: 4/27/2006 :: 4/27/2006 :: NULL

3 :: 32 :: 5/29/2006 :: 5/29/2006 :: NULL

5 :: 7 :: 6/5/2006 :: 6/5/2006 :: NULL

7 :: 2 :: 6/7/2006 :: 6/7/2006 :: NULL

10 :: 60 :: 8/6/2006 :: 8/6/2006 :: NULL

14 :: 1 :: 8/7/2006 :: 8/7/2006 :: NULL

AFTER Update. So, EventID 1 gets a CompletedDate 4/22/2006, so We take 4/22/2006, and Add 6 (the LeadTime of EventID 2) and the new ProjectedCompletionDate for EventID 2 would be 4/28/2006. Now when EventID 2 gets a CompletedDate of 4/26/2006, we take that date, and add 32 to get 5/28/2006 for EventID 3. So, basically, if an Update occurs to a ProjectedCompletetionDate OR CompletedDate, we take that value, and update the Next Event

So, Pseudo Code is like this

UPDATE pe
SET pe.ProjectedCompletionDate = DateAdd(d,CurrentEventLeadTime,IsNull(PreviosEventsCompletedDate, PreviosEventsProjectedCompletionDate))
FROM ProjectEvent pe,
( SELECT p2.projectID, Max(i.EventOrder) AS maxOrder
FROM ProjectEvent AS p2, Inserted AS i
WHERE i.ProjectID = p2.ProjectID
GROUP BY p2.projectID ) AS PI
WHERE pe.ProjectID = PI.ProjectID
AND pe.EventOrder = PI.maxOrder

EventID :: Lead Time :: Required Completion Date :: Projected Completion Date :: Completed Date
1 :: 1 :: 4/21/2006 :: 4/21/2006 :: 4/22/2006

2 :: 6 :: 4/27/2006 :: 4/28/2006 :: 4/26/2006

3 :: 32 :: 5/29/2006 :: 5/28/2006 :: 5/28/2006

5 :: 7 :: 6/5/2006 :: 6/4/2006

7 :: 2 :: 6/7/2006 :: 6/6/2006

10 :: 60 :: 8/6/2006 :: 8/8/2006

14 :: 1 :: 8/7/2006 :: 8/9/2006

Harry C
Posting Yak Master

148 Posts

Posted - 2006-06-02 : 13:17:03

The highlighted portion is VERY Close. But, I somehow need to get the CompletedDate or PlanCompletionDate for JUST THE PREVIOUS row.. The query below is close, but gets the MAX for ANY rows. I just need to exact previous row. Please help if ya have the time. Thanks

SELECT pe.ProjectID, pe.EventID, pe.EventOrder, pe.EstimatedCompletionDate,
pe.PlanCompletionDate, pe.CompletedDate, pe.LeadTime,
[hl="yellow"] (SELECT MAX(IsNull(pe3.CompletedDate,pe3.PlanCompletionDate))
FROM ProjectEvent pe3
WHERE pe3.ProjectID = pe.ProjectID
AND pe3.EventOrder < pe2.EventOrder) as PrevCalcDate
[/hl]

FROM ProjectEvent pe
INNER JOIN
ProjectEvent pe2 ON
pe2.ProjectID = pe.ProjectID
AND
pe2.EventID = pe.EventID
WHERE pe.ProjectID = 38325

Harry C
Posting Yak Master

148 Posts

Posted - 2006-06-02 : 13:32:31

Ok, this works! But, I would apprecaite a look through to see if it could be simplified...

SELECT pe.ProjectID, pe.EventID, pe.EventOrder, pe.EstimatedCompletionDate,
pe.PlanCompletionDate, pe.CompletedDate, pe.LeadTime,
(SELECT MAX(IsNull(pe3.CompletedDate,pe3.PlanCompletionDate))
FROM ProjectEvent pe3
WHERE pe3.ProjectID = pe.ProjectID
AND pe3.EventOrder = (SELECT MAX(pe3.EventOrder)
FROM ProjectEvent pe3
WHERE pe3.ProjectID = pe.ProjectID
AND pe3.EventOrder < pe.EventOrder)) as PrevCalcDate

FROM ProjectEvent pe
INNER JOIN
ProjectEvent pe2 ON
pe2.ProjectID = pe.ProjectID
AND
pe2.EventID = pe.EventID
WHERE pe.ProjectID = 38325

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources