Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Loop with weeknumber
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jylland
Starting Member

27 Posts

Posted - 01/07/2013 :  09:19:55  Show Profile  Reply with Quote
hi

I want to make loop but how to do ?
I have a test database with two tables Look1 and Look2, and
I want to move data from Look2 to Look1 and with weeks implemented

Look2 table

Id date
1 12-30-2012
2 12-31-2013
3 01-01-2013
4 01-02-2013
5 01-03-2013
6 01-04-2013
7 01-05-2013
8 01-06-2013
9 01-07-2013
10 01-08-2013
11 01-09-2013

more

Look1 as it should look like
Id date weeknumber
1 12-30-2012 53
2 12-31-2012 1
3 01-01-2013 1
4 01-02-2013 1
5 01-03-2013 1
6 01-04-2013 1
7 01-05-2013 1
8 01-06-2013 1
9 01-07-2013 2
10 01-08-2013 2

more



Edited by - jylland on 01/07/2013 09:39:13

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/07/2013 :  09:27:26  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
How are you defining the week number?
Years usually end with week 53


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nigelrivett on 01/07/2013 09:28:21
Go to Top of Page

jylland
Starting Member

27 Posts

Posted - 01/07/2013 :  09:38:43  Show Profile  Reply with Quote

thanks I forgot 53 weeks in a year

About the defining week number thats one of the
problems for me how to do ? maybe isn't a loop I need ?


quote:
Originally posted by nigelrivett

How are you defining the week number?
Years usually end with week 53


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/07/2013 :  10:15:14  Show Profile  Reply with Quote
does that mean you need week number to restart after 52?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jylland
Starting Member

27 Posts

Posted - 01/07/2013 :  12:30:57  Show Profile  Reply with Quote
yes that's the meaning

quote:
Originally posted by visakh16

does that mean you need week number to restart after 52?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




Edited by - jylland on 01/07/2013 12:39:27
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/07/2013 :  12:34:50  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
will datepart(wk,date) do?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/07/2013 :  23:32:19  Show Profile  Reply with Quote
quote:
Originally posted by jylland

yes that's the meaning

quote:
Originally posted by visakh16

does that mean you need week number to restart after 52?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






sorry but your modified output looks like you just want weeknumber returned by datepart. then wats the issue?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jylland
Starting Member

27 Posts

Posted - 01/08/2013 :  05:19:31  Show Profile  Reply with Quote

yes that could be a solution or sum from a specific date and + one week up every 7 days, so the week number is 60 after 5 years.

quote:
Originally posted by visakh16

quote:
Originally posted by jylland

yes that's the meaning

quote:
Originally posted by visakh16

does that mean you need week number to restart after 52?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






sorry but your modified output looks like you just want weeknumber returned by datepart. then wats the issue?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/08/2013 :  05:37:47  Show Profile  Reply with Quote
quote:
Originally posted by jylland


yes that could be a solution or sum from a specific date and + one week up every 7 days, so the week number is 60 after 5 years.

quote:
Originally posted by visakh16

quote:
Originally posted by jylland

yes that's the meaning

quote:
Originally posted by visakh16

does that mean you need week number to restart after 52?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






sorry but your modified output looks like you just want weeknumber returned by datepart. then wats the issue?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






nope weeknumber returned by DATEPART will reset after every 53 weeks

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jylland
Starting Member

27 Posts

Posted - 01/08/2013 :  09:16:33  Show Profile  Reply with Quote
yes how I make jobagent which every week take dates from look2 table and put it to look1 table and put weeknumber in !



quote:
Originally posted by visakh16

quote:
Originally posted by jylland


yes that could be a solution or sum from a specific date and + one week up every 7 days, so the week number is 60 after 5 years.

quote:
Originally posted by visakh16

quote:
Originally posted by jylland

yes that's the meaning

quote:
Originally posted by visakh16

does that mean you need week number to restart after 52?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






sorry but your modified output looks like you just want weeknumber returned by datepart. then wats the issue?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






nope weeknumber returned by DATEPART will reset after every 53 weeks

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/08/2013 :  23:52:36  Show Profile  Reply with Quote
quote:
Originally posted by jylland

yes how I make jobagent which every week take dates from look2 table and put it to look1 table and put weeknumber in !



quote:
Originally posted by visakh16

quote:
Originally posted by jylland


yes that could be a solution or sum from a specific date and + one week up every 7 days, so the week number is 60 after 5 years.

quote:
Originally posted by visakh16

quote:
Originally posted by jylland

yes that's the meaning

quote:
Originally posted by visakh16

does that mean you need week number to restart after 52?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






sorry but your modified output looks like you just want weeknumber returned by datepart. then wats the issue?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






nope weeknumber returned by DATEPART will reset after every 53 weeks

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






for copying from one table to other use

INSERT look2
SELECT...
FROM look1
WHERE...

what should be values generated for weeknumber?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
651 Posts

Posted - 01/09/2013 :  22:03:13  Show Profile  Reply with Quote
Are you, perhaps, looking for the ISO Week number?

--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".

First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/10/2013 :  23:03:01  Show Profile  Reply with Quote
quote:

Why you reply with quote non-stop?
This is not good for other people that want to read the topic...



I didnt explicitly quote earlier posts. i just replied to last post from OP which had all the other conversation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next 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.36 seconds. Powered By: Snitz Forums 2000