How to execute such query in Self servicing, I know about derived tables and have worked on them, can another derived table can be created from the derived table created earlier as done in the following query:

Select ClientID, sum(Amount) ChargeBack from ( Select o.ClientID, case when DATEDIFF(day,(Select Min(PaidOnDateTime) from [Order] where [Order].ID=[OrderItem].OrderID or [Order].ParentOrder=[OrderItem].OrderID),Getdate()) >3 and ProductID=4 then 0 else TotalAmount end Amount,PaidOnDate = (Select Min(PaidOnDateTime) from [Order] where [Order].ID=[OrderItem].OrderID or [Order].ParentOrder=[OrderItem].OrderID) from [OrderItem] inner join [Order] o on o.Id = [OrderItem].OrderID and OrderStatus not in (9,10) where (Select Min(PaidOnDateTime) from [Order] where [Order].ID=[OrderItem].OrderID or [Order].ParentOrder=[OrderItem].OrderID) is not null and ProductID in(4,5)) Items group by ClientID

where (Select Min(PaidOnDateTime) from [Order] where [Order].ID=[OrderItem].OrderID or [Order].ParentOrder=[OrderItem].OrderID) is not null

That's a scalar query expression which can be set to an EntityField used in a FieldCompareNullPredicate.

I know about scalar query expression, actually you did not get me, below is the code which summarizes the inner part of query

i.e.

Select o.ClientID, case when DATEDIFF(day,(Select Min(PaidOnDateTime) from [Order] where [Order].ID=[OrderItem].OrderID or [Order].ParentOrder=[OrderItem].OrderID),Getdate()) >3 and ProductID=4 then 0 else TotalAmount end Amount,PaidOnDate = (Select Min(PaidOnDateTime) from [Order] where [Order].ID=[OrderItem].OrderID or [Order].ParentOrder=[OrderItem].OrderID) from [OrderItem] inner join [Order] o on o.Id = [OrderItem].OrderID and OrderStatus not in (9,10) where (Select Min(PaidOnDateTime) from [Order] where [Order].ID=[OrderItem].OrderID or [Order].ParentOrder=[OrderItem].OrderID) is not null and ProductID in(4,5)

Code:

 ResultsetFields oResultsetFieldsChargeBack = new ResultsetFields(2);
            IPredicateExpression filterChargeback = new PredicateExpression();
            filterChargeback.Add(OrderFields.Id == OrderItemFields.OrderId);
            filterChargeback.AddWithOr(OrderFields.ParentOrder == OrderItemFields.OrderId);

            EntityField oEntityFieldPaidOnDate = new EntityField("PaidOnDate", new ScalarQueryExpression( OrderFields.PaidOnDateTime.SetAggregateFunction( AggregateFunction.Min), filterChargeback), typeof(DateTime?));

            EntityField oEntityFieldChargeBack = new EntityField("ChargeBack", new DbFunctionCall("Case When DATEDIFF(day, {0}, GetDate()) > 3 and {1}={2} then 0 else {3} end", new object[]{oEntityFieldPaidOnDate, OrderItemFields.ProductId,
                                                                                                                                                                                Convert.ToInt32( OrdersEnum.ProductsEnum.CoachingFee),OrderItemFields.TotalAmount}));
            RelationCollection oRelationCollection = new RelationCollection();
            oRelationCollection.Add( OrderItemEntity.Relations.OrderEntityUsingOrderId, JoinHint.Inner).CustomFilter = new PredicateExpression(new FieldCompareRangePredicate(OrderFields.OrderStatus, true,
                                                                                                                                              Convert.ToInt32(OrdersEnum.OrderStatus.Cancelled), Convert.ToInt32(OrdersEnum.OrderStatus.Deleted)));
            IPredicateExpression filterDerivedTable = new PredicateExpression();
            filterDerivedTable.Add(oEntityFieldPaidOnDate != DBNull.Value);
            filterDerivedTable.AddWithAnd(new FieldCompareRangePredicate( OrderItemFields.ProductId, OrdersEnum.ProductsEnum.CoachingFee, OrdersEnum.ProductsEnum.MatchMakingFee));

            oResultsetFieldsChargeBack.DefineField(OrderFields.ClientId, 0);
            oResultsetFieldsChargeBack.DefineField(oEntityFieldChargeBack, 1);

            DerivedTableDefinition oDerivedTableDefinition = new DerivedTableDefinition(oResultsetFieldsChargeBack, "ChargeBacks", filterDerivedTable, oRelationCollection);

What I actaully wanted to know how do I implement the outer part of the query using this derived table

Actually your query doesn't look like a candidate for a DerivedTable. You query looks like this (reformatted):

Select 
    o.ClientID,
    case 
        when DATEDIFF(day, 
            (Select Min(PaidOnDateTime) 
             from [Order] 
             where [Order].ID=[OrderItem].OrderID or [Order].ParentOrder=[OrderItem].OrderID
             ),Getdate()) > 3 and ProductID=4 
        then 0
        else
            TotalAmount 
    end Amount,
    PaidOnDate = (Select Min(PaidOnDateTime) 
                  from [Order] 
                  where [Order].ID=[OrderItem].OrderID or [Order].ParentOrder=[OrderItem].OrderID
                 )
                
from [OrderItem]
    inner join [Order] o 
        on o.Id = [OrderItem].OrderID and OrderStatus not in (9,10)

where 
    (Select Min(PaidOnDateTime) 
     from [Order] 
     where [Order].ID=[OrderItem].OrderID or [Order].ParentOrder=[OrderItem].OrderID
    ) is not null
    
    and ProductID in(4,5)

... and a DerivedTable / DynamicRelation is useful for queries like this:

SELECT o.*
FROM
(
    SELECT  CustomerId 
    FROM    Customers
    WHERE   Country = @country
) c INNER JOIN Orders o ON
  c.CustomerId = o.CustomerId

Actually you should use ScalarQueryExpressions all the way in your query. Here is an example on using extensively those expressions in a query: http://www.llblgening.com/archive/2009/09/llblgen-pro-expressions-and-scalar-queries/

I also don't understand what you mean by "implement the outer part of the query using this derived table"

The outer part is:

Select ClientID, sum(Amount) ChargeBack from ( --- A select list ) Items group by ClientID

That's only one derived table not 2 nested ones. What you have inside doesn't need a derived table.