Wo ist die 'falsche Syntax in der Nähe' SUM '' in diesem SP?

Ich bekomme, " Msg 102, Level 15, State 1, Prozedur duckbilledPlatypi, Zeile 21 Falsche Syntax in der Nähe von 'SUM'. " Mit diesem MS SQL server gespeicherten Prozedur, wenn ich versuche, es in Visual Studio auszuführen (nach dem Erstellen aus dem server Explorer ):

CREATE PROCEDURE [dbo].[duckbilledPlatypi] @Unit varchar(25), @BegDate datetime, @EndDate datetime AS DECLARE @Week1End datetime, @Week2begin datetime Select Description, @BegDate BegDate, @Week1End Week1End, @Week1End Week2Begin, @EndDate EndDate, SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Usage ELSE 0 END) Week1Usage, SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) Week2Usage, SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) Week1Price, SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Price ELSE 0 END) - SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Usage ELSE 0 END) UsageVariance SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) - SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) PriceVariance, (SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) - SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) ) / SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) PercentageOfPriceVariance From InvoiceDetail Ind Where Ind.Unit = @Unit AND @Ind.InvoiceDate BETWEEN @BegDate AND @EndDate 

Die Problemlinie ist:

 SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) - 

Im Kontext ist der gesamte Aussageteil:

 SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) - SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) PriceVariance, 

?

Ich dachte, vielleicht musste ich die Aussage in einem anderen Paar von Parens, so wie:

 *(SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) - SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END)) PriceVariance,* 

… aber ich bekomme immer noch die selbe genaue msg damit.

AKTUALISIEREN

Mit diesem (HoneyBadgers Antwort):

 CREATE PROCEDURE [dbo].[variancePriceByProductWithPriceChangePercentage] @Unit varchar(25), @BegDate datetime, @EndDate datetime AS DECLARE @Week1End datetime, @Week2begin datetime Select Description, @BegDate BegDate, @Week1End Week1End, @Week1End Week2Begin, @EndDate EndDate, SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Usage ELSE 0 END) Week1Usage, SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) Week2Usage, SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) Week1Price, SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Price ELSE 0 END) - SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Usage ELSE 0 END) UsageVariance, SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) - SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) PriceVariance, (SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) - SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) ) / SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) PercentageOfPriceVariance From InvoiceDetail Ind Where Ind.Unit = @Unit AND Ind.InvoiceDate BETWEEN @BegDate AND @EndDate Group By Description, @BegDate, @Week1End, @Week1End, @EndDate 

Ich bekomme die folgenden fingerwags:

Msg 164, Level 15, State 1, ProzedurvariantePriceByProductWithPriceChangePercentage, Zeile 30 Jeder GROUP BY-Ausdruck muss mindestens eine Spalte enthalten, die keine äußere reference ist. Msg 207, Level 16, State 1, ProzedurvariantePriceByProductWithPriceChangePercentage, Zeile 16 Ungültiger Spaltenname 'Usage'. Msg 207, Level 16, State 1, ProzedurvariantePriceByProductWithPriceChangePercentage, Zeile 17 Ungültiger Spaltenname 'Usage'. Msg 207, Level 16, State 1, ProzedurvariantePriceByProductWithPriceChangePercentage, Zeile 20 Ungültiger Spaltenname 'Usage'. Msg 207, Level 16, State 1, ProzedurvariantePriceByProductWithPriceChangePercentage, Zeile 21 Ungültiger Spaltenname 'Usage'. Msg 207, Level 16, State 1, ProzedurvariantePriceByProductWithPriceChangePercentage, Zeile 23 Ungültiger Spaltenname 'Usage'.

    Wie ich schon im Kommentar gesagt habe, solltest du eine group by hinzufügen. Andere haben darauf hingewiesen, dass Sie ein fehlendes Komma hatten, fügte ich das auch hinzu:

      Select Description, @BegDate BegDate, @Week1End Week1End, @Week1End Week2Begin, @EndDate EndDate, SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Usage ELSE 0 END) Week1Usage, SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) Week2Usage, SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) Week1Price, SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Price ELSE 0 END) - SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Usage ELSE 0 END) UsageVariance, SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) - SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) PriceVariance, (SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) - SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) ) / SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) PercentageOfPriceVariance From InvoiceDetail Ind Where Ind.Unit = @Unit AND Ind.InvoiceDate BETWEEN @BegDate AND @EndDate Group By Description 

    edit: Anscheinend sind die variables in der group by falsch

    Typo:

     SUM(CASE [..snip...][ END) UsageVariance ^--missing comma 

    weshalb bekommst du den Syntaxerrors auf der SUM in der nächsten Zeile. Sie haben im Wesentlichen SUM(...) SUM(...) was ungültig ist.