SQL Server row_number function how to remove dups based on each date within a range-CodePudding

I have a query that will have duplicate entries. It looks for all event messages (text) within the month. The same message can exist from a provider for a patient within a single day and I want to remove those. I do not want to remove any messages that could be identical but happened on a different day. Is that possible?

CodePudding user response：

In the row_number you partition by the text, date(truncated to day level) and provider. If the message text is truly identical within a day the row_number function will count up for that group. If it is identical message but on different day then it will be in next partition. So you just need to filter on row_number = 1 from the result of that cte.

Something like this...

select * from (
select distinct PtNbr_name
  ,row_number() over(partition by 
  event_message,provider,convert(varchar(8),date/time,112) order by PtNbr_name) rn
from table ) x
where x.rn = 1

CodePudding user response：

Thanks Mattias W. This seems to work. Adding the extra row_number parameters and converting the date with an order by on ptnbr_name seemed to help. Also, the group by in or commented seemed to have no change to the number of records so that was good to know. Appreciate it. :) select * from ( select distinct PtNbr_name ,row_number() over(partition by event_message,provider,convert(varchar(8),date/time,112) order by PtNbr_name) rn from table ) x where x.rn = 1