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

Откуда:
Сообщений: 455
Исх. данные

Есть таблица номенклатуры:
ID Сумма_Реализации
1 100
2 200
3 70
4 400


Таблица оплат
Id_поруч Сумма_Оплаты
1 50
2 300
3 200

Нужно раскидать оплату по номенклатуре
Таблица кот. должна получиться
ID Id_поруч Сумма_Оплаты
1 1 50
1 2 50
2 2 200
3 2 50
3 3 20
4 3 180
14 авг 09, 11:37    [7538075]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
алгоритм раскидывания (по шагам, на русском языке) приведите, пожалуйста
14 авг 09, 11:39    [7538092]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
faustxp
Member

Откуда: Москва
Сообщений: 145
Видимо автор сам хочет узнать этот алгоритм раскидывания ,вот и спрашивает.
14 авг 09, 12:30    [7538610]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
Glory
Member

Откуда:
Сообщений: 104760
Классическая задача распрделить оплаты по продажам ?
14 авг 09, 12:32    [7538627]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
тогда MS SQL Server тт (пока) ни при чем
для этого есть Программирование
имхо, конечно
14 авг 09, 12:33    [7538633]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
hosTuk
Member

Откуда:
Сообщений: 455
Glory
Классическая задача распрделить оплаты по продажам ?

Я не знаю классических задач, но очень возможно, что она классическая.

Алгоритм раскидывания
Смотрим на оплату там 50

50 рубликами мы можем покрыть 1-ую номенклатуру на сумму 50 1-ым поручением
ID Id_поруч Сумма_Оплаты
1 1 50

Переходим к другой оплате там 300
Мы не закрыли 1-ую номенклатуру, еще надо оплатить 50
ID Id_поруч Сумма_Оплаты
1 2 50

Оплатили 1-ую номенклатуру, оплачиваем вторую, у нас осталось от 300, 250
Оплачиваем 2-ую номенк:
ID Id_поруч Сумма_Оплаты
2 2 200

Осталось 50 руб. от 2 поручения, оплачиваем часть 3-ей номенклатуры
ID Id_поруч Сумма_Оплаты
3 2 50

и так далее

Хотелось бы реализовать все на скл
14 авг 09, 12:41    [7538711]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
hosTuk
Member

Откуда:
Сообщений: 455
Может CLR процедурой?
14 авг 09, 12:46    [7538745]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
вот попробовал перевести буквально
рекомендую проверить хорошенько на своих данных,
у меня с такими задачами мало опыта, мог чего-то неправильно понять...
declare @nomen table(id int, sum_realiz int)
insert into @nomen(id, sum_realiz)
select 1, 100 union all
select 2, 200 union all
select 3,  70 union all
select 4, 400

declare @opl table(id_por int, sum_opl int)
insert into @opl(id_por, sum_opl)
select 1,  50 union all
select 2, 300 union all
select 3, 200

;with nomen_ as (
   select id, sum_realiz, row_number() over(order by id) rn
     from @nomen
), opl_ as (
   select id_por, sum_opl, row_number() over(order by id_por) rn
     from @opl
), cte as (
   select n.id, n.sum_realiz, o.id_por, o.sum_opl
         ,case when o.sum_opl <= n.sum_realiz then o.sum_opl else n.sum_realiz end opl
         ,case when o.sum_opl <= n.sum_realiz then n.sum_realiz - o.sum_opl else 0 end dolg
         ,case when o.sum_opl <= n.sum_realiz then 0 else o.sum_opl - n.sum_realiz end sald
         ,case when n.sum_realiz <= o.sum_opl then n.rn + 1 else n.rn end next_n_rn
         ,case when o.sum_opl <= n.sum_realiz then o.rn + 1 else o.rn end next_o_rn
     from nomen_ n
     join opl_ o on o.rn = n.rn
    where n.rn = 1
   union all
   select n.id, n.sum_realiz, o.id_por, o.sum_opl
         ,case when p.sum_opl <= z.sum_realiz then p.sum_opl else z.sum_realiz end opl
         ,case when p.sum_opl <= z.sum_realiz then z.sum_realiz - p.sum_opl else 0 end dolg
         ,case when p.sum_opl <= z.sum_realiz then 0 else p.sum_opl - z.sum_realiz end sald
         ,case when z.sum_realiz <= p.sum_opl then n.rn + 1 else n.rn end next_n_rn
         ,case when p.sum_opl <= z.sum_realiz then o.rn + 1 else o.rn end next_o_rn
     from cte c
     join nomen_ n on n.rn = c.next_n_rn
     join opl_ o on o.rn = c.next_o_rn
     cross apply (select case when c.dolg > 0 then c.dolg else n.sum_realiz end sum_realiz) z
     cross apply (select case when c.sald > 0 then c.sald else o.sum_opl end sum_opl) p
)
select * from cte
option (maxrecursion 0)

