How to remove non exact duplicates in SQL Server

Question

Currently I can get data that is from each report and filtered by case type and again on case open and for each casereport that I want.

However as a case can be open over several months I want Only want the first month it appears. for instance a case could be open in each report 201904, 201905 and then reopened in 201911, alot of info on that case changes so its not an exact duplicate, however I am only after the data for the case in the 201904 report.

Currently I am using the following code

Select ReportDate, CaseNo, Est, CaseType
From output.casedata
Where casetype='family' and Status='Open' AND (
  Reportdate='201903' OR Reportdate='201904' OR Reportdate='201905'
  or Reportdate='201906' or Reportdate='201907' or Reportdate='201908'
  or Reportdate='201909' or Reportdate='201910' or Reportdate='201911'
  or Reportdate='201912' or Reportdate='202001' or Reportdate='202002'
)

score 0 · Accepted Answer · answered Mar 16 '20 at 22:19

0

You can use the rank window function to find the row with the first date per case number, and then take all the details from it:

SELECT *
FROM   (SELECT *, RANK() OVER (PARTITION BY CaseNo ORDER BY Reportdate) AS rk
        FROM   output.casedata
        WHERE  casetype = 'family' AND status='Open') t
WHERE  rk = 1

answered Mar 16 '20 at 22:19

Mureinik

297,002
52
306
350

1

perfect exactly what I was after. Thanks! – calcal Mar 17 '20 at 00:45

score 0 · Answer 2 · answered Mar 16 '20 at 22:21

If I followed your correctly, you want the earliest open record per case.

The following should to what you expect:

select c.*
from output.casedata c
where c.reportdate = (
    select min(c1.reportdate)
    where 
        c1.caseno = c.caseno
        and c1.casetype = 'family' 
        and c1.status = 'open' 
        and c1.reportdate between '201903' and '202002'
)

For performance, you want an index on (caseno, casttype, status, reportdate).

Note that I simplifie the filter on reportdate to use between instead of enumerating all possible values.

How to remove non exact duplicates in SQL Server

2 Answers2