How to get duplicate records according to name, date range and status exactly?

I am trying to get the duplicate records that match a date range and according to their state field exactly.

I am trying with the following query

$datos = DB::table('especialidades')
                        ->select('id','uuid','nombre','created_at')
                        ->orderBy('nombre')
                        ->whereIn('nombre', function($q){
                            $q->select('nombre')
                              ->from('especialidades')
                              ->groupBy('nombre')
                              ->havingRaw('COUNT(*) > 1');
                        })                       
                        ->where('created_at', '>=', '2022-10-18')
                        ->where('created_at', '<=', '2022-10-20')
                        ->where('estado', 6)
                       ->get();

And it returns the following results

I must show exactly when they are:

1. The record has duplicates
2. Duplicate records are on the same date
3. All duplicate records have the same status.

If it does not exactly meet these 3 conditions, it should not be displayed.

As you will see in the image of the records, those in red

1. They have duplicates
2. Some duplicates of the record are in different date ranges, so it should not be displayed.
3. Some duplicates of the record are in different states, so it should not show it.

The correct result should have been the ones highlighted in green, since they meet the 3 conditions exactly.

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/vPYcfJK
via IFTTT