Update rows with increment date

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

General SQL Server Forums

New to SQL Server Programming

Update rows with increment date

Author

Topic

dedwards7
Starting Member

13 Posts

Posted - 2014-04-05 : 21:49:34

dedwards7
Starting Member

13 Posts

Posted - 2014-04-06 : 21:33:23

Please is there anyone that can help me with this???

Daren Edwards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-06 : 22:24:31

[code]
; with cte as
(
select *, rn = row_number() over (order by Startdate)
from yourtable
)
select *,
recursive_date = dateadd(day, rec_num * (case when recursive_value = 'd' then 1 else 7 end) * (rn - 1), Startdate)
from cte[/code]

KH
[spoiler]Time is always against us[/spoiler]

dedwards7
Starting Member

13 Posts

Posted - 2014-04-06 : 22:36:27

KH.. You are awesome thank you very much for your help.. 1 question I'm new to SQL programming.. How should I start this store procedure?
Should it be with " ; with cte as"

Daren Edwards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-06 : 23:01:44

yes

KH
[spoiler]Time is always against us[/spoiler]

dedwards7
Starting Member

13 Posts

Posted - 2014-04-07 : 02:25:18

Kh, I will try today and let you know.. Thank you a lot .

Daren Edwards

dedwards7
Starting Member

13 Posts

Posted - 2014-04-07 : 09:07:37

Kh, i'm getting error.. plese help

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ';'.
Server: Msg 195, Level 15, State 1, Line 3
'row_number' is not a recognized function name.

Daren Edwards

dedwards7
Starting Member

13 Posts

Posted - 2014-04-07 : 09:13:27

Window functions (ROW_NUMBER) are not supported in SQL Server 2000.

Daren Edwards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-04-07 : 09:20:04

Try this

select Startdate, rec_number , recursive_value*number, dateadd(day,recursive_value*number,recursive_date) from your_table as t cross join master..spt_values where type='p' and number between 0 and (select count(*) from your_table)-1

Madhivanan

Failing to plan is Planning to fail

dedwards7
Starting Member

13 Posts

Posted - 2014-04-07 : 15:28:42

Madhivanan - thank you thank you thank you it worked. thank you. :)

Daren Edwards

dedwards7
Starting Member

13 Posts

Posted - 2014-04-09 : 16:00:22

Kh - how do i update a table with this value using your script..?

; with cte as
(
select *, rn = row_number() over (order by Startdate)
from yourtable
)
select *,
recursive_date = dateadd(day, rec_num * (case when recursive_value = 'd' then 1 else 7 end) * (rn - 1), Startdate)
from cte

Daren Edwards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-09 : 22:15:43

you are using SQL 2000, my query will not work on that

KH
[spoiler]Time is always against us[/spoiler]

dedwards7
Starting Member

13 Posts

Posted - 2014-04-10 : 06:23:00

I've upgraded to SQL 2012

Daren Edwards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-10 : 06:52:58

[code]
update t set recursive_date = dateadd(day, rec_num * (case when recursive_value = 'd' then 1 else 7 end) * (rn - 1), Startdate)
from
(
select *, rn = row_number() over (order by Startdate)
from yourtable
) t[/code]

KH
[spoiler]Time is always against us[/spoiler]

dedwards7
Starting Member

13 Posts

Posted - 2014-04-10 : 11:08:27

thank you very much Khtan

Daren Edwards

dedwards7
Starting Member

13 Posts

Posted - 2014-04-17 : 14:38:27

KH IS THERE A WAY WE CAN DO THIS AND STOP AT A END-DATE.. I'VE BEING TRYING TO WITH NO LUCK.

Daren Edwards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-18 : 00:49:50

quote:
Originally posted by dedwards7

KH IS THERE A WAY WE CAN DO THIS AND STOP AT A END-DATE.. I'VE BEING TRYING TO WITH NO LUCK.

Daren Edwards

Can you elaborate that with an example ?

KH
[spoiler]Time is always against us[/spoiler]

dedwards7
Starting Member

13 Posts

Posted - 2014-04-19 : 09:40:53

I have a table with 4 fields,
Startdate, rec_num , recursive_value, recursive_date
04/02/2014 3 d 04/02/2014
04/02/2014 3 d 04/02/2014
04/02/2014 3 d 04/02/2014
I will like to update recursive_date to emulate the recursive_number and recursive_value fields which specify every 3 days. The recursive_value field can also be w to specify weeks, m to specify month or y to specify years.
So my value in the recursive_date field should be updated as followed

Startdate, rec_number , recursive_value, recursive_date
04/02/2014 3 d 04/02/2014
04/02/2014 3 d 04/05/2014
04/02/2014 3 d 04/08/2014
Any help will be greatly appreciated.

but i would also like to specify a end date so if i chosen startdate = 01/01/2014 and the chosen enddate = 03/29/2014, (rec_number = which is every 3 days), (recursive_value, which is days) i would like all the days every 3 days from the startdate to the enddate. as it is right now i have a recurrence count value...which specify i.e startdate, rec_number, recursive_value and how many times i want this to occur so i can pick 6 times. I would like to pick enddate.

Daren Edwards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-19 : 10:13:00

add a WHERE clause to the query

WHERE dateadd(day, rec_num * (case when recursive_value = 'd' then 1 else 7 end) * (rn - 1), Startdate) <= '2014-03-29'

KH
[spoiler]Time is always against us[/spoiler]

dedwards7
Starting Member

13 Posts

Posted - 2014-04-19 : 14:39:00

O...:)
Thank yon KH..

Daren Edwards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources