Your Location is: Home > Sql

Remove duplicated record in sql

From: Papua View: 3653 J.p 

Question

What I'm trying to do is take records that looks like this:

ID  NAME    STARTDATE   ENDDATE EnrollmentMonth
1   PETER   20190101    20200729    20200701    
1   TONY    20200730    99991231    20200701

and change it to look like this:

ID  NAME    STARTDATE   ENDDATE EnrollmentMonth
1   PETER   20190101    20200729    20200701    

Excluding the name that has less enrollment days, Peter enrolled for 29 days in July while Tony enrolled for 2 days in July.

The idea is to pick only one name in July.

I am having difficult time making this work. Any help would be appreciated.

Thanks

Best answer

If you want just one record for July, which has the lest number of enrolment days, you can use date arithmetics, order by and fetch first:

select *
from mytable
where enrollmentmonth = date '2020-07-01'
order by least(enddate, date '2020-07-31') - greatest(startdate, '2020-07-01')
fetch first row with ties

We can implement the same logic on a per-month basis using window functions:

select *
from (
    select t.*, 
        rank() over(
            partition by enrollmentmonth 
            order by least(enddate, last_day(enrollmentmonth)) - greatest(startdate, trunc(enrollmentmonth, 'month'))
        ) rn
    from mytable t
) t
where rn = 1