|
Home > Archive > MS SQL Server > February 2006 > Query Optimizer Question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Query Optimizer Question
|
|
| Amos Soma 2006-02-16, 1:23 pm |
|
SELECT dbo.ProjectTran.ProjectRowID, ProjectTran.RowID,
COALESCE(dbo.ProjectTran.ProjectWBSRowID,0),
PA.BillingTypeRowID,
'REVENUE',
dbo.ProjectTran.PriorInvoiceAmount-(SELECT
COALESCE(SUM(dbo. ProjectInvoiceDetail
.InvoiceAmount),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
.InvoiceAmount),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. LastInvoiceProjectAc
countRowID = 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 AccountTypeDescripti
on = 'REVENUE')
| |
| David Gugick 2006-02-17, 7:23 am |
| 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
| |
| Amos Soma 2006-02-17, 7:23 am |
| 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
>
|
|
|
|
|