DateTime =  max(DateTime),
           OrderNo,
           ProductNo,
           Value,
           Brand,
           BrandName,
           LinkUp,
           KDF,
           StartDate,
           CompletionDate,
           ID_AVG,
          Consumption = AVG(Consumption)
FROM
 (....) S1

            GROUP BY


            OrderNo,
            ProductNo,
            Value,
            Brand,
            BrandName,
            LinkUp,
            KDF,
            StartDate,
            CompletionDate,
            ID_AVG