Having trouble with a SQL report for Dynamics CRM

The following SQL code creates开发者_如何学C a two-line table with an invoice on one line and payment grouped on the other line. However that's not the desired representation. The real goal is to display it as statement, where the invoice is at the top, followed by a list of payments ordered by date.

Is it possible to write a query to accomplish that, based on the information shown? (Feel free to request more information). Can anyone suggest an approach?

Here is the SQL SELECT code:

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

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

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)