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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Loop with weeknumber
 New Topic  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
52317 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
52317 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
52317 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
52317 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
649 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
52317 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  
 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.26 seconds. Powered By: Snitz Forums 2000