1
WITH [ProgressInvoicesPeriodCost] AS
(
    SELECT
        ProgressInvoices.ProjectId
        , ProgressInvoices.PeriodId
        , SUM(ProgressInvoiceDetails.TotalThisInvoice) AS TotalThisInvoice
    FROM
        CostManagement_ProgressInvoices AS ProgressInvoices
        LEFT JOIN CostManagement_ProgressInvoiceDetails AS ProgressInvoiceDetails
            ON  ProgressInvoices.Id = ProgressInvoiceDetails.ProgressInvoiceId
    WHERE
        ProgressInvoices.ProjectId IN (@Projectlisting)

    GROUP BY
        ProgressInvoices.ProjectId
        , ProgressInvoices.PeriodId
)

[CommitmentCOPeriodCost] AS
(
    SELECT
        CommitmentCOs.ProjectId
        , CommitmentCODetails.PeriodId
        , SUM(CommitmentCODetails.AmountApproved) AS AmountApproved
    FROM
        CostManagement_CommitmentCOs AS CommitmentCOs
        LEFT JOIN CostManagement_CommitmentCODetails AS CommitmentCODetails
            ON  CommitmentCOs.Id = CommitmentCODetails.CommitmentCOId
    WHERE
        CommitmentCOs.ProjectId IN (@Projectlisting) AND CommitmentCOs.PostAsId = 2

    GROUP BY
        CommitmentCOs.ProjectId
        , CommitmentCODetails.PeriodId
)

[CommitmentPeriodCost] AS
(
    SELECT
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
        , SUM(CommitmentDetails.TotalCost) AS TotalCost

    FROM
        CostManagement_Commitments AS Commitments
        LEFT JOIN CostManagement_CommitmentDetails AS CommitmentDetails
            ON  Commitments.Id = CommitmentDetails.CommitmentId
    WHERE
        Commitments.ProjectId IN (@Projectlisting)

    GROUP BY
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
)




