List<CashOutDebitInfo> (Player player, Session ses) { Calendar calendar = Calendar.getInstance(); calendar.add(Calendar.MONTH, -6); Query query = ses.createQuery("from Transaction as t where " + "t.player = :player and t.createDate > :date " + "and t.creditStatus is null and t.type = :type and " + "(t.amount > (select sum(-t2.amount) from t.creditTransactions as t2) " + "or t.creditTransactions is empty) " //+ "where t2.status = :status) or t.creditTransactions is empty) " + "and t.status = :status " + "and t.externalId is not null " + "order by t.createDate"); query.setEntity("player", player); query.setDate("date", calendar.getTime()); query.setInteger("type", TransactionType.DEPOSIT.getId()); query.setInteger("status", TransactionStatus.COMPLETED.getId()); //query.setInteger("status", TransactionStatus.COMPLETED.getId()); List<Transaction> transactions = query.list(); List<CashOutDebitInfo> freeDebits = new ArrayList<CashOutDebitInfo>(); for (Transaction transaction : transactions) { Set<Transaction> credits = transaction.getCreditTransactions(); double sum = 0; for (Transaction credit : credits) { sum += credit.getAmount(); } CashOutDebitInfo info = new CashOutDebitInfo(); info.debit = transaction; info.remainder = transaction.getAmount() - sum; freeDebits.add(info); } return freeDebits; }