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

Solution 1:

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(PARTITIONBY pid, CAST(ProcessedDate ASDATE) ORDERBY 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

Solution 2:

Query.

SELECT pid as ProjectID
  , Row_Number() OVER(PARTITIONBY pid, ProcessedDate ORDERBY ProcessedDate) AS AppNo
  , ProcessedDate
FROMtable

Solution 3:

you must use partition just with date part like this :

SELECT AppNo, ProcessedDate,pid
,Row_Number() OVER(PARTITIONBY pid, CONVERT(VARCHAR(10), ProcessedDate, 111) ORDERBY ProcessedDate) AS rn
select*FROMtable

Solution 4:

if ProcessedDate is of type DATETIME you can just do

SELECT 
    pid, 
    ROW_NUMBER() OVER (
        PARTITIONBY pid, convert(date, ProcessedDate) 
        ORDERBY pid, ProcessedDate
    ) AppNo,
    ProcessedDate
FROMtable

if ProcessedDate is of type VARCHAR you can just do

;WITH
t as (
    SELECT  pid, convert(datetime, ProcessedDate, 120) ProcessedDate
    fromtable
)
SELECT 
    pid, 
    ROW_NUMBER() OVER (
        PARTITIONBY pid, convert(date, ProcessedDate) 
        ORDERBY pid, ProcessedDate
    ) AppNo,
    ProcessedDate
FROM t

Solution 5:

convert ProcessedDate to date if row_number shouldn't consider time as:

SELECT AppNo, ProcessedDate,pid
,Row_Number() OVER(PARTITIONBY pid, convert (date,ProcessedDate) 
                   ORDERBY ProcessedDate) AS rn
from@T

You can write an Update as:

with CTE as(
SELECT  ProcessedDate,pid
,Row_Number() OVER(PARTITIONBY pid, convert (date,ProcessedDate) 
                   ORDERBY ProcessedDate) AS rn
from Test
    )
    update CTE
    set AppNo = rn

and test the result as:

Select AppNo , 
       Row_Number() OVER(PARTITIONBY pid, convert (date,ProcessedDate) 
                         ORDERBY ProcessedDate) AS rn,
       ProcessedDate,
       pid
from Test

Sample code here..