World Week - SQL Server Forums

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

World Week

Author

Topic

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-13 : 23:11:26

Hi, I have a table ABC. It has 2 columns, A and B. At column A i have 3 rows, Week,Goal,Used. Let say for column B at row Week is WW15 now, after 7 days i want the row to be updated to WW16. How do i do that? Advice please.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-13 : 23:26:36

Do you want to update the current rows or insert new rows with new week value?

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-14 : 04:52:12

Just update the current row

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-14 : 05:36:33

If you want it to 'update' automatically, it will need to be a calculated value...

declare @ABC table (A varchar(10), B varchar(10))
insert @ABC
		  select 'Goal', 'A goal'
union all select 'Used', 'Something'

select * from @ABC
union all
select 'Week', 'WW' + cast(datediff(day, '20071231', getdate()) / 7 as varchar(10))

/* results
A          B
---------- ------------
Goal       A goal
Used       Something
Week       WW15
*/

You could then create the select statement as a view...

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 05:45:32

We still haven't got Shan's explanation of what a week is for him and his company.

E 12°55'05.25"
N 56°04'39.16"

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-14 : 20:57:36

Hi, thanks for the reply. When i execute this query it doesnt increase the WW15 to WW16. I can schedule a task to execute this query every 7 days. but i just need the Week row to be updated like from WW15 to WW16 every time it executes. I used the following but still not changing.

select left(b,2) + convert(varchar,(convert(int,right(b,2)) + 1))

from abc

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-15 : 02:49:39

DECLARE @str VARCHAR(10)
SELECT @str=B FROM ABC WHERE A ='week'
UPDATE ABC SET B=substring(@str,1,2)+convert(varchar(4),convert(integer,substring(@str,len(@str)-1,2))+1)
WHERE A ='week'

Use the above patch in your job which executes every week.

Prakash.P
The secret to creativity is knowing how to hide your sources!

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-15 : 04:34:44

No need for a variable...


UPDATE ABC
SET B = substring(B, 1, 2) + cast(cast(substring(B, 3, 10) as int) + 1 as varchar(8))
WHERE A = 'Week'

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-15 : 05:11:25

thank you so much.It works..

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-16 : 21:14:31

Hi, forgot about this, how do i set limit for the increasing order? the count is only up to WW52, after that it should restart with WW01 again. How do i do that? Thanks..

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-17 : 06:57:52

[code]UPDATE ABC
SET B = substring(B, 1, 2) + right('0' + cast(cast(substring(B, 3, 10) as int) % 52 + 1 as varchar(8)), 2)
WHERE A = 'Week'[/code]

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-17 : 07:00:32

What about those years with 53 weeks?

It sounds you should look at the F_TABLE_DATE function here at SQLTeam.

E 12°55'05.25"
N 56°04'39.16"

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-17 : 20:42:03

Yes you are right. I used the following to get the current week but how to get one week before the current week?

select 'WW'+convert(varchar(10),datepart(wk,getdate()))

UPDATE ABC SET B='WW'+convert(varchar(10),datepart(wk,getdate())) WHERE A='week'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-18 : 02:49:16

Use the F_TABLE_DATE function.
It has a serial number for weeks.

1) Get the serial number for the current week.'
2) Subtract the serial number with 1.
3) Get the week number from F_TABLE_DATE where week serial number equals the new serial number.

E 12°55'05.25"
N 56°04'39.16"

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-18 : 03:32:03

This solves the problem.

select 'WW'+convert(varchar(10),datepart(wk,getdate()))

UPDATE ABC SET B='WW'+convert(varchar(10),datepart(wk,getdate())-1) WHERE A='week'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources