I have table with date column. I want to insert integer number: from first Monday to that Sunday 1, from second Monday to Sunday 2, etc...
How can I do this in SQL server?
Expected output:
03/14/16 1 03/15/16 1 03/16/16 1 03/17/16 1 03/18/16 1 03/19/16 1 03/20/16 1 03/21/16 2 03/22/16 2 03/23/16 2 03/24/16 2 03/25/16 2 03/26/16 2 03/27/16 2 03/28/16 3 03/29/16 3 03/30/16 3 03/31/16 3 04/01/16 3 04/02/16 3 04/03/16 3 04/04/16 4 Try this
SELECT DENSE_RANK() OVER( ORDER BY DATEPART(YEAR,[date column]) ,DATEPART(WEEK,[date column]) ) FROM table 
DECLARE @start DATE = '2016-03-14' ;WITH cte AS ( SELECT @start as [d], 1 as [w] UNION ALL SELECT DATEADD(day,1,[d]), CASE WHEN DATEPART(WEEK,[d]) != DATEPART(WEEK,DATEADD(day,1,[d])) THEN w+1 ELSE w END FROM cte WHERE d < '2017-04-04') SELECT [d], [w] FROM cte OPTION (MAXRECURSION 1000) Another way of doing the same as DENSE_RANK
You may also need to SET DATEFIRST 1 if your locale doesn't have Monday as the first day of the week.
SELECT MyDate, DATENAME(WEEKDAY, MyDate), (DATEPART(WEEK, MyDate) - DATEPART(WEEK,MIN(MyDate) OVER (ORDER BY MyDate))) + ((DATEPART(YEAR, MyDate) - DATEPART(YEAR,MIN(MyDate) OVER (ORDER BY MyDate))) *52) + 1 MyWeek FROM MyData MyDate MyWeek ---------- ------------------------------ ----------- 2015-12-26 Saturday 1 2015-12-27 Sunday 1 2015-12-28 Monday 2 2015-12-29 Tuesday 2 2015-12-30 Wednesday 2 2015-12-31 Thursday 2 2016-01-01 Friday 2 2016-01-02 Saturday 2 2016-01-03 Sunday 2 2016-01-04 Monday 3 2016-01-05 Tuesday 3 Edit Fix year crossing
Edit 2 Really fix year crossing, increment counter for each Monday (eg if first date is mid week), see example data.
