none
Подсчёт итогов и выполнение процедуры RRS feed

  • Вопрос

  • Добрый день.

    Имеется следующая хранимая процедура:

    CREATE PROCEDURE DataForPeriodWithPlace 
    	@BeginTime SmallDateTime,
    	@EndTime SmallDateTime,
    	@Location TinyInt/*,
    	@Sum Numeric(10,3) OUT*/
    AS
    BEGIN
    	SET NOCOUNT ON;
    
    WITH 
    cteStart AS 
    	(SELECT CounterID, CounterData 
    	FROM Testimony 
    	WHERE TimeDate = @BeginTime), 
    
    cteEnd AS 
    	(SELECT CounterID, CounterData 
    	FROM Testimony 
    	WHERE TimeDate = @EndTime)
    
    SELECT 
    	L.PlaceName AS 'Размещение', 
    	C.CounterNote AS 'Счётчик', 
    	E.CounterData - S.CounterData AS 'Показание'
    FROM 
    	cteStart S INNER JOIN cteEnd E ON S.CounterID = E.CounterID
    			INNER JOIN Counter C ON S.CounterID = C.CounterID 
    			INNER JOIN Location L ON C.PlaceID = L.PlaceID
    WHERE L.PlaceID = @Location
    --SET @Sum = SUM(CounterData)
    END
    GO

    Как занести в переменную @Sum сумму значений последнего столбца?

    При попытке выполнить эту процедуру следующим запросом выдаётся ошибка:

    EXEC DataForPeriodWithPlace
    	@BeginTime = Convert(SmallDateTime, '2012-05-31 13:54:00', 120)),
    	@EndTime = Convert(SmallDateTime, '2012-06-1 13:54:00', 120)),
    	@Location = 2
    Неверный синтаксис около Convert.

Ответы

  • Вы не можете передавать выражение в качестве параметра процедуры. Это ошибка №1. 

    №2 - сумму надо считать через select. 

    select @sum = sum(CounterData) from blah-blah-blah

    • Помечено в качестве ответа J.Lion 7 июня 2012 г. 6:37
  • Уберите char. Нормальным способом передачи даты является кодирование 'YYYYMMDD HH:MM:SS'. Оно будет работать при любых региональных настройках.
    • Помечено в качестве ответа J.Lion 8 июня 2012 г. 6:42
  • Попробовал Ваш способ, не работает.

    EXEC DataForPeriod
    	@BeginTime = 20120531 13:54:00,
    	@EndTime = 20120601 13:54:00,
    	@Location = 2


    А даты в апострофы кто за вас заключать будет?

    http://www.t-sql.ru

    • Помечено в качестве ответа J.Lion 8 июня 2012 г. 6:42
    Модератор
  • Нужно посчитать сумму в полученном столбце 'показания'.

    declare @t table ( field char(1), val int )
    insert into @t
    values( 'a', 1 ), ( 'b', 3 ), ( 'c', 5 )
    
    select case when grouping(field)  = 1 then 'Итого:' else field end as field
         , sum(val) as val 
    from @t
    group by grouping sets ( (field), () )


    http://www.t-sql.ru

    • Помечено в качестве ответа J.Lion 8 июня 2012 г. 12:45
    8 июня 2012 г. 11:36
    Модератор
  • select 'Размещение', 'Счётчик', sum('Показание')
    from
    (
    
    SELECT 
    	L.PlaceName AS 'Размещение', 
    	C.CounterNote AS 'Счётчик', 
    	E.CounterData - S.CounterData AS 'Показание'
    FROM 
    	cteStart S INNER JOIN cteEnd E ON S.CounterID = E.CounterID
    			INNER JOIN Counter C ON S.CounterID = C.CounterID 
    			INNER JOIN Location L ON C.PlaceID = L.PlaceID
    WHERE L.PlaceID = @Location
    
    ) t
    group by
    grouping sets ( ( [Размещение], [Счётчик] ), () )


    http://www.t-sql.ru

    • Помечено в качестве ответа J.Lion 8 июня 2012 г. 13:12
    8 июня 2012 г. 12:52
    Модератор

Все ответы

  • Вы не можете передавать выражение в качестве параметра процедуры. Это ошибка №1. 

    №2 - сумму надо считать через select. 

    select @sum = sum(CounterData) from blah-blah-blah

    • Помечено в качестве ответа J.Lion 7 июня 2012 г. 6:37
  • Немного изменил процедуру, чтобы избежать ошибки №1:

    ALTER PROCEDURE [dbo].[DataForPeriod] 
    	@BeginTime Char,
    	@EndTime Char,
    	@Location TinyInt/*,
    	@Sum Numeric(10,3) OUT*/
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    WITH 
    cteStart AS 
    	(SELECT CounterID, CounterData 
    	FROM Testimony 
    	WHERE TimeDate = Convert(SmallDateTime, @BeginTime, 120)), 
    
    cteEnd AS 
    	(SELECT CounterID, CounterData 
    	FROM Testimony 
    	WHERE TimeDate = Convert(SmallDateTime, @EndTime, 120))
    
    SELECT 
    	L.PlaceName AS 'Размещение', 
    	C.CounterNote AS 'Счётчик', 
    	E.CounterData - S.CounterData AS 'Показание'
    FROM 
    	cteStart S INNER JOIN cteEnd E ON S.CounterID = E.CounterID
    			INNER JOIN Counter C ON S.CounterID = C.CounterID 
    			INNER JOIN Location L ON C.PlaceID = L.PlaceID
    WHERE L.PlaceID = @Location
    --SELECT @Sum = SUM(CounterData) FROM
    END

    Так пытаюсь её выполнить:

    EXEC DataForPeriod
    	@BeginTime = '2012-05-31 13:54:00',
    	@EndTime = '2012-06-1 13:54:00',
    	@Location = 2

    И всё равно выходит ошибка:

    Сообщение 295, уровень 16, состояние 3, процедура DataForPeriod, строка 19

    Conversion failed when converting character string to smalldatetime data type.

    С помощью того же оператора в дату конвертировал внутри запроса, всё было хорошо. Может быть, есть другой способ передать дату в процедуру?

    Насчёт ошибки №2. Какую таблицу указывать в блоке FROM? Результату выполнения SELECT можно как-нибудь имя присвоить?


    С уважением, J.Lion


    • Изменено J.Lion 7 июня 2012 г. 7:02
  • Уберите char. Нормальным способом передачи даты является кодирование 'YYYYMMDD HH:MM:SS'. Оно будет работать при любых региональных настройках.
    • Помечено в качестве ответа J.Lion 8 июня 2012 г. 6:42
  • Попробовал Ваш способ, не работает.

    EXEC DataForPeriod
    	@BeginTime = 20120531 13:54:00,
    	@EndTime = 20120601 13:54:00,
    	@Location = 2

    Выдаётся ошибка:

    Неверный синтаксис около 13


    С уважением, J.Lion

  • Попробовал Ваш способ, не работает.

    EXEC DataForPeriod
    	@BeginTime = 20120531 13:54:00,
    	@EndTime = 20120601 13:54:00,
    	@Location = 2


    А даты в апострофы кто за вас заключать будет?

    http://www.t-sql.ru

    • Помечено в качестве ответа J.Lion 8 июня 2012 г. 6:42
    Модератор
  • Прошу прощения, теперь всё работает

    С уважением, J.Lion

  • А что насчёт подсчёта итогов? Как мне обозначить результат выполнения запроса, чтобы использовать в блоке FROM?

    С уважением, J.Lion

  • Я не понимаю, что именно должен считать ваш запрос. Что такое cteStart и cteEnd? Вам нужны итоги по вашему селекту, который написан выше? Как вариант, можете предварительно сложить результаты select в промежуточную временную таблицу, а потом выдать из неё результат и вторым запросом посчитать сумму.
    8 июня 2012 г. 11:07
  • Мой запрос вычисляет разность показаний CounterData в разное время. Это работает. Нужно посчитать сумму в полученном столбце 'показания'. Я не знаю, как сохранить временную таблицу, чтобы потом к ней обратиться. Такая подсказка была бы очень кстати

    С уважением, J.Lion

    8 июня 2012 г. 11:13
  • Нужно посчитать сумму в полученном столбце 'показания'.

    declare @t table ( field char(1), val int )
    insert into @t
    values( 'a', 1 ), ( 'b', 3 ), ( 'c', 5 )
    
    select case when grouping(field)  = 1 then 'Итого:' else field end as field
         , sum(val) as val 
    from @t
    group by grouping sets ( (field), () )


    http://www.t-sql.ru

    • Помечено в качестве ответа J.Lion 8 июня 2012 г. 12:45
    8 июня 2012 г. 11:36
    Модератор
  • Воспользовался Вашим кодом:

    ALTER PROCEDURE [dbo].[DataForPeriodWithPlace] 
    	@BeginTime SmallDateTime,
    	@EndTime SmallDateTime,
    	@Location TinyInt
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    WITH 
    cteStart AS 
    	(SELECT CounterID, CounterData 
    	FROM Testimony 
    	WHERE TimeDate = @BeginTime), 
    
    cteEnd AS 
    	(SELECT CounterID, CounterData 
    	FROM Testimony 
    	WHERE TimeDate = @EndTime)
    
    SELECT 
    	L.PlaceName AS 'Размещение', 
    	CASE WHEN grouping(C.CounterNote) = 1 THEN 'Итого:' ELSE C.CounterNote END AS 'Счётчик',
    	SUM(E.CounterData - S.CounterData) AS 'Показание'
    FROM 
    	cteStart S INNER JOIN cteEnd E ON S.CounterID = E.CounterID
    			INNER JOIN Counter C ON S.CounterID = C.CounterID 
    			INNER JOIN Location L ON C.PlaceID = L.PlaceID
    WHERE L.PlaceID = @Location
    GROUP BY 
    	grouping sets ( (C.CounterNote), () )
    END

    Теперь выходит ошибка из-за первого столбца:

    Сообщение 8120, уровень 16, состояние 1, процедура DataForPeriodWithPlace, строка 30
    Column 'Location.PlaceName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


    С уважением, J.Lion

    8 июня 2012 г. 12:45
  • select 'Размещение', 'Счётчик', sum('Показание')
    from
    (
    
    SELECT 
    	L.PlaceName AS 'Размещение', 
    	C.CounterNote AS 'Счётчик', 
    	E.CounterData - S.CounterData AS 'Показание'
    FROM 
    	cteStart S INNER JOIN cteEnd E ON S.CounterID = E.CounterID
    			INNER JOIN Counter C ON S.CounterID = C.CounterID 
    			INNER JOIN Location L ON C.PlaceID = L.PlaceID
    WHERE L.PlaceID = @Location
    
    ) t
    group by
    grouping sets ( ( [Размещение], [Счётчик] ), () )


    http://www.t-sql.ru

    • Помечено в качестве ответа J.Lion 8 июня 2012 г. 13:12
    8 июня 2012 г. 12:52
    Модератор
  • Огромное спасибо

    С уважением, J.Lion

    8 июня 2012 г. 13:12
  • Спасибо, что отметили ответ


    Для связи [mail]

    8 июня 2012 г. 13:53