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.