id          sum_realiz  id_por      sum_opl     opl         dolg        sald        next_n_rn            next_o_rn
----------- ----------- ----------- ----------- ----------- ----------- ----------- -------------------- --------------------
1           100         1           50          50          50          0           1                    2
1           100         2           300         50          0           250         2                    2
2           200         2           300         200         0           50          3                    2
3           70          2           300         50          20          0           3                    3
3           70          3           200         20          0           180         4                    3
4           400         3           200         180         220         0           4                    4

(6 row(s) affected)
14 авг 09, 13:16    [7539000]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
hosTuk
Member

Откуда:
Сообщений: 455
Оууууууууууууууууу
Не плох запрос. Потеститую над своими данными. Приведу к своему виду.
За запрос спасибо.
Откуда такие шаристые!!!!!!!!!!!!!!!!
Для меня что-то сложновато было такое придумать
14 авг 09, 13:40    [7539223]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
hosTuk
Member

Откуда:
Сообщений: 455
Возник еще один вопрос по формированию оплаты.
Есть заголовок счета, например
Номер_счета   Сумма_Реал
1                       4324
2                       3232 

И номенклатура
в ней есть сумма оптовая
Номер_счета   Сумма_Опт
1                       300
1                       3000
1                       1000
2                       3000 
2                       100

Нужно пропорционально раскидать для каждой номенклатуры, делаю так
(Сумма_Опт*Сумма_Реал)/SUM(Сумма_Опт)
это будет раскиданная сумма реализации но уже в номенклатуре

Плюс проверка для последней записи(подгон копеек), т.к. при делении можем потерять копейки.
Сумма_Реал - (SUM(Сумма_Опт) кроме той записи в кот. буду заносить этот полученный результат)

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

Привожу скрипт,
-- Отдельная сумма и общая сумма, с порядковым номером:
-- PARTITION BY tempSum.pl_n, tempSum.data_p, tempSum.dpl_n, tempSum.GOD_IS
-- ORDER BY tempSum.pl_n, tempSum.data_p,  tempSum.dpl_n, tempSum.GOD_IS, tempSum.n_nom, tempSum.Sum_Opt
ALTER VIEW [dbo].[vTotalSumWithOneSum]
AS
SELECT tempSum.pl_n, tempSum.data_p, tempSum.dpl_n, tempSum.GOD_IS, 
	tempSum.Sum_Opt, SumPoz.TotalSumOpt, tempSum.n_nom, 
	(ROW_NUMBER() OVER (PARTITION BY tempSum.pl_n, tempSum.data_p, tempSum.dpl_n, tempSum.GOD_IS
						ORDER BY tempSum.pl_n, tempSum.data_p,  tempSum.dpl_n, tempSum.GOD_IS, tempSum.n_nom, tempSum.Sum_Opt)) AS SerialNum
FROM OT07_DBF_MAIN.dbo.pozsh AS tempSum
	LEFT JOIN
			(
			SELECT SumPoz.pl_n, SumPoz.data_p, SumPoz.dpl_n, SumPoz.GOD_IS, CAST(sum(SumPoz.sum_opt) AS DEC(15,2)) AS TotalSumOpt
			FROM OT07_DBF_MAIN.dbo.pozsh AS SumPoz
			GROUP BY SumPoz.pl_n, SumPoz.data_p, SumPoz.dpl_n, SumPoz.GOD_IS
			) AS SumPoz
	ON (tempSum.pl_n=sumPoz.pl_n) AND (tempSum.data_p=sumPoz.data_p)
						AND ((tempSum.dpl_n=sumPoz.dpl_n) OR ((tempSum.dpl_n is null) AND (sumPoz.dpl_n is null)))
						AND (tempSum.GOD_IS = sumPoz.GOD_IS)


