Ich bekomme, "Meldung 102, Ebene 15, Status 1, Prozedur duckbilledPlatypi, Zeile 21 Inkorrekte Syntax in der Nähe von 'SUM'. "mit dieser gespeicherten MS SQL Server-Prozedur, wenn ich versuche, sie in Visual Studio auszuführen (nachdem ich sie im Server-Explorer erstellt habe):

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 Problemzeile lautet:

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

Im Kontext lautet der gesamte Anweisungsteil:

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, ich müsste die Aussage vielleicht in ein anderes Paar von Parens einschließen:

*(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 exakte err msg damit.

AKTUALISIERE

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, Ebene 15, Status 1, Prozedur variancePriceByProductWithPriceChangePercentage, Zeile 30 Jeder GROUP BY-Ausdruck muss mindestens eine Spalte enthalten, die kein äußerer Verweis ist. Meldung 207, Ebene 16, Status 1, Prozedur variancePriceByProductWithPriceChangePercentage, Zeile 16 Ungültiger Spaltenname 'Usage'. Meldung 207, Ebene 16, Status 1, Prozedur variancePriceByProductWithPriceChangePercentage, Zeile 17 Ungültiger Spaltenname 'Usage'. Meldung 207, Ebene 16, Status 1, Prozedur variancePriceByProductWithPriceChangePercentage, Zeile 20 Ungültiger Spaltenname 'Usage'. Meldung 207, Ebene 16, Status 1, Prozedur variancePriceByProductWithPriceChangePercentage, Zeile 21 Ungültiger Spaltenname 'Usage'. Meldung 207, Ebene 16, Status 1, Prozedur variancePriceByProductWithPriceChangePercentage, Zeile 23 Ungültiger Spaltenname 'Usage'.