Search code examples
sqlsql-serversql-updatesubquerywindow-functions

I need a Row_number over partition to achieve this in SQL Server

I have three columns PID, AppNo and ProcessedDate I need a query to update the AppNo in the format below

PID AppNo   ProcessedDate
11  1      09/30/2019 18:21
3   1      09/25/2019 08:37
3   2      09/25/2019 08:37
11  1      09/25/2019 08:39
11  2      09/25/2019 08:40
7   1      09/26/2019 14:19
7   2      09/26/2019 14:20
7   3      09/26/2019 14:22
2   1      09/26/2019 14:23
11  1      09/26/2019 14:23
11  2      09/26/2019 14:24
11  3      09/26/2019 14:24
3   1      09/26/2019 14:24

For now the AppNo column is null.

This is the sql that is not working

SELECT AppNo, ProcessedDate,pid
,Row_Number() OVER(PARTITION BY pid, ProcessedDate ORDER BY ProcessedDate) AS rn
select * FROM table

Solution

  • You seem to be looking to update your original table. You can use ROW_NUMBER() in a CTE to rank records with groups having the same date (without time) and pid, ordered by date (with time) and then do the update on the fly:

    WITH cte AS (
    SELECT 
        pid, 
        ProcessedDate, 
        AppNo, 
        ROW_NUMBER() OVER(PARTITION BY pid, CAST(ProcessedDate AS DATE) ORDER BY ProcessedDate) rn 
    FROM mytable
)
UPDATE cte SET AppNo = rn

    Demo on DB Fiddle

    Original data:

    PID | AppNo | ProcessedDate   
--: | ----: | :---------------
 11 |  null | 09/30/2019 18:21
  3 |  null | 09/25/2019 08:37
  3 |  null | 09/25/2019 08:37
 11 |  null | 09/25/2019 08:39
 11 |  null | 09/25/2019 08:40
  7 |  null | 09/26/2019 14:19
  7 |  null | 09/26/2019 14:20
  7 |  null | 09/26/2019 14:22
  2 |  null | 09/26/2019 14:23
 11 |  null | 09/26/2019 14:23
 11 |  null | 09/26/2019 14:24
 11 |  null | 09/26/2019 14:24
  3 |  null | 09/26/2019 14:24

    After running the query:

    PID | AppNo | ProcessedDate   
--: | ----: | :---------------
 11 |     1 | 09/30/2019 18:21
  3 |     1 | 09/25/2019 08:37
  3 |     2 | 09/25/2019 08:37
 11 |     1 | 09/25/2019 08:39
 11 |     2 | 09/25/2019 08:40
  7 |     1 | 09/26/2019 14:19
  7 |     2 | 09/26/2019 14:20
  7 |     3 | 09/26/2019 14:22
  2 |     1 | 09/26/2019 14:23
 11 |     1 | 09/26/2019 14:23
 11 |     2 | 09/26/2019 14:24
 11 |     3 | 09/26/2019 14:24
  3 |     1 | 09/26/2019 14:24