Author |
Topic |
dedwards7
Starting Member
13 Posts |
Posted - 2014-04-05 : 21:49:34
|
I have a table with 4 fields, Startdate, rec_num , recursive_value, recursive_date 04/02/2014 3 d 04/02/201404/02/2014 3 d 04/02/201404/02/2014 3 d 04/02/2014I 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 followedStartdate, rec_number , recursive_value, recursive_date 04/02/2014 3 d 04/02/201404/02/2014 3 d 04/05/201404/02/2014 3 d 04/08/2014Any help will be greatly appreciated. Daren Edwards |
|
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 helpServer: Msg 170, Level 15, State 1, Line 1Line 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 thisselect 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)-1MadhivananFailing 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 cteDaren 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 2012Daren 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 KhtanDaren 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_date04/02/2014 3 d 04/02/201404/02/2014 3 d 04/02/201404/02/2014 3 d 04/02/2014I 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 followedStartdate, rec_number , recursive_value, recursive_date04/02/2014 3 d 04/02/201404/02/2014 3 d 04/05/201404/02/2014 3 d 04/08/2014Any 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 queryWHERE 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 |
|
|
|