Require SQL server procedure for grouping dates 1

[resumes123]

I am using SQL Server 2008. I have a Sales table. The table name is
SALES. The fields are ID, DATE, TIME, COST_OF_SALES, WK_ID
The Sales table has Sales data of the company for about 3 months
It has data on a daily basis ( no holidays ) . Every hour there are transactions and the cost of sales is stored on hourly basis every day. Hence each day will have multiple rows.
ID is a unique value field to identify a row.

I have a field called WK_ID. I need to populate this field with a unique Week Id for each day in the table. That is, if the day is Sunday the wK_ID is 1, if it is Monday it is 2 and so on and so forth. Each day may have multiple rows, but all the rows for a given day will have only one WK_ID. If only the date changes the WK_ID changes to the next ID

Now, after the WK_ID gets to the value of 7,( ie SATURDAY ), it has to again re adjust the value to 1 for SUNDAY, to accomodate the next days WK_ID value
The table already has thousands of rows with all the other fields ( ID, DATE, TIME, COST ) filled in. WK_ID is a new field and has to be populated
Could some one please suggest a SQL Server procedure code as to how I can populate the WK_ID
I am relatively new to SQL Server

 

[SQLSister]

Suggest you set the column up with a constraint (so that it automatically changes if someone changes a date. Look up the datepart command in books online to see how to get a numeric value for the day of the week.

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

There is a DatePart function that you may find useful. Ex:

Select DatePart(Weekday, GetDate())

This returns a 4 (when you run it today) because today is Wednesday, the 4th day of the week.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom