Как я могу создать хранимую процедуру, которая будет возвращать набор результатов, содержащий много вычисленных значений?

Это значения, которые мне нужно вернуть из хранимой процедуры:

Description
Week 1 Usage
Week 2 Usage
Usage Variance
Week 1 Price
Week 2 Price
Price Variance
% of Price Variance

Три поля с "Дисперсией" в названии являются вычисляемыми полями на основе других значений, а разделы "Неделя" должны рассчитываться на основе параметров даты. Таким образом, фактические (наиболее подходящие) поля, которые находятся в таблицах базы данных:

Description
Usage
Price
InvoiceDate

Параметры, предоставляемые пользователем:

Unit
DateBegin
DateEnd

Переменные, которые я должен объявить в хранимой процедуре:

DECLARE 
@Week1Begin datetime, 
@Week1End datetime, 
@Week2Begin datetime, 
@Week2End datetime, 
@Week1Usage varchar(30),
@Week2Usage varchar(30),
@Week1Price varchar(30),
@Week2Price varchar(30),
@UsageVariance varchar(30),
@PriceVariance varchar(30),
@PercentageOfPriceVariance varchar(30)

Week1Begin а также Week1End будет рассчитываться на основе значения, предоставленного пользователем в DateBegin параметр (например, если DateBegin это 12/27/15, Week1Begin будет 27.12.2015 и Week1End будет 1/2/2016);

Week2Begin а также Week2End будет аналогичным, но их продолжительность может составлять менее семи дней, а если не более 7 дней (Week2End будет "сокращен" до предшествующей даты по мере необходимости).

Таким образом, несмотря на большое количество вычислений, исходные данные довольно редки. Это в основном четыре поля, упомянутые выше (Description, Usage, Price и InvoiceDate), которые доступны из InvoiceDetail Таблица.

Моя самая большая дилемма заключается в том, что большая часть этих данных возвращается из существующей хранимой процедуры, которую лучше всего скопировать и расширить, но я ни в коем случае не являюсь экспертом по SQL - я могу написать просто SELECT а также UPDATEи т. д., заявления, но танцы с временными таблицами и такими реляционными вращениями в значительной степени за пределами моего понимания.

Поэтому создание новой хранимой процедуры на основе существующей кажется опасной; ОТО, мне интересно, можно ли было бы получить нужные мне данные, используя довольно минимальную хранимую процедуру со сложным оператором запроса, содержащим несколько подвыборов.

Опять же, сложный SQL ни в коем случае не является моей сильной стороной, поэтому я могу быть "далеко" здесь, но вот мой псевдо-SQL:

CREATE Procedure [dbo].[myFunkySP]
    @Unit varchar(25),
    @BegDate datetime,
    @EndDate datetime
As

// Create a mostly-empty-for-now temp table
Select  Description,
    Week1Begin,
    Week1End,
    Week2Begin,
    Week2End,
    Week1Usage,
    Week2Usage,
    Week1Price,
    Week2Price,
    UsageVariance,
    PriceVariance
    PercentageOfPriceVariance
    Into    #TempItems
    From    InvoiceDetail Ind
    Where   Ind.Unit = @Unit
    and Ind.InvoiceDate Between @BegDate and @EndDate


begin

    Update #TempItems set 
    Week1Begin = // val from@BegDate
    Week1End = // do some date math to determine; maybe a Function called GetEndOfWeek(Week1Begin)? 
    Week2Begin = // do some date math to determine (1 day beyond Week1End val)
    Week2End = // val from@EndDate
    Week1Usage = SELECT SUM(USAGE) FROM InvoiceDetail WHERE UNIT = @UNIT, DATE BETWEEN Week1Begin AND Week1End 
    Week2Usage = SELECT SUM(USAGE) FROM InvoiceDetail WHERE UNIT = @UNIT, DATE BETWEEN Week2Begin AND Week2End 
    Week1Price = SELECT SUM(PRICE) FROM InvoiceDetail WHERE UNIT = @UNIT, DATE BETWEEN Week1Begin AND Week1End
    Week2Price = SELECT SUM(PRICE) FROM InvoiceDetail WHERE UNIT = @UNIT, DATE BETWEEN Week2Begin AND Week2End
    UsageVariance = (Week2Usage - Week1Usage)
    PriceVariance = (Week2Price - Week1Price)
    PercentageOfPriceVariance = (PriceVariance div week2Price)

    // now select * from the temp table to return everything?

В противном случае (если вышеприведенное не сработает или неосуществимо), я подумал, что мне, возможно, придется выполнить какой-то запрос 9-X-Uglier-than-a-bag-of-butts, загрязненный подзапросами beaucoup, что-то вроде:

SELECT DESCRIPTION, 
    (SELECT SUM(USAGE) FROM InvoiceDetail
        WHERE UNIT = @UNIT, InvoiceDate BETWEEN :firstWeekBegin AND :firstWeekEnd as Week1Usage), 
    (SELECT SUM(USAGE) FROM InvoiceDetail
        WHERE UNIT = @UNIT, InvoiceDate BETWEEN :secondWeekBegin AND :secondWeekEnd as Week2Usage), 
    SELECT PRICE FROM InvoiceDetail 
        WHERE UNIT = @UNIT, InvoiceDate = firstWeekBegin as Week1Price), 
    (SELECT PRICE FROM InvoiceDetail    
        WHERE UNIT = @UNIT, InvoiceDate = secondWeekBegin as Week2Price), 
FROM InvoiceDetail
WHERE UNIT = @UNIT, InvoiceDate BETWEEN @BEGDATE AND @ENDATE ORDER BY PRODUCTS

Скажи это не так, Джо!

Даже если этот запутанный запрос в некотором роде выполним, он все равно не дает мне вычисленные значения, которые мне нужны (UsageVariance, PriceVariance и PercentageOfPriceVariance).

Итак, что мне нужно сделать, чтобы создать хранимую процедуру, которая будет возвращать нужные мне результаты?

Источник

3 ответа

Решение

Ответ можно найти, следуя совету lad2025 здесь.

По сути, определите их при создании таблицы следующим образом:

USAGEVARIANCE   AS WEEK2USAGE - WEEK1USAGE,
PRICEVARIANCE   AS WEEK2PRICE - WEEK1PRICE,
PRICEVARIANCEPERCENTAGE  AS (WEEK2PRICE - WEEK1PRICE) / WEEK1PRICE

... для большего контекста:

WEEK1USAGE DECIMAL(18,10),
WEEK1PRICE DECIMAL(18,10),
WEEK2USAGE DECIMAL(18,10),
WEEK2PRICE DECIMAL(18,10),
USAGEVARIANCE   AS WEEK2USAGE - WEEK1USAGE,
PRICEVARIANCE   AS WEEK2PRICE - WEEK1PRICE,
PRICEVARIANCEPERCENTAGE  AS (WEEK2PRICE - WEEK1PRICE) / WEEK1PRICE

После этого вам не нужно учитывать их в операторах INSERT - вычисленные значения добавляются автоматически при добавлении значений, на которые они полагаются.

Источник

Расширенный комментарий, а не ответ
Часть ОБНОВЛЕНИЕ не нужна. Сначала вам нужно рассчитать week1end и week2bigin при значении SP. После этого оператор SELECT выглядит прямо -

    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
Источник

Вы должны создать функцию, которая возвращает таблицу.

https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx

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