Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как просуммировать не превышая определенное значение?  [new]
Grigoriy
Member

Откуда: Краснодар
Сообщений: 1484
странная задача стоит передо мной - надо просуммировать записи, но чтобы сумма не первышала 60 000
поясню:
есть таблица приходных ордеров, грубо говоря :
номер документа, дата, сумма ордера, номер клиента
надо сложить для клиента, начиная с начала года, суммы по ордерам.
Но.
Суммировать надо только то кол-во ордеров, сумма по которым не превышает 60 000.
КАК это сделать при помощи Т-SQL?
В Дельфях бы я организовал простейший цикл по двумерному отсортированному массиву.
А как в сервере?
16 май 05, 23:26    [1545728]     Ответить | Цитировать Сообщить модератору
 Re: Как просуммировать не превышая определенное значение?  [new]
Ч. Денис
Member

Откуда: Москва
Сообщений: 59
курсор открыть и дальше обрабатывать его как угодно.
16 май 05, 23:37    [1545740]     Ответить | Цитировать Сообщить модератору
 Re: Как просуммировать не превышая определенное значение?  [new]
asch
Guest
-- =============================================
-- Declare and using a READ_ONLY cursor
-- =============================================
DECLARE @SD smalldatetime, --начало года 
				@FD smalldatetime  --конец года
DECLARE cur_orders CURSOR
READ_ONLY
FOR SELECT ClientNumber, OrderAmount FROM orders WHERE Date BETWEEN @SD AND @FD

DECLARE @ClientNumber int, @OrderAmount money,
@ClientAmount money, @Limit money, @ClientNumberOld int
@Result TABLE(ClientNumber int, ClientAmount money)

SET @ClientAmount=0
SET @Limit=60000
SET @ClientNumberOld=-1

OPEN cur_orders

FETCH NEXT FROM cur_orders INTO @ClientNumber, @OrderAmount
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		IF(@ClientNumberOld!=@ClientNumber)
		BEGIN
			INSERT INTO @Result VALUES(@ClientNumberOld, @ClientAmount)
			SET @ClientNumberOld=@ClientNumber
			SET @ClientAmount=0
		END
		ELSE
		BEGIN
			IF((@ClientAmount+@OrderAmount)<@Limit)
				SET @ClientAmount=@ClientAmount+@OrderAmount
		END
	END
	FETCH NEXT FROM cur_orders INTO @ClientNumber, @OrderAmount
END

CLOSE cur_orders
DEALLOCATE cur_orders

SELECT * FROM @Result
GO

забить примерно такой код в какую нить процедурку и все дела
мог где нить ошибиться но суть примерно такая

16 май 05, 23:51    [1545754]     Ответить | Цитировать Сообщить модератору
 Re: Как просуммировать не превышая определенное значение?  [new]
Taffy
Member

Откуда:
Сообщений: 20501
Вам надо использовать механизм расчета "нарастающих итогов"
Недавно было большое обсуждение здесь. Да и вообще посмотрите по форуму про нарастающие итоги
17 май 05, 07:43    [1545906]     Ответить | Цитировать Сообщить модератору
 Re: Как просуммировать не превышая определенное значение?  [new]
Grigoriy
Member

