tags:

views:

74

answers:

0

Hello,

I need to generate a report for a system which has a whole bunch of quotes, some of which have been accepted (a receipt has been created), and some of those accepted have since been cancelled. I need a list, per user, with the following things:

-The amount of quotes made
-The amount of accepted quotes
-The total price of accepted quotes
-The amount of cancelled quotes
-The total price of cancelled quotes

There are 5 tables involved: Quote, QuoteItemGroup, QuoteItem, Receipt and ReceiptCancel

I have written the following query, but I am having issues converting it to LINQ

SELECT QuoteUser, COUNT(*) AS TotalAmount, ISNULL(COUNT(Receipts.QuoteID),0) AS GrossAmount, ISNULL(SUM(GrossTotal),0) AS GrossTotal, ISNULL(COUNT(Cancels.QuoteID),0) AS CancelAmount, ISNULL(SUM(CancelTotal),0) AS CancelTotal  
FROM Quote  
LEFT OUTER JOIN  
    (SELECT QuoteID, (QuoteValue + ISNULL(QuoteItemsTotal,0)) AS GrossTotal  
    FROM Receipt  
    INNER JOIN Quote ON QuoteID = ReceiptQuote  
    LEFT OUTER JOIN  
        (SELECT QuoteItemGroupQuote AS Quote, SUM(QuoteItemQty * QuoteItemValue) As QuoteItemsTotal  
        FROM QuoteItem  
        INNER JOIN QuoteItemGroup ON QuoteItemGroupID = QuoteItemGroup  
        GROUP BY QuoteItemGroupQuote) AS QuoteItems ON QuoteItems.Quote = Quote.QuoteID)  
    AS Receipts ON Receipts.QuoteID = Quote.QuoteID  
LEFT OUTER JOIN  
    (SELECT QuoteID, (QuoteValue + ISNULL(QuoteItemsTotal,0)) AS CancelTotal  
    FROM Receipt  
    INNER JOIN ReceiptCancel ON ReceiptCancelReceipt = ReceiptID  
    INNER JOIN Quote ON QuoteID = ReceiptQuote  
    LEFT OUTER JOIN  
        (SELECT QuoteItemGroupQuote AS Quote, SUM(QuoteItemQty * QuoteItemValue) As QuoteItemsTotal  
        FROM QuoteItem  
        INNER JOIN QuoteItemGroup ON QuoteItemGroupID = QuoteItemGroup  
        GROUP BY QuoteItemGroupQuote) AS QuoteItems ON QuoteItems.Quote = Quote.QuoteID)  
    AS Cancels ON Cancels.QuoteID = Quote.QuoteID  
GROUP BY QuoteUser  

If someone thinks the query can be rewritten to give the same output, but be not so confusing, and provide the LINQ code for that, that's fine as well.

I would really appreciate the help, my preference is for lambda-expressions, but an answer without is fine as well.