SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update rows with increment date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dedwards7
Starting Member

USA
13 Posts

Posted - 04/05/2014 :  21:49:34  Show Profile  Reply with Quote
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.


Daren Edwards

Edited by - dedwards7 on 04/05/2014 22:52:59

dedwards7
Starting Member

USA
13 Posts

Posted - 04/06/2014 :  21:33:23  Show Profile  Reply with Quote
Please is there anyone that can help me with this???

Daren Edwards
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17595 Posts

Posted - 04/06/2014 :  22:24:31  Show Profile  Reply with Quote

; 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



KH
Time is always against us

Go to Top of Page

dedwards7
Starting Member

USA
13 Posts

Posted - 04/06/2014 :  22:36:27  Show Profile  Reply with Quote
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

Edited by - dedwards7 on 04/06/2014 22:38:15
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17595 Posts

Posted - 04/06/2014 :  23:01:44  Show Profile  Reply with Quote
yes


KH
Time is always against us

Go to Top of Page

dedwards7
Starting Member

USA
13 Posts

Posted - 04/07/2014 :  02:25:18  Show Profile  Reply with Quote
Kh, I will try today and let you know.. Thank you a lot .

Daren Edwards
Go to Top of Page

dedwards7
Starting Member

USA
13 Posts

Posted - 04/07/2014 :  09:07:37  Show Profile  Reply with Quote
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
Go to Top of Page

dedwards7
Starting Member

USA
13 Posts

Posted - 04/07/2014 :  09:13:27  Show Profile  Reply with Quote
Window functions (ROW_NUMBER) are not supported in SQL Server 2000.

Daren Edwards
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 04/07/2014 :  09:20:04  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

dedwards7
Starting Member

USA
13 Posts

Posted - 04/07/2014 :  15:28:42  Show Profile  Reply with Quote
Madhivanan - thank you thank you thank you it worked. thank you. :)


Daren Edwards
Go to Top of Page

dedwards7
Starting Member

USA
13 Posts

Posted - 04/09/2014 :  16:00:22  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17595 Posts

Posted - 04/09/2014 :  22:15:43  Show Profile  Reply with Quote
you are using SQL 2000, my query will not work on that


KH
Time is always against us

Go to Top of Page

dedwards7
Starting Member

USA
13 Posts

Posted - 04/10/2014 :  06:23:00  Show Profile  Reply with Quote
I've upgraded to SQL 2012

Daren Edwards
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17595 Posts

Posted - 04/10/2014 :  06:52:58  Show Profile  Reply with Quote

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



KH
Time is always against us

Go to Top of Page

dedwards7
Starting Member

USA
13 Posts

Posted - 04/10/2014 :  11:08:27  Show Profile  Reply with Quote
thank you very much Khtan


Daren Edwards
Go to Top of Page

dedwards7
Starting Member

USA
13 Posts

Posted - 04/17/2014 :  14:38:27  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17595 Posts

Posted - 04/18/2014 :  00:49:50  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

dedwards7
Starting Member

USA
13 Posts

Posted - 04/19/2014 :  09:40:53  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17595 Posts

Posted - 04/19/2014 :  10:13:00  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

dedwards7
Starting Member

USA
13 Posts

Posted - 04/19/2014 :  14:39:00  Show Profile  Reply with Quote
O...:)
Thank yon KH..

Daren Edwards
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000