Having trouble with a SQL report for Dynamics CRM

Question

I am trying to create a statement like report for invoices, I have it so far that it displays the invoice and the amount due and also the payment if it has been paid, I have all this in a table with the invoice on 1 line and the payments on another with them grouped. What i really what to achieve is to have them ordered by date and for it to look like a statement.

Is this even possible does anyone have any ideas on what I could do to achieve this.

Below is my current SQL Select statement.

SELECT     FilteredInvoice.accountidname, FilteredInvoice.createdon, FilteredInvoice.duedate, FilteredInvoice.invoicenumber, FilteredInvoice.statecodename, 
                  FilteredInvoice.totalamount_base, FilteredMag_Payment.mag_paymentdate, FilteredMag_Payment.mag_amount_base, GETDATE() AS Today
FROM         FilteredInvoice LEFT OUTER JOIN
                  FilteredAccount ON FilteredInvoice.accountid = FilteredAccount.accountid LEFT OUTER JOIN
                  FilteredMag_Payment ON FilteredInvoice.invoiceid = FilteredMag_Payment.mag_invoiceid
WHERE     (FilteredInvoice.statecodename <> N'Canceled')
ORDER BY FilteredInvoice.createdon

Answer 1

Looks to me like the payment info should be in the same row as the invoice. The only way you get a second row is if you have two payments. Since you are sorting on Invoice date, both rows will have the same date and sort next to each other.

My guess, from what you have said, is that you want to sort on Invoice Date if there isn't a payment and payment date if there is one. Try:

Order by Coalesce(FilteredMag_Payment.mag_paymentdate, FilteredInvoice.createdon)

Answer 2

There are some oddities about the original query - is the Account ID Name really held on the Invoice table, rather than the Account table? The left outer join from Invoice to Account also makes it look as though there could be Invoices without corresponding Accounts - it would be more normal to assume the converse, especially in a statement report.

Assuming the original query is selecting the required data correctly, I suggest:

SELECT    FilteredInvoice.accountidname, 
    FilteredInvoice.createdon,
    FilteredInvoice.createdon AS sort_date,
    FilteredInvoice.duedate,
    FilteredInvoice.invoicenumber,
    FilteredInvoice.statecodename, 
    FilteredInvoice.totalamount_base,
    CONVERT(datetime,NULL) AS mag_paymentdate,
    0 AS mag_amount_base,
    GETDATE() AS Today
FROM    FilteredInvoice 
LEFT OUTER JOIN    FilteredAccount ON FilteredInvoice.accountid = FilteredAccount.accountid 
WHERE    (FilteredInvoice.statecodename <> 'Canceled')
UNION ALL
SELECT    FilteredInvoice.accountidname, 
    FilteredInvoice.createdon,
    FilteredInvoice.createdon AS sort_date,
    FilteredInvoice.duedate,
    FilteredInvoice.invoicenumber,
    FilteredInvoice.statecodename, 
    FilteredInvoice.totalamount_base,
    FilteredMag_Payment.mag_paymentdate,
    FilteredMag_Payment.mag_amount_base,
    GETDATE() AS Today
FROM    FilteredInvoice 
LEFT OUTER JOIN    FilteredAccount ON FilteredInvoice.accountid = FilteredAccount.accountid 
JOIN    FilteredMag_Payment ON FilteredInvoice.invoiceid = FilteredMag_Payment.mag_invoiceid
WHERE    (FilteredInvoice.statecodename <> 'Canceled')
ORDER BY 3