Hi there,

I have a database that has debtor records and these are organised into debtorgroups (one group, many debtors).

I have a requirement to provide a report that shows - number of debtors - number of groups that have that many debtors

An added complication is that it must show only groups of debtors that have a collective total debt of zero (ascertained using the DebtorBalance table).

This SQL does exactly what I need:

SELECT X.DebtorCount, COUNT(*) AS GroupCount
FROM
(
    SELECT DebtorGroupUID, COUNT(*) AS DebtorCount
    FROM DebtorDetails
    WHERE DebtorGroupUID IN
    (
        SELECT DebtorDetails.DebtorGroupUID
        FROM DebtorDetails
        INNER JOIN DebtorGroup ON DebtorDetails.DebtorGroupUID = DebtorGroup.DebtorGroupUID
        INNER JOIN DebtorBalance ON DebtorDetails.MostRecentDebtorBalanceUID = DebtorBalance.DebtorBalanceUID
        GROUP BY DebtorDetails.DebtorGroupUID
        HAVING SUM(DebtorBalance.TotalDebt)=0
    )
    GROUP BY DebtorgroupUID
) AS X
GROUP BY X.DebtorCount
ORDER BY X.DebtorCount

I'm completely stuck how to do this with LLBLGen. Can anyone assist please?

(**SelfServicing **architecture)

Thanks

SELECT DebtorDetails.DebtorGroupUID FROM DebtorDetails INNER JOIN DebtorGroup ON DebtorDetails.DebtorGroupUID = DebtorGroup.DebtorGroupUID INNER JOIN DebtorBalance ON DebtorDetails.MostRecentDebtorBalanceUID = DebtorBalance.DebtorBalanceUID GROUP BY DebtorDetails.DebtorGroupUID HAVING SUM(DebtorBalance.TotalDebt)=0

By the way I think the above inner query can be simplified as follows:

SELECT DebtorDetails.DebtorGroupUID
        FROM DebtorDetails
        INNER JOIN DebtorBalance ON DebtorDetails.MostRecentDebtorBalanceUID = DebtorBalance.DebtorBalanceUID
        WHERE DebtorBalance.TotalDebt = 0

Anyway, for the outer query.

SELECT .....
FROM
( Another Query ) as X

You should be using a dynamic list with v.2.6 derived table new feature.

And for the next inner query.

SELECT DebtorGroupUID, COUNT(*) AS DebtorCount
    FROM DebtorDetails
    WHERE DebtorGroupUID IN
    (
        Another query
    )

You should be using a dynamic list with a FieldCompareSetPredicate to implement the IN clause.

Once again Walaa, thank you very much for your help. And you're right, that query can be simplified Cheers