row_number dates

Question

Post reply

row_number dates

boehnc

Ten Centuries

Points: 1003
More actions
March 11, 2022 at 12:34 am

#3997253
Grouping by date? Am I missing something?
2020-08-03 13:21 = 1
2020-08-03 14:45 = 2

IF OBJECT_ID('TempDB..#pat','U') IS NOT NULL
DROP TABLE #pat
create table #Pat
(PatID varchar(10)
,ProcNm varchar(10)
,ProcDate date
)
IF OBJECT_ID('TempDB..#value','U') IS NOT NULL
DROP TABLE #value
create table #value
(PatID varchar(10)
,ProcVal int
,ProcValDate datetime)

Insert into #value
values
(15, 25, '2020-08-03 13:21')
,(15, 25, '2020-08-03 13:21')
,(15, 25, '2020-08-03 13:21')
,(15, 25, '2020-08-03 13:21')
,(15, 25, '2020-08-03 13:21')
,(15, 25, '2020-08-03 14:45')
,(15, 25, '2020-08-03 14:45')
,(15, 25, '2020-08-03 14:45')
,(15, 25, '2020-08-03 14:45')
,(15, 25, '2020-08-03 14:45')
Select
v.PatID
,v.ProcValDate
,dense_rank()over(partition by v.patid,v.procvaldate order by v.procvaldate) 'RN'
from #value v
- This topic was modified 2 years, 8 months ago by boehnc.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24386 More actions · Answer 1

You need to GROUP BY v.PatID, v.ProcValDate

Below is an expanded data set, with options to add "RN" by both the DateTime, and just the Date

IF OBJECT_ID( 'TempDB..#value', 'U' ) IS NOT NULL
  DROP TABLE #value;

CREATE TABLE #value (
    PatID       varchar(10)
  , ProcVal     int
  , ProcValDate datetime
);

INSERT INTO #value ( PatID, ProcVal, ProcValDate )
VALUES ( 15, 25, '2020-08-03 13:21' )
     , ( 15, 25, '2020-08-03 13:21' )
     , ( 15, 25, '2020-08-03 13:21' )
     , ( 15, 25, '2020-08-03 14:45' )
     , ( 15, 25, '2020-08-03 14:45' )
     , ( 15, 25, '2020-08-03 14:45' )
     , ( 15, 25, '2020-08-04 11:33' )
     , ( 15, 25, '2020-08-04 11:33' )
     , ( 15, 25, '2020-08-04 12:55' )
     , ( 15, 25, '2020-08-04 12:55' )
     , ( 15, 25, '2020-08-04 17:22' )
     , ( 15, 25, '2020-08-04 17:22' )
     , ( 15, 25, '2020-08-05 09:11' )
     , ( 15, 25, '2020-08-05 09:11' )
     , ( 15, 25, '2020-08-06 14:57' )
     , ( 15, 25, '2020-08-06 14:57' );

SELECT  v.PatID
      , v.ProcValDate
      , RN_ByDateTime = DENSE_RANK() OVER ( PARTITION BY v.PatID ORDER BY v.ProcValDate )
      , RN_ByDate     = DENSE_RANK() OVER ( PARTITION BY v.PatID, CAST(v.ProcValDate AS date) ORDER BY v.ProcValDate )
FROM    #value AS v
GROUP BY v.PatID, v.ProcValDate;

How to post data/code on a forum to get the best help.

Ed B Hall of Fame Points: 3666 More actions · Answer 2

I think the OP's problem is the inclusion of procvaldate in the partition by. The output will be 1 for all rows. If procvaldate is removed from the partition, then 1 will repeat for the first date and 2 will repeat for the second date.

SELECT v.PatID,
       v.ProcValDate,
       DENSE_RANK() OVER(PARTITION BY v.patid ORDER BY v.procvaldate) AS 'RN'
FROM  #value AS v

I assume this is a subset of the data and there are other attributes or codes that are part of the procedure from the same date, all of which need to be assigned the same rank, otherwise the columns could be grouped and a ROW_NUMBER() used.