Откуда: Краснодар
Сообщений: 1484
ё-мое... :(
читаю и никак не въеду... как я понял тут треугольное суммирование типа
s(a)=a+b+c+d
s(b)=b+c+d
s(c)=c+d
s(d)=d
а мне нужна "пила" типа
s(a)=a+b
s(b)=b
s(c)=c+d
s(d)=d

буду думать дальше...
17 май 05, 15:32    [1548142]     Ответить | Цитировать Сообщить модератору
 Re: Как просуммировать не превышая определенное значение?  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541
Grigoriy
надо сложить для клиента, начиная с начала года, суммы по ордерам.
Суммировать надо только то кол-во ордеров, сумма по которым не превышает 60 000.
КАК это сделать при помощи Т-SQL?


Выбираем документы (select t1.id), считая сумму нарастающим итогом (t1.id > t2.id) предварительно выкинув строки, в которых сумма не меньше 60000 (where t2.summ < 60000), затем выбираем результат (having sum(t2.summ) < 60000)

declare @t table(id int primary key identity(1,1), summ money)

insert @t (summ)
select 10000 union all
select 61000 union all
select 10000 union all
select 20000 union all
select 10000 union all
select 15000 union all
select 15000
-- Отбор документов
select t1.id
from @t t1
  inner join @t t2 on (t1.id >= t2.id)
where t2.summ < 60000
group by t1.id
having sum(t2.summ) < 60000

-- Расчет суммы
select sum(summ) from (
select t1.summ
from @t t1
  inner join @t t2 on t1.id >= t2.id
where t2.summ < 60000
group by t1.id, t1.summ
having sum(t2.summ) < 60000
) doc_summ


--
WBR, Roman S. Golubin
17 май 05, 16:01    [1548354]     Ответить | Цитировать Сообщить модератору
 Re: Как просуммировать не превышая определенное значение?  [new]
Grigoriy
Member

Откуда: Краснодар
Сообщений: 1484
Спасибо за подсказку про больше 60 000 - этот момент учел.
Я считаю по всей таблице нарастающим итогом для каждого клиента, тут без курсора не обойтись, но это ладно я смирился.
Теперь дальше - сделал запрос(я вместо курсора просто выбрал данные для клиента номер 1231 за этот год),
Use [Sum-Order]
if object_id('tempdb..#k', 'U') > 0 drop table #k
Select nndoc, summa
Into #k
from k_order 
Where an_kschet=001231 and dataoper>CONVERT(DATETIME, '2005-01-01 00:00:00', 102)
Order By 1

SELECT #k.nndoc, Max(#k.summa) summa, sum(isnull(k_order_1.summa,0))+Max(#k.summa) sumsumma
FROM  #k LEFT OUTER JOIN
               #k k_order_1 ON #k.nndoc > k_order_1.nndoc
GROUP BY #k.nndoc
нарастающий итог получил

N doc Summa Sum_Summa
000142 30400.000 30400.000
000223 6000.000 36400.000
000326 10848.000 47248.000
000476 6714.980 53962.980
000517 27380.640 81343.620
000601 5312.870 86656.490
000703 18550.000 105206.490
000802 4250.000 109456.490
000837 28926.910 138383.400
000927 7115.990 145499.390
000960 6411.640 151911.030
И вот что надо дальше -
первые четыре строки удовлетворяют условию, значит мы берем и в отдельную таблицу (nomer_contract, sum) пишем
1231/1 | 53962.980 |
и ДАЛЬШЕ мне надо заново начать счет, т.е. начиная с 000517 заново считать и запихать в таблицу уже 1231/2 и его сумму.
Т.е. получается пилка такая, в отличие от одного большого зубца у нарастающих итогов, здесь много мелких, причем разного размера.
17 май 05, 16:40    [1548601]     Ответить | Цитировать Сообщить модератору
 Re: Как просуммировать не превышая определенное значение?  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541
Тогда такой вариант:
declare @t table(id int primary key identity(1,1), summ money)

declare @r table(id int, i int, summ money, summ_r money)

insert @t (summ)
select 10000 union all
select 61000 union all
select 10000 union all
select 20000 union all
select 10000 union all
select 15000 union all
select 10000 union all
select 61000 union all
select 10000 union all
select 20000 union all
select 10000 union all
select 15000

declare @id int, @summ money, @summ_r money, @i int
select @summ_r = 0, @i = 0

declare cur cursor fast_forward
for select id, summ from @t t where summ < 60000 order by id

open cur

fetch next from cur into @id, @summ
while (@@fetch_status != -1)
begin
  if @@fetch_status != -2
  begin
    if @summ_r + @summ >= 60000
    begin
      select @summ_r = 0, @i = @i + 1
    end

    select @summ_r = @summ_r + @summ

    insert @r (id, i, summ, summ_r)
    select @id, @i, @summ, @summ_r

  end
  fetch next from cur into @id, @summ
end

close cur
deallocate cur

select max(summ_r)
from @r 
group by i

В результате получаем таблицу (@r), содержащую все документы, сумма которых меньше 60000, а рядом - поле с расчетом нарастающим итогом и твоей "пилой" (сбросом в 0 при достижении или превышении 60000)

--
WBR, Roman S. Golubin
17 май 05, 17:10    [1548800]     Ответить | Цитировать Сообщить модератору
 Re: Как просуммировать не превышая определенное значение?  [new]
Grigoriy
Member

Откуда: Краснодар
Сообщений: 1484
Погоди, твоя схема работает, но я получу суммы по всем документам всех фирм.
А мне надо для каждой отдельно. Это получается вложенный курсор? :(
если упростить, то из таблицы (id, firma, summa)
надо вырастить таблицу (firma, nomer, summa)
где nomer - это id фирмы плюс порядковый номер ордера (например для 1231 это будет 1231/1, 1231/2 и т.д.), а summa - это сумма по сумма для этой фирмы.
18 май 05, 14:13    [1551572]     Ответить | Цитировать Сообщить модератору
 Re: Как просуммировать не превышая определенное значение?  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541
Да, вложенный селект
18 май 05, 15:18    [1552038]     Ответить | Цитировать Сообщить модератору
 Re: Как просуммировать не превышая определенное значение?  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541
То есть, курсор.
18 май 05, 15:19    [1552045]     Ответить | Цитировать Сообщить модератору
 Re: Как просуммировать не превышая определенное значение?  [new]
Grigoriy
Member

Откуда: Краснодар
Сообщений: 1484
Понятно, @@fetch_status будет выпендриваться, придется завести допо. переменную...
18 май 05, 15:26    [1552089]     Ответить | Цитировать Сообщить модератору
 Re: Как просуммировать не превышая определенное значение?  [new]
Glory
Member

Откуда:
Сообщений: 104760
Grigoriy
Понятно, @@fetch_status будет выпендриваться, придется завести допо. переменную...

Зачем ?
18 май 05, 15:27    [1552093]     Ответить | Цитировать Сообщить модератору
 Re: Как просуммировать не превышая определенное значение?  [new]
Grigoriy
Member

Откуда: Краснодар
Сообщений: 1484
???
А как иначе можно работать с двумя курсорами, один из которых вложен в другой?
18 май 05, 15:44    [1552174]     Ответить | Цитировать Сообщить модератору
 Re: Как просуммировать не превышая определенное значение?  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541
Так и будет работать. @@fetch_status возвращает результат последнего fetch next.
18 май 05, 15:48    [1552192]     Ответить | Цитировать Сообщить модератору
 Re: Как просуммировать не превышая определенное значение?  [new]
Glory
Member

Откуда:
Сообщений: 104760
Grigoriy
???
А как иначе можно работать с двумя курсорами, один из которых вложен в другой?

Ну пример из BOL как-то ведь работает
18 май 05, 15:50    [1552203]     Ответить | Цитировать Сообщить модератору
 Re: Как просуммировать не превышая определенное значение?  [new]
Grigoriy
Member

Откуда: Краснодар
Сообщений: 1484
Да, действительно...
19 май 05, 10:36    [1554087]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить