Assigning some week numbers

Question

Assigning some week numbers

Matthew24

SSCommitted

Points: 1736
More actions
July 11, 2010 at 4:20 am

#226710

Hi,
I'm self taught SQL and stuck on a problem with trying to assign week numbers to a table.
I have a table that has columns Date, Day of Week, Week Number.
The date is a serial format that the application front end uses. Looks like at present:
Date DOW Weekno
76161MonNULL
76162TueNULL
76163WedNULL
76164ThuNULL
76165FriNULL
76166SatNULL
76167SunNULL
76168MonNULL
76169TueNULL
76170WedNULL
76171ThuNULL
76172FriNULL
76173SatNULL
76174SunNULL
What I want to do is assign weekn numbers, so the first Mon to Sun will be 1, the second Mon to Sun will be 2 and so on.
Can't think of an update statement to get this, any help greatly appreciated.
Matthew

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Looks like you're trying to build a calendar table.

You should add a column with datetime format to make it easier to recognize the date. You could do it by using a persisted computed column.

You might also consider searching this site for some code snippets regarding calendar tables (search string: calendar table, for example this link[/url] ).

One thing to notice regarding week_of_Year and day_of_week used in this code: the functions used will not result in the ISO week or the day_of_Week you might expect. See BOL (BooksOnLine, the SQL Server help system) for details.

Regarding date functions I also recommend Lynn's great blog post

.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Matthew24 SSCommitted Points: 1736 More actions · Answer 2

Thank you for your reply.

I'm not building a calendar table. I'm calculating something which requires totals to be broken down into weeks. It could just as easily be blocks of numbers that have to be broken down into groups of 7. What I can't figure out is how to assign the first block of 7 the value 1, the second 2 etc.

Thanks,

Matthew

LutzM SSC Guru Points: 107049 More actions · Answer 3

Assuming you don't have an additional column that will number your rows starting with 1 you could either use ROW_NUMBER() or subtract the min(DATE) from your DATE column and add 1.

assuming the former approach you could use

SELECT (ROW_NUMBER() OVER(ORDER BY [Date]) -1)/ 7 +1

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Matthew24 SSCommitted Points: 1736 More actions · Answer 4

Hi Lutz,

I've tried this, and it updates every weeknumber to be 1.

Perhaps I am putting it into the Update incorrectly. I need to examine the syntax and figure out what is ahppening, but at present:

update #holcalc set weekno = (SELECT (ROW_NUMBER() OVER(ORDER BY [Date]) -1)/ 7 +1)

Thanks again.

Matthew

LutzM SSC Guru Points: 107049 More actions · Answer 5

Ok, here's the code including test data setup.

Please note that there a few people around (including myself, usually) who expect sample data in a ready to use format (as shown below).

Especially, if the OP has a significant number of visits... 😉

DECLARE @tbl TABLE

(

DATE INT, DOW CHAR(3), Weekno INT NULL

)

INSERT INTO @tbl

SELECT 76161 ,'Mon', NULL UNION ALL

SELECT 76162 ,'Tue', NULL UNION ALL

SELECT 76163 ,'Wed', NULL UNION ALL

SELECT 76164 ,'Thu', NULL UNION ALL

SELECT 76165 ,'Fri', NULL UNION ALL

SELECT 76166 ,'Sat', NULL UNION ALL

SELECT 76167 ,'Sun', NULL UNION ALL

SELECT 76168 ,'Mon', NULL UNION ALL

SELECT 76169 ,'Tue', NULL UNION ALL

SELECT 76170 ,'Wed', NULL UNION ALL

SELECT 76171 ,'Thu', NULL UNION ALL

SELECT 76172 ,'Fri', NULL UNION ALL

SELECT 76173 ,'Sat', NULL UNION ALL

SELECT 76174 ,'Sun', NULL

;

WITH cte AS

(

SELECT ROW_NUMBER() OVER(ORDER BY DATE) AS ROW,DATE

FROM @tbl

)

UPDATE t

SET Weekno=(ROW -1)/ 7 +1

FROM @tbl t

INNER JOIN cte

ON t.Date=cte.Date

SELECT *

FROM @tbl

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Matthew24 SSCommitted Points: 1736 More actions · Answer 6

Hi,

I've managed to successfully adapt this to solve my problem. Now I'll have to spend some time trying to understand exactly what is happening.

