Где в этом SP 'Неверный синтаксис рядом с'SUM'?

Я получаю сообщение " Msg 102, уровень 15, состояние 1, процедура duckbilledPlatypi, строка 21 с неправильным синтаксисом рядом с" SUM ". " С помощью этой хранимой процедуры MS SQL Server при попытке выполнить ее в Visual Studio (после ее создания из обозревателя сервера):

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

Проблемная линия:

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

В контексте вся часть заявления:

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)) PriceVariance,*

... но я все еще получаю ту же ошибку с этим.

ОБНОВИТЬ

С этим (ответ HoneyBadger):

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

Я получаю следующие отпечатки пальцев:

Сообщение 164, уровень 15, состояние 1, процедура variancePriceByProductWithPriceChangePercentage, строка 30 Каждое выражение GROUP BY должно содержать хотя бы один столбец, который не является внешней ссылкой. Сообщение 207, уровень 16, состояние 1, процедура variancePriceByProductWithPriceChangePercentage, строка 16 Неверное имя столбца "Использование". Сообщение 207, уровень 16, состояние 1, процедура variancePriceByProductWithPriceChangePercentage, строка 17 Неверное имя столбца "Использование". Сообщение 207, уровень 16, состояние 1, процедура variancePriceByProductWithPriceChangePercentage, строка 20 Неверное имя столбца "Использование". Сообщение 207, уровень 16, состояние 1, процедура variancePriceByProductWithPriceChangePercentage, строка 21 Неверное имя столбца "Использование". Сообщение 207, уровень 16, состояние 1, процедура variancePriceByProductWithPriceChangePercentage, строка 23 Недопустимое имя столбца "Использование".

Источник

2 ответа

Решение

Как я уже сказал в комментарии, вы должны добавить group by, Другие отмечали, что у вас пропущена запятая, я также добавил:

    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

редактировать: очевидно, переменные в group by не правы

Источник

Опечатка:

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

вот почему вы получаете синтаксическую ошибку на SUM в следующей строке. у вас по существу SUM(...) SUM(...) который недействителен.

Источник
Другие вопросы по тегам