SELECT
    ProjectId
    ,ProjectName
    ,'INV' as GroupRecordType
    ,'INV' as RecordType
    , PeriodId
    , Periods.Period
    , TotalThisInvoice AS TotalCost
    , (
            SELECT  SUM(TotalThisInvoice)
            FROM    ProgressInvoicesPeriodCost AS RunningCost
            WHERE   ProjectId = ProgressInvoicesPeriodCost.ProjectId
                    AND PeriodId <= ProgressInvoicesPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    [ProgressInvoicesPeriodCost]
LEFT OUTER JOIN Periods
            ON  ProgressInvoicesPeriodCost.PeriodId = Periods.Id
LEFT OUTER JOIN Projects
            ON ProgressInvoicesPeriodCost.ProjectId = Projects.Id

Where (Period <= @ToPeriod) and ProgressInvoicesPeriodCost.ProjectId IN (@Projectlisting)AND 'INV' IN (@RecordTypes)


ORDER BY
    ProjectId
    , Period


UNION


SELECT
    ProjectId
    ,ProjectName
    ,'COM' as GroupRecordType
    ,'CO' as RecordType
    , PeriodId
    , Periods.Period
    , AmountApproved AS TotalCost
    , (
            SELECT  SUM(AmountApproved)
            FROM    CommitmentCOPeriodCost AS RunningCost
            WHERE   ProjectId = CommitmentCOPeriodCost.ProjectId
                    AND PeriodId <= CommitmentCOPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    [CommitmentCOPeriodCost]
LEFT OUTER JOIN Periods
            ON  CommitmentCOPeriodCost.PeriodId = Periods.Id
LEFT OUTER JOIN Projects
            ON CommitmentCOPeriodCost.ProjectId = Projects.Id

Where (Period <= @ToPeriod) and CommitmentCOPeriodCost.ProjectId IN (@Projectlisting)AND 'CO' IN (@RecordTypes)


ORDER BY
    ProjectId
    , Period

UNION

SELECT
    ProjectId
    ,ProjectName
    ,'COM' as GroupRecordType
    ,'COM' as RecordType
    , PeriodId
    , Periods.Period
    , TotalCost
    , (
            SELECT  SUM(TotalCost)
            FROM    CommitmentPeriodCost AS RunningCost
            WHERE   ProjectId = CommitmentPeriodCost.ProjectId
                    AND PeriodId <= CommitmentPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    [CommitmentPeriodCost]
LEFT OUTER JOIN Periods
            ON  CommitmentPeriodCost.PeriodId = Periods.Id
LEFT OUTER JOIN Projects
            ON CommitmentPeriodCost.ProjectId = Projects.Id

Where (Period <= @ToPeriod) and CommitmentPeriodCost.ProjectId IN (@Projectlisting)AND 'COM' IN (@RecordTypes)

ORDER BY
    ProjectId
    , Period

I need help trying to UNION these 3 WITH AS sections. I get the following error: Error Message when trying to UNION Multiple WITH AS!

Query with only 1 WITH AS being used

0

2 Answers 2

3

You have two problems. First you need commas between the CTEs. Second you can only have one ORDER BY for a group of UNIONed statements. Just use one ORDER BY at the very end.

WITH [ProgressInvoicesPeriodCost] AS
(
    SELECT
        ProgressInvoices.ProjectId
        , ProgressInvoices.PeriodId
        , SUM(ProgressInvoiceDetails.TotalThisInvoice) AS TotalThisInvoice
    FROM
        CostManagement_ProgressInvoices AS ProgressInvoices
        LEFT JOIN CostManagement_ProgressInvoiceDetails AS ProgressInvoiceDetails
            ON  ProgressInvoices.Id = ProgressInvoiceDetails.ProgressInvoiceId
    WHERE
        ProgressInvoices.ProjectId IN (@Projectlisting)

    GROUP BY
        ProgressInvoices.ProjectId
        , ProgressInvoices.PeriodId
),

[CommitmentCOPeriodCost] AS
(
    SELECT
        CommitmentCOs.ProjectId
        , CommitmentCODetails.PeriodId
        , SUM(CommitmentCODetails.AmountApproved) AS AmountApproved
    FROM
        CostManagement_CommitmentCOs AS CommitmentCOs
        LEFT JOIN CostManagement_CommitmentCODetails AS CommitmentCODetails
            ON  CommitmentCOs.Id = CommitmentCODetails.CommitmentCOId
    WHERE
        CommitmentCOs.ProjectId IN (@Projectlisting) AND CommitmentCOs.PostAsId = 2

    GROUP BY
        CommitmentCOs.ProjectId
        , CommitmentCODetails.PeriodId
),

[CommitmentPeriodCost] AS
(
    SELECT
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
        , SUM(CommitmentDetails.TotalCost) AS TotalCost

    FROM
        CostManagement_Commitments AS Commitments
        LEFT JOIN CostManagement_CommitmentDetails AS CommitmentDetails
            ON  Commitments.Id = CommitmentDetails.CommitmentId
    WHERE
        Commitments.ProjectId IN (@Projectlisting)

    GROUP BY
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
)




SELECT
    ProjectId
    ,ProjectName
    ,'INV' as GroupRecordType
    ,'INV' as RecordType
    , PeriodId
    , Periods.Period
    , TotalThisInvoice AS TotalCost
    , (
            SELECT  SUM(TotalThisInvoice)
            FROM    ProgressInvoicesPeriodCost AS RunningCost
            WHERE   ProjectId = ProgressInvoicesPeriodCost.ProjectId
                    AND PeriodId <= ProgressInvoicesPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    [ProgressInvoicesPeriodCost]
LEFT OUTER JOIN Periods
            ON  ProgressInvoicesPeriodCost.PeriodId = Periods.Id
LEFT OUTER JOIN Projects
            ON ProgressInvoicesPeriodCost.ProjectId = Projects.Id

Where (Period <= @ToPeriod) and ProgressInvoicesPeriodCost.ProjectId IN (@Projectlisting)AND 'INV' IN (@RecordTypes)



UNION


SELECT
    ProjectId
    ,ProjectName
    ,'COM' as GroupRecordType
    ,'CO' as RecordType
    , PeriodId
    , Periods.Period
    , AmountApproved AS TotalCost
    , (
            SELECT  SUM(AmountApproved)
            FROM    CommitmentCOPeriodCost AS RunningCost
            WHERE   ProjectId = CommitmentCOPeriodCost.ProjectId
                    AND PeriodId <= CommitmentCOPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    [CommitmentCOPeriodCost]
LEFT OUTER JOIN Periods
            ON  CommitmentCOPeriodCost.PeriodId = Periods.Id
LEFT OUTER JOIN Projects
            ON CommitmentCOPeriodCost.ProjectId = Projects.Id

Where (Period <= @ToPeriod) and CommitmentCOPeriodCost.ProjectId IN (@Projectlisting)AND 'CO' IN (@RecordTypes)


UNION

SELECT
    ProjectId
    ,ProjectName
    ,'COM' as GroupRecordType
    ,'COM' as RecordType
    , PeriodId
    , Periods.Period
    , TotalCost
    , (
            SELECT  SUM(TotalCost)
            FROM    CommitmentPeriodCost AS RunningCost
            WHERE   ProjectId = CommitmentPeriodCost.ProjectId
                    AND PeriodId <= CommitmentPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    [CommitmentPeriodCost]
LEFT OUTER JOIN Periods
            ON  CommitmentPeriodCost.PeriodId = Periods.Id
LEFT OUTER JOIN Projects
            ON CommitmentPeriodCost.ProjectId = Projects.Id

Where (Period <= @ToPeriod) and CommitmentPeriodCost.ProjectId IN (@Projectlisting)AND 'COM' IN (@RecordTypes)

ORDER BY
    ProjectId
    , Period
1
  • 4
    You may also use UNION ALL and avoid the duplicate checks that UNION does. Oct 23, 2014 at 18:18
5

Just a word about nested CTEs, they get evaluated each time they are referenced. To put it another way, they do not behave like temp tables, the data is not cached in some special way. So for a CTE referenced multiple times as yours are ( ie once in the UNION, once in the RunningTotal subquery ), there can be a performance penalty. I set up a simple rig with your query and only a few hundred rows to demonstrate this. The the first call (43,070 reads) is your query, the next four together are my rewrite using temp tables to cache the results of the CTEs, registering only 702 reads in total:

IF OBJECT_ID('tempdb..#ProgressInvoicesPeriodCost') IS NOT NULL DROP TABLE tempdb..#ProgressInvoicesPeriodCost
IF OBJECT_ID('tempdb..#CommitmentCOPeriodCost') IS NOT NULL DROP TABLE tempdb..#CommitmentCOPeriodCost
IF OBJECT_ID('tempdb..#CommitmentPeriodCost') IS NOT NULL DROP TABLE tempdb..#CommitmentPeriodCost

;WITH [ProgressInvoicesPeriodCost] AS
(
    SELECT
        ProgressInvoices.ProjectId
        , ProgressInvoices.PeriodId
        , SUM(ProgressInvoiceDetails.TotalThisInvoice) AS TotalThisInvoice
    FROM
        CostManagement_ProgressInvoices AS ProgressInvoices
        LEFT JOIN CostManagement_ProgressInvoiceDetails AS ProgressInvoiceDetails
            ON  ProgressInvoices.Id = ProgressInvoiceDetails.ProgressInvoiceId
    WHERE
        ProgressInvoices.ProjectId = @Projectlisting

    GROUP BY
        ProgressInvoices.ProjectId
        , ProgressInvoices.PeriodId
)
SELECT *
INTO #ProgressInvoicesPeriodCost
FROM [ProgressInvoicesPeriodCost]

;WITH [CommitmentCOPeriodCost] AS
(
    SELECT
        CommitmentCOs.ProjectId
        , CommitmentCODetails.PeriodId
        , SUM(CommitmentCODetails.AmountApproved) AS AmountApproved
    FROM
        CostManagement_CommitmentCOs AS CommitmentCOs
        LEFT JOIN CostManagement_CommitmentCODetails AS CommitmentCODetails
            ON  CommitmentCOs.Id = CommitmentCODetails.CommitmentCOId
    WHERE
        CommitmentCOs.ProjectId = @Projectlisting 
        AND CommitmentCOs.PostAsId = 2

    GROUP BY
        CommitmentCOs.ProjectId
        , CommitmentCODetails.PeriodId
)
SELECT *
INTO #CommitmentCOPeriodCost
FROM [CommitmentCOPeriodCost]

;WITH [CommitmentPeriodCost] AS
(
    SELECT
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
        , SUM(CommitmentDetails.TotalCost) AS TotalCost

    FROM
        CostManagement_Commitments AS Commitments
        LEFT JOIN CostManagement_CommitmentDetails AS CommitmentDetails
            ON  Commitments.Id = CommitmentDetails.CommitmentId
    WHERE
        Commitments.ProjectId = @Projectlisting

    GROUP BY
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
)
SELECT *
INTO #CommitmentPeriodCost
FROM [CommitmentPeriodCost]


SELECT

     ProjectId
    ,ProjectName
    ,'INV' as GroupRecordType
    ,'INV' as RecordType
    , PeriodId
    , Periods.Period
    , TotalThisInvoice AS TotalCost
    , (
            SELECT  SUM(TotalThisInvoice)
            FROM    #ProgressInvoicesPeriodCost AS RunningCost
            WHERE   ProjectId = ProgressInvoicesPeriodCost.ProjectId
                    AND PeriodId <= ProgressInvoicesPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    #ProgressInvoicesPeriodCost ProgressInvoicesPeriodCost
LEFT OUTER JOIN Periods
            ON  ProgressInvoicesPeriodCost.PeriodId = Periods.Id
LEFT OUTER JOIN Projects
            ON ProgressInvoicesPeriodCost.ProjectId = Projects.Id

WHERE (Period <= @ToPeriod)
  AND ProgressInvoicesPeriodCost.ProjectId = @Projectlisting
  AND 'INV' = @RecordTypes

UNION ALL

SELECT
    ProjectId
    ,ProjectName
    ,'COM' as GroupRecordType
    ,'CO' as RecordType
    , PeriodId
    , Periods.Period
    , AmountApproved AS TotalCost
    , (
            SELECT  SUM(AmountApproved)
            FROM    #CommitmentCOPeriodCost AS RunningCost
            WHERE   ProjectId = CommitmentCOPeriodCost.ProjectId
                    AND PeriodId <= CommitmentCOPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    #CommitmentCOPeriodCost CommitmentCOPeriodCost
LEFT OUTER JOIN Periods
            ON  CommitmentCOPeriodCost.PeriodId = Periods.Id
LEFT OUTER JOIN Projects
            ON CommitmentCOPeriodCost.ProjectId = Projects.Id

WHERE (Period <= @ToPeriod) and CommitmentCOPeriodCost.ProjectId = @Projectlisting
 AND 'CO' = @RecordTypes

UNION ALL

SELECT
    ProjectId
    ,ProjectName
    ,'COM' as GroupRecordType
    ,'COM' as RecordType
    , PeriodId
    , Periods.Period
    , TotalCost
    , (
            SELECT  SUM(TotalCost)
            FROM    #CommitmentPeriodCost AS RunningCost
            WHERE   ProjectId = CommitmentPeriodCost.ProjectId
                    AND PeriodId <= CommitmentPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    #CommitmentPeriodCost CommitmentPeriodCost
LEFT OUTER JOIN Periods
            ON  CommitmentPeriodCost.PeriodId = Periods.Id
LEFT OUTER JOIN Projects
            ON CommitmentPeriodCost.ProjectId = Projects.Id

Where (Period <= @ToPeriod) and CommitmentPeriodCost.ProjectId = @Projectlisting
AND 'COM' = @RecordTypes

ORDER BY
    ProjectId
    , Period

Result: enter image description here

Try this rewrite with your data, compare reads and let us know if you see a difference.

Another word about 'In' - it does not behave the way you think it does. Therefore I have converted all 'In' references to '=' in my query. If this does not work for you, can please post sample values for your variables @Projectlisting, @ToPeriod and @RecordTypes. In the meantime you may wish to review Erland Sommarskog's excellent article on this topic

http://www.sommarskog.se/arrays-in-sql.html

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.