Point noted about the data provision.

Many thanks,

Matthew

LutzM SSC Guru Points: 107049 More actions · Answer 7

The concept is rather simple:

the CTE or "common table expression" is nothing but a subquery (at least in this case), just easier to read.

The purpose is to number your rows ordered by Date column.

The result is something like

ROW DATE

1 76161

2 76162

3 76163

4 76164

...

To calculate Weekno, I took advantage of the way SQL Server does integer division: an integer divided by another integer will return an integer again.

For example, "SELECT 1/7" will return 0, "SELECT 7/7" will return 1.

Since I want to go to the next group every 7 days, I need to subtract 1 from the Row number. This will retun 0 for the first seven rows, 1 for the next seven and so forth.

But you wanted to start with 1 as the first group number, so I added +1 to the calculation.

And, finally, I joined the two tables and changed the value for Weekno.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 8

m.dunster (7/11/2010)
Hi,
I've managed to successfully adapt this to solve my problem. Now I'll have to spend some time trying to understand exactly what is happening.
Point noted about the data provision.
Many thanks,
Matthew

Cool. Thanks for the feedback but please post your solution. It's just good forum ettiquette. 😉

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 9

m.dunster (7/11/2010)
Hi,
I've managed to successfully adapt this to solve my problem. Now I'll have to spend some time trying to understand exactly what is happening.
Point noted about the data provision.
Many thanks,
Matthew

I hope so this time because this isn't the first time, Matt. 😉

http://www.sqlservercentral.com/Forums/Topic788057-1291-1.aspx#bm788397

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 10

lmu92 (7/11/2010)
Ok, here's the code including test data setup.
Please note that there a few people around (including myself, usually) who expect sample data in a ready to use format (as shown below).
Especially, if the OP has a significant number of visits... 😉

Heh... Especially if the OP has been told before. 😉

http://www.sqlservercentral.com/Forums/Topic788057-1291-1.aspx#bm788397

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matthew24 SSCommitted Points: 1736 More actions · Answer 11

Jeff Moden (7/11/2010)
m.dunster (7/11/2010)
Hi,
I've managed to successfully adapt this to solve my problem. Now I'll have to spend some time trying to understand exactly what is happening.
Point noted about the data provision.
Many thanks,
Matthew
Cool. Thanks for the feedback but please post your solution. It's just good forum ettiquette. 😉

Ok, when I use the term 'adapted', what I really mean is change the table names to suit my database. 😀

Through your example for creating the data...I have learnt about table variables and will now be replacing all my temp table work !!

LutzM SSC Guru Points: 107049 More actions · Answer 12

m.dunster (7/12/2010)
Ok, when I use the term 'adapted', what I really mean is change the table names to suit my database. 😀
Through your example for creating the data...I have learnt about table variables and will now be replacing all my temp table work !!

Just because you've seen someone using table variables doesn't imply any learning effect ...;-)

I'd consider Wayne's excellent article[/url] including the discussion as a resource someone can use to gain such knowledge. But not the code snippet I posted.

I strongly recommend against replacing all temp tables with table variables!! Each one has its own advantage and disadvantage.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WayneS SSC Guru Points: 95409 More actions · Answer 13

LutzM (7/12/2010)
m.dunster (7/12/2010)
Ok, when I use the term 'adapted', what I really mean is change the table names to suit my database. 😀
Through your example for creating the data...I have learnt about table variables and will now be replacing all my temp table work !!
Just because you've seen someone using table variables doesn't imply any learning effect ...;-)
I'd consider Wayne's excellent article[/url] including the discussion as a resource someone can use to gain such knowledge. But not the code snippet I posted.
I strongly recommend against replacing all temp tables with table variables!! Each one has its own advantage and disadvantage.

I agree with Lutz - read Wayne's article. 😀 Also, you might be interested to note that most of the guru's on this site rarely use table variables.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 14

m.dunster (7/12/2010)
Through your example for creating the data...I have learnt about table variables and will now be replacing all my temp table work !!

Don't do that. It could be the worst mistake you'll make in the near future. Table Variables are NOT a panacea and frequently offer much worst performance than Temp Tables. Like anything else in T-SQL, "It Depends" and you shouldn't make wholesale changes to your code Instead, you need to do some serious testing in each case where you intend to make a change.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)