Query Optimizer Question

SELECT dbo.ProjectTran.ProjectRowID, ProjectTran.RowID,
COALESCE(dbo.ProjectTran.ProjectWBSRowID,0),
PA.BillingTypeRowID,
'REVENUE',
dbo.ProjectTran.PriorInvoiceAmount-(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmoun t),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID =
ProjectTran.RowID) AS Revenue,
dbo.ProjectTran.PriorInvoiceUnits-(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceUnits ),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID =
ProjectTran.RowID) AS BilledUnits,
dbo.ProjectTran.PriorInvoiceAmount -(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmoun t),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID =
ProjectTran.RowID) AS BilledAmount
FROM dbo.ProjectTran
LEFT OUTER JOIN @.ProjectAccount PA ON
dbo.ProjectTran.ProjectAccountRowID = PA.ProjectAccountRowID
-- LEFT OUTER JOIN @.ProjectAccount PA2 ON
dbo.ProjectTran.LastInvoiceProjectAccountRowID = PA2.ProjectAccountRowID
INNER JOIN dbo.ProjectDoc ON dbo.ProjectTran.ProjectDocRowID =
dbo.ProjectDoc.RowID
Where dbo.ProjectTran.ProjectRowID in (Select RowID From #ProjectTemp)
AND dbo.ProjectTran.ProjectDocRowID IS NOT NULL
AND dbo.ProjectDoc.StatusRowID = 1003
AND ProjectTran.RowID NOT IN (Select TransactionRowID From
@.ProjectDataDetail WHERE AccountTypeDescription = 'REVENUE')
Amos Soma wrote:
> SELECT dbo.ProjectTran.ProjectRowID, ProjectTran.RowID,
> <SNIP>
That's a very nice query. What's the question :-)
David Gugick - SQL Server MVP
Quest Software
|||I'm sorry - I didn't mean to post this in this condition. Another post is
coming.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u6ggfDzMGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Amos Soma wrote:
> That's a very nice query. What's the question :-)
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>