• It might be caused by how the date ranges are handled. The union might allow to get a more straight forward plan.

    Maybe if you combine the solutions, you could get better performance.

    SELECT

    t.Id,

    x.Amount,

    t.CashFlowDate,

    x.AmtType

    FROM #Test t

    CROSS APPLY (

    VALUES

    (t.Principal, 'Principal'), --No need for additional date condition

    (CASE WHEN t.CashFlowDate >= '2016-01-02' THEN t.Interest END, 'Interest') --Exclude the first day

    ) x (Amount,AmtType)

    WHERE x.Amount > 0

    AND CashFlowDate between '2016-01-01' AND '2016-01-05'; --Leave a single date condition

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2