TSQL -> Using ROW_NUMBER() function with PARTITION BY

Here just one simple Exercise showing use of ROW_NUMBER() function with PARTITION BY condition.

-- create data table for testing needs (list of 49 dates)
-- suppose that you have Number's table (num_seq) in database

IF EXISTS ( SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N'[dbo].[Dates_List]') AND type in (N'U'))
  DROP TABLE [dbo].[Dates_List]
GO

SELECT ROW_NUMBER() OVER (ORDER BY n) AS Id,
   DATEADD(d,n, CONVERT(date, SYSDATETIME())) AS the_date,
   -9999 AS Month_No, -9999 AS Day_No
   INTO Dates_List
FROM dbo.num_seq
WHERE n < 49
GO

-- Update Month_No,Day_No
UPDATE Dates_List
SET Month_No = MONTH(the_date), Day_No = DAY(the_date)
GO

-- Numerate Records per Month, ordered by Day number
-- ( generate Section_Id number )

SELECT the_date, Month_No, Day_No,
ROW_NUMBER() OVER (PARTITION BY Month_No
ORDER BY Month_No, Day_No) AS Section_Id
FROM Dates_List
ORDER BY Month_No, Day_No
GO



This will give you numerated days of month, because of Patition By Month_No :
the_dateMonth_NoDay_NoSection_Id
............
2012-03-3033011
2012-03-3133112
2012-04-01411
2012-04-02422
2012-04-03433
2012-04-04444
............



sqlexamples.info