Click here to Skip to main content
15,900,818 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I'm trying to figure out if I can update values in a table based on another value which is in the same table and is updated within a same query.

I have 'Num_of_Days' column and 'Due_Date' column. To Calculate Due_Date for Row2, I do this: Row2's Due_Date = Row1's Due_Date + Row1's Num_of_Days. Due_Date is calculated with the previous row's information.

I need to create a trigger to update Due_Date at Num_of_Days' update. If Row1's Num_of_Days is updated, then I have to update Row2 and 3's Due_Date based on Row1's new Due_Date.


row num
Num of Days 
Due Date
1 1 2013/07/10
2 2 2013/07/11
3 null 2013/07/13


becomes


row num
Num of Days 
Due Date
1 5 2013/07/10
2 2 2013/07/15
3 null 2013/07/17


I'm not able to use Fetch nor loop due to a performance issue, and I'm out of idea. Is it possible to do this within a single update query? I can use temporary table and such.

Thanks in advance.
Posted

1 solution

Have a look at example code:
SQL
DECLARE @days TABLE(id INT IDENTITY(1,1), NumOfDays INT NULL, dueDate DATETIME) 

INSERT INTO @days (dueDate)
SELECT '2013-06-01'
UNION ALL SELECT '2013-06-07'
UNION ALL SELECT '2013-06-10'
UNION ALL SELECT '2013-06-15'
UNION ALL SELECT '2013-06-19'
UNION ALL SELECT '2013-06-30'
UNION ALL SELECT '2013-07-01'
UNION ALL SELECT '2013-07-05'
UNION ALL SELECT '2013-07-15'
UNION ALL SELECT '2013-07-24'

UPDATE dst
	SET NumOfDays = DATEDIFF(dd, dst.dueDate, src.CurrDate)
FROM @days AS dst INNER JOIN (
	SELECT id-1 AS id, dueDate AS CurrDate
	FROM @days
	) AS src ON dst.id = src.id

SELECT id, NumOfDays, CONVERT(NVARCHAR(10),dueDate,121) AS dueDate
FROM @days



[EDIT #1]
Sorry for misunderstanding ;)

In below example i use CTE[^]
SQL
DECLARE @days TABLE(id INT IDENTITY(1,1), NumOfDays INT, dueDate DATETIME NULL) 

INSERT INTO @days (NumOfDays, dueDate)
SELECT 6, '2013-06-01'
UNION ALL SELECT 3, NULL
UNION ALL SELECT 5, NULL
UNION ALL SELECT 4, NULL
UNION ALL SELECT 11, NULL
UNION ALL SELECT 1, NULL
UNION ALL SELECT 4, NULL
UNION ALL SELECT 10, NULL
UNION ALL SELECT 9, NULL


;WITH dates AS
(
	SELECT id, NumOfDays, dueDate, DATEADD(dd,NumOfDays,dueDate) AS newDate
	FROM @days
	WHERE (dueDate IS NOT NULL) AND (NumOfDays IS NOT NULL)
	UNION ALL
	SELECT t1.id, t1.NumOfDays, t2.newDate AS dueDate, DATEADD(dd,t1.NumOfDays,t2.newDate) AS newDate
	FROM @days AS t1 INNER JOIN dates AS t2 ON t1.id-1 = t2.id
	WHERE (t1.dueDate IS NULL) AND (t1.NumOfDays IS NOT NULL)
)
UPDATE dst
	SET dueDate = src.dueDate
FROM @days AS dst INNER JOIN dates AS src ON dst.id = src.id

SELECT id, NumOfDays, CONVERT(NVARCHAR(10),dueDate,121) AS dueDate
FROM @days


Result is the same ;)
1	6	2013-06-01
2	3	2013-06-07
3	5	2013-06-10
4	4	2013-06-15
5	11	2013-06-19
6	1	2013-06-30
7	4	2013-07-01
8	10	2013-07-05
9	9	2013-07-15


More about: Recursive Queries Using Common Table Expressions[^]
[/EDIT]
 
Share this answer
 
v3
Comments
Meg Takahashi 24-Jul-13 16:35pm    
Hi Maciej,
Thanks for your post, but I don't think it is doing what I want. Your code is getting NumOfDays from the fixed dates already exists in the table. What I want is to calculate Due_Dates based on a previous row's NumOfDays and Due_Date where Due_Date should be updated recursively. Did you give this example so that I can tweak to apply to my problem?
Maciej Los 24-Jul-13 17:17pm    
Meg, the mechanism is the same ;) I'll update my answer soon...
Maciej Los 24-Jul-13 17:55pm    
See my updated answer ;)
Meg Takahashi 25-Jul-13 9:02am    
Hi Maciej,
It worked!!! Thanks a lot!!! :)
Maciej Los 25-Jul-13 10:23am    
You're welcome ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900