How to modify Row_Number syntax in SQL so that row numbers are assigned based on a condition?

Question

I am using SQL Server 2014 and I have the following T-SQL query:

SELECT  
    d.PropertyCode,
    b.ProfileID, 
    a.RSY_RESERVATIONSTAYID, 
    b.StatusCode, c2.CreatedOn AS 'Original CreatedOn',
    a.RSY_UDFCHAR07 AS 'Original PMS No', 
    c2.ReservationStayID AS 'Original Resa ID',
    b.CreatedOn, 
    b.PMSConfirmationNumber, 
    ROW_NUMBER() OVER (PARTITION BY b.ProfileID ORDER BY Count(*) DESC)  AS rownum
FROM 
    ReservationStay b
LEFT JOIN 
    P5RESERVATIONSTAY a ON a.RSY_RESERVATIONSTAYID = b.ReservationStayID
LEFT JOIN
    GuestNameInfo c on c.Reservationstayid = b.ReservationStayID
LEFT JOIN
    RESERVATIONSTAY c2 on  c2.PMSConfirmationNumber = a.RSY_UDFCHAR07
LEFT JOIN
    GuestStaySummary d ON d.ReservationStayID = b.ReservationStayID

Here is an extract of my output:

PropertyCode    ProfileID  ....       CreatedOn     ....      rownum

  AXL             90072        2015-06-03 14:15:27.000        1
  AXL             90072        2015-03-16 19:10:27.000        2

I need the rownum to be assigned based on the CreatedOn dates. In other words, for ProfileID 90072, rownum 1 is to be assigned where CreatedOn = 2015-03-16 and rownum 2 to be assigned where CreatedOn = 2015-06-03.

To simplify, rownum is assigned to a ProfileID in the ascending order of its CreatedOn dates.

How do I modify my row_number syntax to achieve this?

Answer 1

ROW_NUMBER() OVER (PARTITION BY b.ProfileID ORDER BY CreatedOn ASC)  AS rownum