Missing dates on Min() while using Over and Partition by in T-SQL

I am using OVER, and Partition by to get the mindate and max date of dataset.

|ResdetId | bookingdate | Amount | AmountExcl |
-----------------------------------------------
|120106   | 2018-02-04  |  75.00 |  70.7547   |
|120106   | 2018-02-05  |  75.00 |  70.7547   |
|120106   | 2018-02-06  |  90.00 |  84.9057   |
|120106   | 2018-02-08  |  75.00 |  70.7547   |
|120106   | 2018-02-09  |  75.00 |  70.7547   |

I am using this query

select distinct ResDetId, Amount, AmountExcl, 
    min(Bookingdate) OVER(Partition by ResDetId, Amount, AmountExcl) as Mindate,
    max(Bookingdate) OVER(Partition by ResDetId, Amount, AmountExcl) as MaxDate
 from @Cumulatedbookingdetails

And I am getting this result

|ResdetId | Amount | AmountExcl | MinDate    | MaxDate     |
------------------------------------------------------------
|120106   | 75.00  |  70.7547   | 2018-02-04 |  2018-02-09 |
|120106   | 90.00  |  84.9057   | 2018-02-06 |  2018-02-06 |

As we see date 2018-02-07 record is missing from the data set. So, I need result like this

|ResdetId | Amount | AmountExcl | MinDate    | MaxDate     |
------------------------------------------------------------
|120106   | 75.00  |  70.7547   | 2018-02-04 |  2018-02-05 |
|120106   | 75.00  |  70.7547   | 2018-02-08 |  2018-02-09 |
|120106   | 90.00  |  84.9057   | 2018-02-06 |  2018-02-06 |

Solution

One way to approach an "Islands and Gaps" problem, such as this, is to use a recursive CTE to build up the islands. We make the non-recursive portion (above the union) find the row which marks the start of each island, and the recursive part grows each island one match at a time.

The final results of the CTE unfortunately contain all of the intermediate rows used in building the islands, so you need a final GROUP by to select the final island out:

declare @t table (ResdetId int, bookingdate date, Amount decimal(9,3), AmountExcl decimal (9,3))
insert into @t(ResdetId,bookingdate,Amount,AmountExcl) values
(120106,'20180204',75.00,70.7547),
(120106,'20180205',75.00,70.7547),
(120106,'20180206',90.00,84.9057),
(120106,'20180208',75.00,70.7547),
(120106,'20180209',75.00,70.7547)

;With Islands as (
    select ResdetId, Amount, AmountExcl,bookingdate as MinDate,bookingDate as MaxDate
    from @t t
    where not exists (select * from @t t2
        where t2.ResdetId = t.ResdetId
        and t2.Amount = t.Amount
        and t2.AmountExcl = t.AmountExcl
        and t2.bookingdate = DATEADD(day,-1,t.BookingDate))
    union all
    select i.ResdetId, i.Amount,i.AmountExcl,i.MinDate,t.bookingDate
    from Islands i
        inner join
        @t t
        on t.ResdetId = i.ResdetId
        and t.Amount = i.Amount
        and t.AmountExcl = i.AmountExcl
        and t.bookingdate = DATEADD(day,1,i.MaxDate)
)
select
    ResdetId, Amount, AmountExcl,MinDate,MAX(MaxDate) as MaxDate
from
    Islands
group by ResdetId, Amount, AmountExcl,MinDate

Results:

ResdetId    Amount    AmountExcl   MinDate    MaxDate
----------- --------- ------------ ---------- ----------
120106      75.000    70.755       2018-02-04 2018-02-05
120106      75.000    70.755       2018-02-08 2018-02-09
120106      90.000    84.906       2018-02-06 2018-02-06