--------------------------------------------------------
-- Сумма по счету c новыми коэффицентами SUM(Пропорция)
--------------------------------------------------------
USE [OT07_Schet]
GO
/****** Объект:  View [dbo].[vNewSumKoef]    Дата сценария: 08/03/2009 12:24:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET DATEFORMAT ymd

ALTER VIEW [dbo].[vNewSumKoef]
AS 
SELECT  TOP 100 PERCENT TotalSumAndSumForSert.pl_n, TotalSumAndSumForSert.data_p, 
	TotalSumAndSumForSert.dpl_n, TotalSumAndSumForSert.GOD_IS, TotalSumOpt, tabCount.Counts,
	(	SUM(CAST(
			(CASE 
				WHEN (TotalSumAndSumForSert.TotalSumOpt!=0)
					THEN (tarif * TotalSumAndSumForSert.sum_opt)/TotalSumOpt
				-- Если SUM(сумма_оптовая)равна 0, то делим на количество записей
				ELSE 
					CASE
						WHEN CAST(Tarif AS DEC(15,2)) !=0 THEN (prsKoef.Tarif / Counts)
						ELSE 0
					END
			END)AS DEC(15,2)))) AS newSumTarif,
	-- tarif с типом данных real, в дальнейшем будем делать CAST этого столбца
	prsKoef.tarif, 
	(	SUM(CAST(
			(CASE 
				WHEN (TotalSumAndSumForSert.TotalSumOpt!=0)
					THEN (prsKoef.SUM_REAL * TotalSumAndSumForSert.sum_opt)/TotalSumOpt
				-- Если SUM(сумма_оптовая)равна 0, то делим на количество записей
				ELSE 
					CASE
						WHEN CAST(SUM_REAL AS DEC(15,2)) !=0 THEN (prsKoef.SUM_REAL / Counts)
						ELSE 0
					END
			END)AS DEC(15,2)))) AS newSumREAL,
	prsKoef.SUM_REAL AS SumReal
FROM OT07_DBF_MAIN.dbo.prsch AS prsKoef
	LEFT JOIN
		OT07_Schet.dbo.vTotalSumWithOneSum AS TotalSumAndSumForSert
	ON (prsKoef.pl_n=TotalSumAndSumForSert.pl_n) AND (prsKoef.data_p=TotalSumAndSumForSert.data_p)
			AND ((prsKoef.dpl_n=TotalSumAndSumForSert.dpl_n) OR ((prsKoef.dpl_n is null) AND (TotalSumAndSumForSert.dpl_n is null)))
			AND(prsKoef.GOD_IS = TotalSumAndSumForSert.GOD_IS)
	LEFT JOIN 
			(
				SELECT  COUNT(*) AS Counts, P1.pl_n, P1.data_p, P1.dpl_n, P1.GOD_IS
				FROM OT07_DBF_MAIN.dbo.pozsh AS P1
				GROUP BY P1.pl_n, P1.data_p, P1.dpl_n, P1.GOD_IS
			) AS tabCount
	ON (tabCount.pl_n=prsKoef.pl_n) AND (tabCount.data_p=prsKoef.data_p)
		AND ((tabCount.dpl_n=prsKoef.dpl_n) OR ((tabCount.dpl_n is null) AND (prsKoef.dpl_n is null)))
		AND(tabCount.GOD_IS = prsKoef.GOD_IS)
GROUP BY TotalSumAndSumForSert.pl_n, TotalSumAndSumForSert.data_p, TotalSumAndSumForSert.dpl_n, TotalSumAndSumForSert.GOD_IS,
		 TotalSumOpt, prsKoef.Tarif, tabCount.Counts, prsKoef.SUM_REAL


-- newSumTarif - высчитанная сумма tarif по счету (сравнивается с tarif из prsch)
-- TotalSumOpt - сумма оптовая по счету
SELECT  
	pl_n, data_p, dpl_n, GOD_IS, newSumTarif, TotalSumOpt, tarif, newSumREAL, SumREAL, Counts
INTO tbTemp
FROM OT07_Schet.dbo.[vNewSumKoef]

-- Пронумерованный счет c SUM(суммой оптовой) по всему счету и с суммой оптовой по номенк,
-- с порядковым номером
SELECT 
	V.pl_n, V.data_p, V.dpl_n, V.GOD_IS, V.Sum_Opt, V.SerialNum, V.n_nom
INTO tbNum
FROM OT07_Schet.[dbo].[vTotalSumWithOneSum] AS V




SELECT POZ.Pl_N, POZ.data_p, POZ.dpl_n, POZ.God_IS,
	CASE dbo.GetSumWithout(POZ.SerialNum, PRS.Counts, PRS.TotalSumOpt, CAST(PRS.tarif AS DEC(15,2)), PRS.newSumTarif)
		WHEN 0 THEN 0
		WHEN 3 THEN CAST((Poz.Sum_Opt*Prs.Tarif/PRS.TotalSumOpt) AS DEC(15,2))
		WHEN 2 THEN CAST(PRS.Tarif AS DEC(15,2)) - (PRS.COUNTS-1)*CAST((Prs.Tarif / PRS.Counts) AS DEC(15,2))
		WHEN 1 THEN 
				(SELECT CAST(PRS.Tarif AS DEC(15,2))-SUM(P.SumWithOut)
						FROM
						(
							SELECT TOP (PRS.COUNTS-1) (CAST(((POZ1.Sum_Opt*PRS.Tarif)/PRS.TotalSumOpt) AS DEC(15,2))) AS SumWithOut
							FROM tbNum AS POZ1
							WHERE (POZ1.pl_n=POZ.pl_n) AND (POZ1.data_p=POZ.data_p)
								AND ((POZ1.dpl_n=POZ.dpl_n) OR ((POZ1.dpl_n is null) AND (POZ.dpl_n is null)))
								AND(POZ1.GOD_IS=POZ.GOD_IS)
							GROUP BY POZ1.Pl_N, POZ1.data_p, POZ1.dpl_n, POZ1.GOD_IS, POZ1.Sum_Opt,	POZ1.SerialNum
							ORDER BY POZ1.pl_n, POZ1.data_p,  POZ1.dpl_n, POZ1.GOD_IS, POZ1.SerialNum
						) AS P
				)
		WHEN 4 THEN CAST((Prs.Tarif / PRS.Counts) AS DEC(15,2))
		--WHEN 5 THEN CAST(Prs.Tarif AS DEC(15,2))
	END AS _NewTarif, PRS.newSumTarif, PRS.tarif,
	CASE dbo.GetSumWithout(POZ.SerialNum, PRS.Counts, PRS.TotalSumOpt, CAST(PRS.SumREAL AS DEC(15,2)), PRS.newSumREAL)
		WHEN 0 THEN 0
		WHEN 3 THEN CAST((Poz.Sum_Opt*Prs.SumREAL/PRS.TotalSumOpt) AS DEC(15,2))
		WHEN 2 THEN CAST(PRS.SumREAL AS DEC(15,2)) - (PRS.COUNTS-1)*CAST((Prs.SumREAL / PRS.Counts) AS DEC(15,2))
		WHEN 1 THEN 
				(SELECT CAST(PRS.SumREAL AS DEC(15,2))-SUM(P.SumWithOut)
						FROM
						(
							SELECT TOP (PRS.COUNTS-1) (CAST(((POZ1.Sum_Opt*PRS.SumREAL)/PRS.TotalSumOpt) AS DEC(15,2))) AS SumWithOut
							FROM tbNum AS POZ1
							WHERE (POZ1.pl_n=POZ.pl_n) AND (POZ1.data_p=POZ.data_p)
								AND ((POZ1.dpl_n=POZ.dpl_n) OR ((POZ1.dpl_n is null) AND (POZ.dpl_n is null)))
								AND(POZ1.GOD_IS=POZ.GOD_IS)
							GROUP BY POZ1.Pl_N, POZ1.data_p, POZ1.dpl_n, POZ1.GOD_IS, POZ1.Sum_Opt,	POZ1.SerialNum
							ORDER BY POZ1.pl_n, POZ1.data_p,  POZ1.dpl_n, POZ1.GOD_IS, POZ1.SerialNum
						) AS P
				)
		WHEN 4 THEN CAST((Prs.SumREAL / PRS.Counts) AS DEC(15,2))
		--WHEN 5 THEN CAST(Prs.Tarif AS DEC(15,2))
	END AS _NewSumReal,
	PRS.newSumREAL, PRS.SumREAL,
	POZ.SerialNum,PRS.Counts
FROM tbTemp AS PRS
	LEFT JOIN tbNum AS POZ
	ON (PRS.pl_n=POZ.pl_n) AND (PRS.data_p=POZ.data_p)
				AND ((PRS.dpl_n=POZ.dpl_n) OR ((PRS.dpl_n is null) AND (POZ.dpl_n is null)))
				AND(PRS.GOD_IS=POZ.GOD_IS)
ORDER BY POZ.pl_n, POZ.data_p,  POZ.dpl_n, POZ.GOD_IS, POZ.n_nom, POZ.Sum_Opt


-- В зависимости от возвращаемого значения различается подсчет тарифа(коэф.) в вызывающем запросе
-- 
ALTER FUNCTION GetSumWithout(@Num int, @Counts int, @SumTotal dec(15,2), @PredKoef dec(15,2), @NewKoef dec(15,2))
RETURNS int
AS
BEGIN
	DECLARE @RES int;

	SET @RES =
	CASE 
		-- Коэф равен нулю
		WHEN @PredKoef=0 THEN 0
		-- Последняя строка и в номенклатуре больше одной строки, и новое расчитанное начение не равно
		-- значению по исх. данным
		WHEN (@Num=@Counts)AND(@Counts!=1)AND(@NewKoef!=@PredKoef) THEN
			CASE
				-- Сумма оптовая по счету !=0
				WHEN @SumTotal != 0 THEN 1
				ELSE 2
			END
		--WHEN (@Counts=1)AND(@PredKoef!=0) THEN 5
		-- Не последние записи в счете
		ELSE 
			CASE
				WHEN @SumTotal != 0 THEN 3
				ELSE 4
			END
	END

	RETURN @RES
END




Это мой быдло код:) Как сделать проще. Что исправить или делать по другому. Любая оптимизация принимается буду рад услышать и научиться новому.
21 авг 09, 15:09    [7567366]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
В тему не вчитывался. Но, может быть, поможет древний пост Glory?
У меня тоже есть запрос из одного SELECTа (но с коррелированными подзапросами),
который разбрасывает оплаты по счетам-фактурам. Но я его никому не показывал, ибо очень тормозит
У меня есть стойкое ощущение, что именно для такой задачи лучше всего подходят курсоры (прости, Taffy )
21 авг 09, 15:39    [7567649]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
hosTuk
Member

Откуда:
Сообщений: 455
Как раз хочу уйти от курсоров.
В данный момент работает вариант с курсорами, писал его не я. Работает медленно. 20-30 минут.
21 авг 09, 17:07    [7568310]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
hosTuk
писал его не я
Не важно КТО. Важно КАК!
21 авг 09, 17:57    [7568569]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
Kostt
Member

Откуда:
Сообщений: 54
Курсоры сами по себе не тормозные. Скорее всего скрипт с ними коряво написан, вот и тормозят. Можно попробовать найти в нем узкие места и оптимизировать их
22 авг 09, 07:36    [7569366]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
hosTuk
Member

Откуда:
Сообщений: 455
Я могу привести код этих курсоров, а вы посмеетесь ;)
22 авг 09, 21:42    [7570007]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
hosTuk
Member

Откуда:
Сообщений: 455
Написал раскидку для своей задачи по тому как предложил Паганель.
Работает 14 минут.
В таблице номенклатуры были все года. В таблице оплат взял только определнный год. Сейчас попробую номенклатуру за тот же год взять.
Текст запроса, тот же что и у Паганель, но под свою задачу.
Кому интресно

;with nomen_ as (
   select Pl_N, data_p, dpl_n, God_IS, _NewSumReal,  SerialNum AS id, SerialNum AS rn
   from tbTempNom
), opl_ as (
   select Pl_N, data_p, dpl_n, God_IS, CAST(SUMMA AS DEC(15,2)) AS SUMMA,  N_PORUSC AS id_por,
			 (ROW_NUMBER() OVER (PARTITION BY tempSum.pl_n, tempSum.data_p, tempSum.dpl_n, tempSum.GOD_IS
			 ORDER BY tempSum.pl_n, tempSum.data_p,  tempSum.dpl_n, tempSum.GOD_IS, tempSum.N_PORUSC, tempSum.SUMMA)) AS rn
   from OT07_DBF_MAIN.dbo.OPL_SH AS tempSum
   WHERE DATA_SECTION = 'OPL07'
), cte as (
   select n.Pl_N, n.data_p, n.dpl_n, n.God_IS, n.id, n._NewSumReal, o.id_por, o.SUMMA
         ,case when o.SUMMA <= n._NewSumReal then o.SUMMA else n._NewSumReal end opl
         ,case when o.SUMMA <= n._NewSumReal then n._NewSumReal - o.SUMMA else 0 end dolg
         ,case when o.SUMMA <= n._NewSumReal then 0 else o.SUMMA - n._NewSumReal end sald
         ,case when n._NewSumReal <= o.SUMMA then n.rn + 1 else n.rn end next_n_rn
         ,case when o.SUMMA <= n._NewSumReal then o.rn + 1 else o.rn end next_o_rn
     from nomen_ n
		join opl_ o 
			ON (o.pl_n=n.pl_n) AND (o.data_p=n.data_p)
					AND ((o.dpl_n=n.dpl_n) OR ((o.dpl_n is null) AND (n.dpl_n is null)))
					AND(o.GOD_IS=n.GOD_IS)
		where (n.rn = 1) AND (o.rn = 1)
   union all
   select n.Pl_N, n.data_p, n.dpl_n, n.God_IS, n.id,n._NewSumReal, o.id_por, o.SUMMA
         ,case when p.sum_opl <= z.sum_realiz then p.sum_opl else z.sum_realiz end opl
         ,case when p.sum_opl <= z.sum_realiz then z.sum_realiz - p.sum_opl else 0 end dolg
         ,case when p.sum_opl <= z.sum_realiz then 0 else p.sum_opl - z.sum_realiz end sald
         ,case when z.sum_realiz <= p.sum_opl then n.rn + 1 else n.rn end next_n_rn
         ,case when p.sum_opl <= z.sum_realiz then o.rn + 1 else o.rn end next_o_rn
     from cte c
     join nomen_ n on (n.rn = c.next_n_rn)AND(n.pl_n=c.pl_n) AND (n.data_p=c.data_p)
					AND ((n.dpl_n=c.dpl_n) OR ((n.dpl_n is null) AND (c.dpl_n is null)))
					AND(n.GOD_IS=c.GOD_IS)
     join opl_ o on (o.rn = c.next_o_rn)AND(o.pl_n=c.pl_n) AND (o.data_p=c.data_p)
					AND ((o.dpl_n=c.dpl_n) OR ((o.dpl_n is null) AND (c.dpl_n is null)))
					AND(o.GOD_IS=c.GOD_IS)
     cross apply (select case when c.dolg > 0 then c.dolg else n._NewSumReal end sum_realiz) z
     cross apply (select case when c.sald > 0 then c.sald else o.SUMMA end sum_opl) p
)
select  * from cte
--WHERE (pl_n = 20001)AND(DATA_p='2001-11-08 00:00:00.000')
--order by MainId, id, id_por
option (maxrecursion 0)
24 авг 09, 11:52    [7572178]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
hosTuk
Member

Откуда:
Сообщений: 455
Для таблицы номенклатуры 14691 записей
и оплат 10293
7:53
Может действительно попробовать курсором сделать
24 авг 09, 12:32    [7572454]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
hosTuk
Member

Откуда:
Сообщений: 455
Написал курсором


DECLARE cursorOpl  CURSOR LOCAL STATIC READ_ONLY FOR
	SELECT pl_n, data_p, dpl_n, GOD_IS, N_PORUSC, SUMMA
	FROM OT07_DBF_MAIN.dbo.opl_sh
	WHERE (DATA_SECTION = 'opl04')--AND(pl_n = 71857)AND(data_p='2004-11-26 00:00:00.000')
	ORDER BY  pl_n, data_p, dpl_n, GOD_IS, N_PORUSC, SUMMA
OPEN cursorOpl

DECLARE @CurOplSch int, @CurOplDate datetime, @CurOplDpl_n char(1), @CurOplPoruch int,	@CurOplSumma dec(15,2),
		@CurOplGodIs smallint;
DECLARE @CurNomSch int, @CurNomDate datetime, @CurNomDpl_n char(1), @CurNomGodIs smallint, @CurNomReal dec(15,2),
		@CurNomNum int;

-- Сколько осталось от оплаты, сколько осталось долга от номенклатуры
DECLARE @Ostatok dec(15,2), @Dolg dec(15,2)

SET @Ostatok = 0; 
SET @Dolg = 0;

fetch first from cursorOpl into @CurOplSch, @CurOplDate, @CurOplDpl_n, @CurOplGodIs, @CurOplPoruch,
								@CurOplSumma

SET @CurNomSch = @CurOplSch
SET @CurNomDate = @CurOplDate
SET @CurNomDpl_n = @CurOplDpl_n
SET @CurNomGodIs = @CurOplGodIs
SET @Ostatok = @CurOplSumma; 

WHILE @@FETCH_STATUS = 0 
BEGIN
	-- Тот же счет, нужно оплатить долг или первый заход в номенклатуру
	IF (@CurNomSch = @CurOplSch)AND(@CurNomDate = @CurOplDate)
		AND( (@CurNomDpl_n = @CurOplDpl_n) OR ((@CurNomDpl_n IS NULL)AND(@CurOplDpl_n IS NULL)) )
		AND(@CurNomGodIs=@CurOplGodIs)
	BEGIN
		
		IF (@DOLG=0) -- Самая первая номенклатура 
		BEGIN
			DECLARE cursorNom CURSOR LOCAL STATIC READ_ONLY FOR
				SELECT pl_n, data_p, dpl_n, GOD_IS, SerialNum, _NewSumReal
				FROM OT07_SCHET.dbo.tbTempNom
				WHERE (pl_n = @CurOplSch)AND(data_p=@CurOplDate)AND((dpl_n=@CurOplDpl_n)OR((dpl_n IS NULL)AND(@CurOplDpl_n IS NULL)))
					  AND(God_IS=@CurOplGodIs)
				ORDER BY pl_n, data_p, dpl_n, GOD_IS, SerialNum 
			OPEN cursorNom;

			fetch first from cursorNom into
					@CurNomSch, @CurNomDate, @CurNomDpl_n, @CurNomGodIs, @CurNomNum, @CurNomReal;
			
			SET @Dolg = @CurNomReal;
		END

		-- Цикл по номенклатурам, первый заход или оплата долга
		WHILE @@FETCH_STATUS = 0
		BEGIN
			-- Можем оплатить всю строчку в номенклатуре
			IF @Ostatok>=@Dolg 
			BEGIN
				INSERT INTO tbTempSchet
					(n_scheta, data_p, dpl_n, God_IS, idPor, idNom, Summa)
				VALUES (@CurOplSch, @CurOplDate, @CurOplDpl_n, @CurOplGodIs, @CurOplPoruch, @CurNomNum, @Dolg);

				SET @Ostatok = @Ostatok - @Dolg;
				
				-- Двигаемся к следующей номенклатуре
				fetch next from cursorNom into
						@CurNomSch, @CurNomDate, @CurNomDpl_n, @CurNomGodIs, @CurNomNum, @CurNomReal;

				IF @@FETCH_STATUS=0	SET @Dolg = @CurNomReal; -- В номенклатуре есть еще записи
				ELSE BEGIN -- Нет записей в номенклатуре, переходим к другой оплате
					SET @Dolg = 0; 
				END
			END
			ELSE BEGIN -- На эту оплату можем оплатить только часть номенклатуры
				
				-- Осталось чем оплатить номенклатуру
				IF @Ostatok!=0 
				BEGIN
					SET @Dolg = @Dolg - @Ostatok;

					INSERT INTO tbTempSchet
						(n_scheta, data_p, dpl_n, God_IS, idPor, idNom, Summa)
					VALUES (@CurOplSch, @CurOplDate, @CurOplDpl_n, @CurOplGodIs, @CurOplPoruch, @CurNomNum, @Ostatok);
					break;
				END
				ELSE BEGIN -- Нечем платить, случай когда на предудыщем шаге остаток был равен долгу, а сейчас остаток 0
					SET @Dolg = @CurNomReal;
					break;
				END
			END
		END

		fetch next from cursorOpl into 
				@CurOplSch, @CurOplDate, @CurOplDpl_n, @CurOplGodIs, @CurOplPoruch, @CurOplSumma

		-- Новый остаток
		IF @@FETCH_STATUS = 0 SET @Ostatok = @CurOplSumma;
	END
	ELSE BEGIN -- Новый счет в оплате
		SET @CurNomSch = @CurOplSch
		SET @CurNomDate = @CurOplDate
		SET @CurNomDpl_n = @CurOplDpl_n
		SET @CurNomGodIs = @CurOplGodIs

		SET @Dolg = 0;
		SET @Ostatok = @CurOplSumma;

		-- Все номенклатуры закончились, двигаемся по счету
		close cursorNom
		deallocate cursorNom;
	END
END

close cursorOpl
deallocate cursorOpl;

На тех же данных 29 секунд.
25 авг 09, 15:02    [7577637]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
если и это медленно, тогда можете updateом воспользоваться при обязательном указании индекса обхода... но это неофициальная фича, и пользоваться ей можно на "свой страх и риск"... но когда вопрос стоит во времени..

для спящего время бодрствования равносильно сну
25 авг 09, 15:38    [7577943]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
hosTuk
Member

Откуда:
Сообщений: 455
Меня устраивают эти 30 сек. Потестриую еще немного и угомонюсь.

Алексей2003
если и это медленно, тогда можете updateом воспользоваться при обязательном указании индекса обхода... но это неофициальная фича, и пользоваться ей можно на "свой страх и риск"... но когда вопрос стоит во времени..

для спящего время бодрствования равносильно сну

Не совем понял, не знаком с этим... Можете поподробнее, интересно стало:)
25 авг 09, 19:38    [7579312]     Ответить | Цитировать Сообщить модератору
 Re: Запрос формирования оплаты  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
hosTuk

Алексей2003
если и это медленно, тогда можете updateом воспользоваться при обязательном указании индекса обхода... но это неофициальная фича, и пользоваться ей можно на "свой страх и риск"... но когда вопрос стоит во времени..

для спящего время бодрствования равносильно сну

Не совем понял, не знаком с этим... Можете поподробнее, интересно стало:)

create table #t (id int, i int, i2 int)
create index t_i on #t(id)
insert into #t (id, i) select 2, 3 union all select 1, 2
declare @i int
set @i = 0
update #t set @i = @i + i, i2 = @i from #t with (index (t_i))
select * from #t
select * from #t order by id
set @i = 0
update #t set @i = @i + i, i2 = @i from #t
select * from #t
select * from #t order by id
go
drop table #t
когда надо было - пользовался. пользователи не жаловались на неправильность расчетов.
это своего рода сумма с нарастающим итогом. отрабатывает на ура. быстрее чем курсор.
но для этого в данном случае необходимо будет создавать промежуточную объединенную таблицу с индексом..
25 авг 09, 20:15    [7579381]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить