Some fom of ranked update????? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Some fom of ranked update?????

Author

Topic

greeny122229
Starting Member

25 Posts

Posted - 2009-04-23 : 10:39:27

Hi Guys n Gals,

Hoping someone can help with this, I have the following table in sql server 2000 sp4

Amend Id|Pred ID|Start_dt |EndDT |COST|Rank
---------------------------------------------
1 |0 |01/04/2009|06/04/2009|5 |1
1 |3 |Null |Null |5 |2
1 |9 |Null |Null |4 |3
4 |0 |01/04/2009|03/04/2009|2 |1

The table can contain multiple ammendments with muliple Pred_id's,
>start dt's are automatically entered for pred_id 0's with the current date.
>End DT is a sum of start_dt + cost

My question:
I need a way to update all the null dt values to show:
>start date, this would be the previous ranks end date
>end date, this would be the previous ranks start date plus this ranks cost

This needs to be done for all pred_id's in each ammendment id which has a pred_id > 0 from either a stored proc or some form of update query.

The example result for the above would be:
Amend Id|Pred ID|Start_dt |EndDT |COST|Rank
---------------------------------------------
1 |0 |01/04/2009|06/04/2009|5 |1
1 |3 |06/04/2009|11/04/2009|5 |2
1 |9 |11/04/2009|15/04/2009|4 |3
4 |0 |01/04/2009|03/04/2009|2 |1

Any assistance appreciated

Matt

P.s. i'm new to this forum :D

greeny122229
Starting Member

25 Posts

Posted - 2009-04-23 : 10:54:37

Is it possible to use a trigger?,

so lets say insert these records into a table and after each line the trigger checks the entered record:
>if the dates are null
>select max end_date from the table where the ammendment id is the same as the record just inserted
>Insert this into start_dt
>insert end date as this date plus the cost from inserted record.

Is this possible if so how do you do it? :D

greeny122229
Starting Member

25 Posts

Posted - 2009-04-23 : 11:23:20

I'm new to triggers and can't seem to get one to work, heres my attempt - what've i done wrong?:

CREATE TRIGGER trig_updatedts_INSERT
ON temp_tbl_moc_tasks
FOR INSERT
AS
SET NOCOUNT ON

IF (SELECT start_date FROM inserted) is null
BEGIN
UPDATE temp_tbl_moc_tasks t1
t1.Start_date = (SELECT max(end_date) FROM temp_tbl_moc_tasks)
t1.end_date = sum((SELECT max(end_date) FROM temp_tbl_moc_tasks) + (SELECT cost FROM inserted))
where moc_id = (SELECT MOC_ID FROM inserted) and amend_id = (SELECT amend_id FROM inserted) and TASK_id = (SELECT task_id FROM inserted)
END
go

greeny122229
Starting Member

25 Posts

Posted - 2009-04-23 : 11:51:09

i THINK I'M GETTING CLOSER - I NOW NEED IT TO LOOP THROUGH EACH RECORD INSERTED - HOW DO I DO IT?

CREATE TRIGGER trig_updatedts_INSERT
ON temp_tbl_moc_tasks
FOR INSERT
AS
SET NOCOUNT ON

IF (SELECT PREDECESSOR_ID FROM inserted) > 0
DECLARE @Count tinyint
SET @Count = (SELECT COUNT(*) FROM inserted WHERE PREDECESSOR_ID > 0)

BEGIN
UPDATE temp_tbl_moc_tasks SET
Start_date = (SELECT max(end_date) FROM temp_tbl_moc_tasks where moc_id = (SELECT MOC_ID FROM inserted) and amend_id = (SELECT amend_id FROM inserted))
where moc_id = (SELECT MOC_ID FROM inserted) and amend_id = (SELECT amend_id FROM inserted) and TASK_id = (SELECT task_id FROM inserted)

END

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-23 : 12:58:32

I only read yourfirst post. So, the code below I think will perform the update you require. But, the performance is probably going to be not so good.

DECLARE @Table TABLE 
(
	AmendID INT,
	PredID INT,
	StartDT DATETIME,
	EndDT DATETIME,
	Cost INT,
	Rank INT
)

INSERT @Table
SELECT 1 ,0 ,'20060401','20060406',5 ,1
UNION ALL SELECT 1 ,3 ,Null ,Null ,5 ,2
UNION ALL SELECT 1 ,9 ,Null ,Null ,4 ,3
UNION ALL SELECT 1 ,10 ,Null ,Null ,6 ,4
UNION ALL SELECT 1 ,12 ,Null ,Null ,3 ,5
UNION ALL SELECT 4 ,0 ,'20060401','20060403',2 ,1


UPDATE T
SET 
	StartDT = 
		-- Get the EndDT for the first record
		(SELECT EndDT FROM @Table AS A WHERE A.AmendID = T.AmendID and PredID = 0)
		-- Add the sum of the Costs to get the new StartDT, 
		-- excluding the rows own cost
		+ (
			SELECT COALESCE(SUM(Cost), 0) 
			FROM @Table AS B 
			WHERE 
				B.AmendID = T.AmendID 
				AND B.Rank < T.Rank 
				AND PredID <> 0
		),
	EndDT = 
		-- Get the EndDT for the first record
		(SELECT EndDT FROM @Table AS A WHERE A.AmendID = T.AmendID and PredID = 0)
		-- Add the sum of the Costs to get the new EndDT, 
		-- including the rows own cost
		+ (
			SELECT COALESCE(SUM(Cost), 0) 
			FROM @Table AS B 
			WHERE 
				B.AmendID = T.AmendID 
				AND B.Rank <= T.Rank 
				AND PredID <> 0
		)
FROM
	@Table AS T
WHERE
	PredID > 0
	AND StartDT IS NULL

SELECT *
FROM @Table

greeny122229
Starting Member

25 Posts

Posted - 2009-04-29 : 05:39:30

Hi - thanks a ton - works like a dream, it only will be used for 9-10 records ata time as such resource cost isn't an issue. The actual code i've used is:

UPDATE T
SET
Start_date =
-- Get the EndDT for the first record
(SELECT End_Date FROM TEMP_TBL_MOC_TASKS AS A WHERE A.Amend_ID = T.Amend_ID and Predecessor_ID = 0)
-- Add the sum of the Costs to get the new StartDT,
-- excluding the rows own cost
+ (
SELECT COALESCE(SUM(Cost), 0)
FROM TEMP_TBL_MOC_TASKS AS B
WHERE
B.Amend_ID = T.Amend_ID
AND B.Predecessor_ID < T.Predecessor_ID
AND Predecessor_ID <> 0
),
End_date =
-- Get the EndDT for the first record
(SELECT End_date FROM TEMP_TBL_MOC_TASKS AS A WHERE A.Amend_ID = T.Amend_ID and Predecessor_ID = 0)
-- Add the sum of the Costs to get the new EndDT,
-- including the rows own cost
+ (
SELECT COALESCE(SUM(Cost), 0)
FROM TEMP_TBL_MOC_TASKS AS B
WHERE
B.Amend_ID = T.Amend_ID
AND B.Predecessor_ID <= T.Predecessor_ID
AND Predecessor_ID <> 0
)
FROM
TEMP_TBL_MOC_TASKS AS T
WHERE
Predecessor_ID > 0
AND Start_date IS NULL

Many thanks for your help.

Matt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources