How to Update Week Number in SQL?

Question

I have a following which are displayed order by date. I want to group each weekseries as below.

Select  tq.ID,
        CONVERT(VARCHAR(10),tq.DateCreated,101)WeekDate,
        DATENAME(WEEKDAY,tq.DateCreated)WeekDays,
        CASE When DATEPART(WEEKDAY,tq.DateCreated)-1=0 THEN 7 ELSE  DATEPART(WEEKDAY,tq.DateCreated)-1 END as WeekSerial
    From #temp tq

Current Data:

ID  WeekDate                WeekDays    WeekSerial  WeekNumber
56  2012-03-01 00:00:00.000 Thursday    4            NULL
57  2012-03-02 00:00:00.000 Friday      5            NULL
58  2012-03-03 00:00:00.000 Saturday    6            NULL
59  2012-03-04 00:00:00.000 Sunday      7            NULL
62  2012-03-05 00:00:00.000 Monday      1            NULL
63  2012-03-06 00:00:00.000 Tuesday     2            NULL
64  2012-03-07 00:00:00.000 Wednesday   3            NULL
65  2012-03-08 00:00:00.000 Thursday    4            NULL
67  2012-03-09 00:00:00.000 Friday      5            NULL
68  2012-03-10 00:00:00.000 Saturday    6            NULL
69  2012-03-11 00:00:00.000 Sunday      7            NULL
70  2012-03-12 00:00:00.000 Monday      1            NULL
71  2012-03-13 00:00:00.000 Tuesday     2            NULL
73  2012-03-14 00:00:00.000 Wednesday   3            NULL
74  2012-03-15 00:00:00.000 Thursday    4            NULL
76  2012-03-16 00:00:00.000 Friday      5            NULL
77  2012-03-17 00:00:00.000 Saturday    6            NULL
78  2012-03-18 00:00:00.000 Sunday      7            NULL

Required Data:

ID  WeekDate                WeekDays    WeekSerial  WeekNumber
56  2012-03-01 00:00:00.000 Thursday    4            1
57  2012-03-02 00:00:00.000 Friday      5            1
58  2012-03-03 00:00:00.000 Saturday    6            1
59  2012-03-04 00:00:00.000 Sunday      7            1
62  2012-03-05 00:00:00.000 Monday      1            2
63  2012-03-06 00:00:00.000 Tuesday     2            2
64  2012-03-07 00:00:00.000 Wednesday   3            2
65  2012-03-08 00:00:00.000 Thursday    4            2
67  2012-03-09 00:00:00.000 Friday      5            2
68  2012-03-10 00:00:00.000 Saturday    6            2
69  2012-03-11 00:00:00.000 Sunday      7            2
70  2012-03-12 00:00:00.000 Monday      1            3
71  2012-03-13 00:00:00.000 Tuesday     2            3
73  2012-03-14 00:00:00.000 Wednesday   3            3
74  2012-03-15 00:00:00.000 Thursday    4            3
76  2012-03-16 00:00:00.000 Friday      5            3
77  2012-03-17 00:00:00.000 Saturday    6            3
78  2012-03-18 00:00:00.000 Sunday      7            3

So, I want to group these values under weeknumber which must start from 1 for WeekSerial number ranges from 1 to 7.

NOTE: The week day starts from Monday to Sunday so its numbered from 1 through 7. i.e 1=Monday, 2=Tuesday and so on...!

Update:

INSERT INTO #Temp(KioskCount,KioskAmount,KioskAverage,WeekDate,WeekDays,WeekSerial)     
Select  COUNT(tq.quoteid)KioskCount,
        SUM(tq.PriceQuote) [KioskAmount],
        SUM(tq.PriceQuote) / COUNT(tq.QuoteID) [KioskAverage],
        CONVERT(VARCHAR(10),tq.DateCreated,101)WeekDate,
        DATENAME(WEEKDAY,tq.DateCreated)WeekDays,
        CASE When DATEPART(WEEKDAY,tq.DateCreated)-1=0 THEN 7 ELSE  DATEPART(WEEKDAY,tq.DateCreated)-1 END as WeekSerial
from tbl_Quotes tq
where 
tq.QuoteStatusID <> 12 --remove void transactions
group by CONVERT(VARCHAR(10),tq.DateCreated,101),DATENAME(WEEKDAY,tq.DateCreated),DATEPART(WEEKDAY,tq.DateCreated)-1 
order by 4

Question unclear, what are you trying to achieve in your `INSERT` statement? — Felix Pamittan, Mar 05 '15 at 06:40

score 0 · Answer 1 · answered Mar 05 '15 at 07:38

It is unclear actually what did you expect

You have already a WeekDate filed i think the date are correct and based on that you are filled your weekday field

then simply you can loop through the dates and update your week number using the WeekDate

i can provide an example with that

DECLARE @DATE DATETIME
DECLARE @count int
DECLARE @i int
SET @i=56// you can find your first id via query too
SET @count=(select COUNT(WeekDays) from tablename)
WHILE  @i<=@count
BEGIN

SET @DATE =(select  WeekDate from tablename where ID=@i)
update tablename set WeekNumber=(
SELECT DATEPART(WEEK, @DATE)  -
DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM,0,@DATE), 0))+ 1 AS WEEK_OF_MONTH)
where ID=@i
set @i=@i + 1
END;

it will update all your weeknumber field with corresponding number

score 0 · Answer 2 · answered Mar 05 '15 at 07:45

Probably this helps you:

First you need to check the first of the day in your database
```
SELECT @@DATEFIRST
```
By default SUNDAY is first day of week (US). So you need to change it to 1 (Monday)
```
SET DATEFIRST 1
```
More inforamtion about SET DATEFIRST
In SQL Server by using DATEPART built-in function you can get the number of week in a year
```
SELECT DATAPART(WEEK, WeekDate)
```
Also you can get YEAR of your selected datetime
```
SELECT DATAPART(YEAR, WeekDate)
```
If you run these two queries for your first row (Let's say Week number 1)
```
SELECT DATEPART(WEEK, '2012-03-01 00:00:00.000') -- Output = 10
SELECT DATEPART(YEAR, '2012-03-01 00:00:00.000') -- Output = 2012
```
But this week should be your week number 1.
- So you can easily subtract 2012 from your YEAR then multiply it by 52 (because we have 52 weeks in a year)
- Substract 9 from your WEEKNUMBER
- Add the numbers you get from above
```
(YEAR - 2012) * 52 + (WEEK - 9)
```

So by running this you can get the actual result

SELECT (DATEPART(YEAR, WeekDate) - 2012) * 52 + DATEPART(WEEK, WeekDate) - 9 AS WeekNumber
FROM yourTable

How to Update Week Number in SQL?

2 Answers2