title: How to Update Week Number in SQL?
body: <p>I have a following  which are displayed order by date. I want to group each weekseries as below.</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>Current Data:</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>Required Data:</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p>So, I want to group these values under weeknumber which must start from 1 for WeekSerial number ranges from 1 to 7.</p>
  
  <p><strong>NOTE:</strong> <em>The week day starts from Monday to Sunday so its numbered from 1 through 7. i.e 1=Monday, 2=Tuesday and so on...!</em></p>
  
  <p><strong>Update:</strong></p>
  
  <pre><code>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 &lt;&gt; 12 --remove void transactions
  group by CONVERT(VARCHAR(10),tq.DateCreated,101),DATENAME(WEEKDAY,tq.DateCreated),DATEPART(WEEKDAY,tq.DateCreated)-1 
  order by 4
  </code></pre>