Queryspec

Posts   
 
    
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 07-Oct-2013 09:33:17   

This SQL works but I just can't work out how to reproduce it in Queryspec

SELECT
    fd.ID,
    (at.Amount + COALESCE(at.VATAmount, 0)) AS Amount,
    COALESCE(x.Amount, 0) AS AllocatedAmount
FROM FeeDocument fd JOIN AccountsTransaction at ON at.ID = fd.ID
LEFT JOIN
(
    SELECT
        a.TargetAccountsTransactionID,
        SUM((at.Amount + COALESCE(at.VATAmount, 0))) AS Amount
    FROM
    Allocation a JOIN AccountsTransaction at ON at.ID = a.ID
    GROUP BY a.TargetAccountsTransactionID
) x ON x.TargetAccountsTransactionID = fd.ID

(the FROM and JOINs on the same line indicate inheritance)

I won't even show the crap I've come up with trying it but I did want to try to keep the 'inner query' as a query in its own right if possible since it is reusable in other similar queries.

Cheers Simon

simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 07-Oct-2013 10:12:59   
            var qf = new QueryFactory();

            var allocationSummaryQuery = qf.Create()
                .Where(AllocationFields.TargetAccountsTransactionID != DBNull.Value)
                .GroupBy(AllocationFields.TargetAccountsTransactionID)
                .Select(AllocationFields.TargetAccountsTransactionID,
                    (AllocationFields.Amount + AllocationFields.VATAmount.CoalesceToZero()).Sum().As("AA")
                );

            var query = qf.Create()
                .From(qf.FeeDocument
                    .LeftJoin(allocationSummaryQuery.As("X"))
                    .On(FeeDocumentFields.ID == AllocationFields.TargetAccountsTransactionID.SetObjectAlias("X")))
                .Where(FeeDocumentFields.FeeDocumentType == FeeDocumentTypes.Invoice.Value)
                .Select(() => new XAllocInfo
                                  {
                                      ID = FeeDocumentFields.ID.ToValue<int>(),
                                      GrossAmount = FeeDocumentFields.Amount.ToValue<decimal>() + (FeeDocumentFields.VATAmount.ToValue<decimal?>() ?? 0m),
                                      AllocatedAmount = AllocationFields.Amount.SetObjectAlias("X").As("AA").ToValue<decimal?>() ?? 0m
                                  });

This seems to work! Is it the best way?

Walaa avatar
Walaa
Support Team
Posts: 14995
Joined: 21-Aug-2005
# Posted on: 07-Oct-2013 19:52:09   

That's how I would have done it.