ReSQL.ru
     
powered by simpleCommunicator - 2.0.52     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Собрать таблицу с расчётом остатка, кредиторская задолженность
7 сообщений из 7, страница 1 из 1
Собрать таблицу с расчётом остатка, кредиторская задолженность
    #40002023
Earl11
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Earl11
Гость
Здравствуйте. Есть две таблицы Накладная и Платежи по накладным. Связаны они поклиентно. Требуется создать таблицу, в которой будет остаток погашения. Написал "кривой" алгоритм, который работал только с одним клиентом.
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
DROP TABLE #tempped

CREATE TABLE #tempped (
  Partner NVARCHAR(20)
  ,Inv NVARCHAR(20)
  ,DateKey NVARCHAR(20)
  ,SumI NVARCHAR(20)
  ,SumP NVARCHAR(20)
  ,Rem NVARCHAR(20)
  );
GO


DECLARE @a INT
  ,@b INT
  ,@j INT
  ,@i INT
  ,@count INT
  ,@sumI INT
  ,@sumP INT
  ,@partner NVARCHAR(20)
  ,@inv NVARCHAR(20)
  ,@datekey NVARCHAR(20)
  ,@sumS INT
  ,@dkRet NVARCHAR(20)
  ,@countP INT
  ,@ss INT
  ,@Rem INT

SET @sumI = 0
SET @sumS = 0
SET @sumP = 0

SELECT @dkRet = min(datekey)
FROM PAYMENT_TEST_

SELECT @b = sum(PaymentSUM)
FROM PAYMENT_TEST

SELECT @count = count(*)
FROM [INVOICE_TEST]

SELECT @countP = count(*)
FROM PAYMENT_TEST

SET @i = 0
SET @j = 0

WHILE @i <= @count
BEGIN
  --SET @j = 0
  SELECT @partner = partner
    ,@inv = invoice
    ,@sumI = @sumS + [sum]
  FROM [INVOICE_TEST]
  ORDER BY DATEKEY offset @i rows

  FETCH NEXT 1 rows ONLY

  SET @sumS = @sumI
  SET @ss = @sumI

  WHILE @j < @countP
  BEGIN
    SELECT @sumP = iif(@ss >= 0, [paymentsum], - @ss)
      ,@sumS = @sumS - [paymentsum]
      ,@b = @b - PaymentSUM
      ,@datekey = DATEKEY
    --,@ss = @sumI + @sumS
    FROM PAYMENT_TEST
    --WHERE datekey = @dkRet 
    ORDER BY DATEKEY offset @j rows

    FETCH NEXT 1 rows ONLY

    PRINT @j
    PRINT @sumI
    PRINT 'sumS ='
    PRINT @sums

    --print @sum1
    SET @ss = @sums

    INSERT INTO #tempped
    SELECT @partner
      ,@inv
      ,@datekey
      ,iif(@j = 0, @sumI, iif(@sumS <= 0
          OR @sumI <= 0, (
            SELECT Saldo
            FROM #tempped
            ORDER BY DATEKEY offset @j - 1 ROWS FETCH NEXT 1 ROWS ONLY
            ), @sumI))
      ,@sumP
      ,@SumS
    SET @j = @j + 1
    IF @sums <= 0
    BEGIN
      --  SET @ss = -@sumS 
      SELECT @partner = partner
        ,@inv = invoice
        ,@Rem = [sum]
      FROM [INVOICE_TEST]
      ORDER BY DATEKEY offset @i + 1 rows
      FETCH NEXT 1 rows ONLY

      INSERT INTO #tempped
      SELECT @partner
        ,@inv
        ,@datekey
        ,@Rem
        ,- @SumS
        ,@Rem + @SumS
      BREAK
    END
  END

  PRINT @sumS

  SET @i = @i + 1
END

SELECT *
FROM #tempped


Добавил данных в таблицы для нескольких клиентов, попытался что-то поменять - не вышло.
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
DROP TABLE #tempped

CREATE TABLE #tempped (
	Partner NVARCHAR(20)
	,Inv NVARCHAR(20)
	,DateKey NVARCHAR(20)
	,SumI NVARCHAR(20)
	,SumP NVARCHAR(20)
	,Rem NVARCHAR(20)
	);
GO

DECLARE @a INT
	,@b INT
	,@j INT
	,@i INT
	,@count INT
	,@sumI INT
	,@sumP INT
	,@partner NVARCHAR(20)
	,@inv NVARCHAR(20)
	,@datekey NVARCHAR(20)
	,@sumS INT
	,@dkRet NVARCHAR(20)
	,@countP INT
	,@ss INT
	,@Rem INT

SET @sumI = 0
SET @sumS = 0
SET @sumP = 0

SELECT @dkRet = min(datekey)
FROM PAYMENT_TEST

SELECT @b = sum(PaymentSUM)
FROM PAYMENT_TEST

SELECT @count = count(*)
FROM [INVOICE_TEST]

SELECT @countP = count(*)
FROM PAYMENT_TEST

SET @i = 0
SET @j = 0

DECLARE curs CURSOR
FOR
SELECT partner
FROM [INVOICE_TEST]
ORDER BY partner;

OPEN curs;

FETCH NEXT
FROM curs
INTO @partner

WHILE @i <= @count
	--WHILE @@FETCH_STATUS = 0
BEGIN
	--SET @j = 0
	SELECT --@partner = partner,
		@inv = invoice
		,@sumI = @sumS + [sum]
	FROM [INVOICE_TEST]
	WHERE PARTNER = @partner
	ORDER BY DATEKEY offset @i rows

	FETCH NEXT 1 rows ONLY

	SET @sumS = @sumI
	SET @ss = @sumI

	WHILE @j < @countP
	BEGIN
		PRINT @partner

		SELECT @sumP = iif(@ss >= 0, [paymentsum], - @ss)
			,@sumS = @sumS - [paymentsum]
			,@b = @b - PaymentSUM
			,@datekey = DATEKEY
		--,@ss = @sumI + @sumS
		FROM PAYMENT_TEST
		WHERE partner = @partner
		ORDER BY DATEKEY offset @j rows

		FETCH NEXT 1 rows ONLY

		SET @ss = @sums

		INSERT INTO #tempped
		SELECT @partner
			,@inv
			,@datekey
			,iif(@j = 0, @sumI, iif(@sumS <= 0
					OR @sumI <= 0, (
						SELECT Rem
						FROM #tempped
						ORDER BY DATEKEY offset @j - 1 ROWS FETCH NEXT 1 ROWS ONLY
						), @sumI))
			,@sumP
			,@SumS

		SET @j = @j + 1

		IF @sums <= 0
		BEGIN
			--  SET @ss = -@sumS 
			SELECT --@partner = partner,
				@inv = invoice
				,@Rem = [sum]
			FROM [INVOICE_TEST]
			WHERE PARTNER = @partner
			ORDER BY DATEKEY offset @i + 1 rows

			FETCH NEXT 1 rows ONLY

			INSERT INTO #tempped
			SELECT @partner
				,@inv
				,@datekey
				,@Rem
				,- @SumS
				,@Rem + @SumS

			BREAK
		END
	END

	PRINT @sumS

	SET @i = @i + 1

	FETCH NEXT
	FROM curs
	INTO @partner
END

CLOSE curs;

DEALLOCATE curs;

SELECT *
FROM #tempped


Может быть есть другие алгоритмы для данного случая, а не пилить два цикла? Возможно рекурсией как-то?
...
Рейтинг: 0 / 0
24.09.2020, 11:22
| Ответить | Цитировать | Написать  
Собрать таблицу с расчётом остатка, кредиторская задолженность
    #40002054
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Earl11
Может быть есть другие алгоритмы для данного случая, а не пилить два цикла? Возможно рекурсией как-то?
Это типовая задача, называется накопительный итог, рассчитывается одним запросом.
Поищите в форуме.

И ещё, вместо картинки выложите тестовые данные (с временными таблицами накладной и платежа).
Тогда можно будет более предметно говорить, показывать код.
...
Рейтинг: 0 / 0
24.09.2020, 12:04
| Ответить | Цитировать | Написать  
Собрать таблицу с расчётом остатка, кредиторская задолженность
    #40002187
Sybex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sybex
Гость
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
DECLARE @INVOICE TABLE ([Client] int, [Doc] nvarchar(50), [Date] date, [Sum] money)
DECLARE @PAYMENT TABLE ([Client] int, [Doc] nvarchar(50), [Date] date, [SumPay] money)

INSERT @INVOICE ([Client], [Doc], [Date], [Sum])
VALUES (1, N'inv1', '2020-01-20', 500.0000)
      ,(1, N'inv2', '2020-02-05', 1300.0000)
      ,(1, N'inv3', '2020-02-13', 2500.0000)
      ,(1, N'inv4', '2020-02-26', 1800.0000)
      ,(2, N'inv1', '2020-02-02', 400.0000)
      ,(2, N'inv2', '2020-02-17', 1200.0000)
      ,(2, N'inv3', '2020-03-02', 700.0000)
      ,(2, N'inv4', '2020-03-10', 300.0000)
      ,(2, N'inv5', '2020-03-21', 100.0000)

INSERT @PAYMENT ([Client], [Doc], [Date], [SumPay])
VALUES (1, N'pay1', '2020-01-30', 200.0000)
      ,(1, N'pay2', '2020-02-08', 500.0000)
      ,(1, N'pay3', '2020-02-15', 700.0000)
      ,(1, N'pay4', '2020-02-27', 500.0000)
      ,(1, N'pay5', '2020-03-05', 800.0000)
      ,(2, N'pay1', '2020-02-08', 100.0000)
      ,(2, N'pay2', '2020-02-20', 1000.0000)
      ,(2, N'pay3', '2020-03-04', 500.0000)
      ,(2, N'pay4', '2020-03-15', 700.0000)

;WITH [ICTE]
AS
(
    SELECT [I].[Client]
          ,[I].[Doc]
          ,[I].[Date]
          ,[I].[Sum]
          ,SUM([I].[Sum]) OVER(PARTITION BY [I].[Client] ORDER BY [I].[Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [FSum]
      FROM @INVOICE AS [I]
)
,[PCTE]
AS
(
    SELECT [P].[Client]
          ,[P].[Date]
          ,[P].[SumPay]
          ,SUM([P].[SumPay]) OVER(PARTITION BY [P].[Client] ORDER BY [P].[Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [FSumPay]
      FROM @PAYMENT AS [P]
)
,[CTE]
AS
(
    SELECT [ICTE].[Client]
          ,[ICTE].[Doc]
          ,[ICTE].[Date] AS [Date]
          ,[ICTE].[Sum]
          ,[P].[Date] AS [DatePay]
          ,[P].[SumPay]
          ,[ICTE].[FSum] - [P].[FSumPay] AS [Rem]
          ,LAG([ICTE].[FSum] - [P].[FSumPay]) OVER(PARTITION BY [ICTE].[Client] ORDER BY [ICTE].[Date], [P].[Date]) AS [LagRem]
      FROM [ICTE]
           CROSS APPLY(SELECT [PCTE].*
                         FROM [PCTE]
                        WHERE [ICTE].[Client] = [PCTE].[Client] AND
                              [ICTE].[FSum] - [PCTE].[FSumPay] + [PCTE].[SumPay] > 0 AND
                              [PCTE].[FSumPay] > [ICTE].[FSum] - [ICTE].[Sum]) [P]
)
SELECT [Client]
      ,[Doc]
      ,[DatePay]
      ,IIF([LagRem] > 0, [LagRem], [Sum]) AS [SumDoc]
      ,IIF([LagRem] < 0, [LagRem] * (-1), [SumPay]) AS [SumPay]
      ,[Rem]
  FROM [CTE]
 ORDER BY [Client], [Date], [DatePay]



В ОТВ (обобщённое табличное выражение, оно же common table expressions) ICTE и PCTE к данным в таблицах добавляются столбцы FSum и FSumPay - это нарастающие итоги по накладным и платежам соответственно. Далее в ОТВ CTE для каждой записи из накладной присоединяется набор платежей (через cross apply), которые затрагивают эту накладную (лишние отсекаются). Столбец LagRem это значение столбца Rem из прошлой строки (прошлого платежа).

alexeyvg
И ещё, вместо картинки выложите тестовые данные (с временными таблицами накладной и платежа).
Тогда можно будет более предметно говорить, показывать код.

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

P.S. В вашем примере на картинке ошибка в столбце SumDoc. Там дважды повторяется 1100, хотя во втором случае должно быть 400.
...
Рейтинг: 0 / 0
24.09.2020, 15:35
| Ответить | Цитировать | Написать  
Собрать таблицу с расчётом остатка, кредиторская задолженность
    #40003101
Earl11
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Earl11
Гость
Sybex, большое Вам спасибо.
...
Рейтинг: 0 / 0
27.09.2020, 17:09
| Ответить | Цитировать | Написать  
Собрать таблицу с расчётом остатка, кредиторская задолженность
    #40012341
Earl11
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Earl11
Гость
Поднимая тему снова. Данные немного изменились. Дано первоначальное сальдо, которое требуется погасить платежами и только потом закрывать данные накладные последующими платежами.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
DECLARE @INVOICE TABLE ([Client] int, [Doc] nvarchar(50), [Date] date, [Sum] money, [SaldoOrig] money)
DECLARE @PAYMENT TABLE ([Client] int, [Doc] nvarchar(50), [Date] date, [SumPay] money)

INSERT @INVOICE ([Client], [Doc], [Date], [Sum],[SaldoOrig])
VALUES (1, N'inv1', '2020-01-20', 500.0000, -800.0000)
      ,(1, N'inv2', '2020-02-05', 1300.0000, -800.0000)
      ,(1, N'inv3', '2020-02-13', 2500.0000, -800.0000)
      ,(1, N'inv4', '2020-02-26', 1800.0000, -800.0000)
   

INSERT @PAYMENT ([Client], [Doc], [Date], [SumPay])
VALUES (1, N'pay1', '2020-01-30', 200.0000)
      ,(1, N'pay2', '2020-02-08', 500.0000)
      ,(1, N'pay3', '2020-02-15', 700.0000)
      ,(1, N'pay4', '2020-02-27', 500.0000)
      ,(1, N'pay5', '2020-03-05', 800.0000)
      ,(1, N'pay6', '2020-03-15', 700.0000)
      ,(1, N'pay7', '2020-03-27', 500.0000)
      ,(1, N'pay8', '2020-04-05', 800.0000)
      

;WITH [ICTE]
AS
(
    SELECT [I].[Client]
          ,[I].[Doc]
          ,[I].[Date]
          ,[I].[Sum]
          ,SUM([I].[Sum]) OVER(PARTITION BY [I].[Client] ORDER BY [I].[Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [FSum]
		  ,[SaldoOrig]
      FROM @INVOICE AS [I]
)
,[PCTE]
AS
(
    SELECT [P].[Client]
          ,[P].[Date]
          ,[P].[SumPay]
          ,SUM([P].[SumPay]) OVER(PARTITION BY [P].[Client] ORDER BY [P].[Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [FSumPay]
      FROM @PAYMENT AS [P]
)
,[CTE]
AS
(
    SELECT [ICTE].[Client]
          ,[ICTE].[Doc]
          ,[ICTE].[Date] AS [Date]
          ,[ICTE].[Sum]
          ,[P].[Date] AS [DatePay]
          ,[P].[SumPay]
          ,[ICTE].[FSum] - [P].[FSumPay] AS [Rem]
          ,LAG([ICTE].[FSum] - [P].[FSumPay]) OVER(PARTITION BY [ICTE].[Client] ORDER BY [ICTE].[Date], [P].[Date]) AS [LagRem]
		  ,[SaldoOrig]
		  ,[SaldoOrig] + sum(p.[FSumPay]) OVER(order by  [P].[Date]  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as [SaldoRem]
      FROM [ICTE]
           CROSS APPLY(SELECT [PCTE].*
                         FROM [PCTE]
                        WHERE [ICTE].[Client] = [PCTE].[Client] AND
                              [ICTE].[FSum] - [PCTE].[FSumPay] + [PCTE].[SumPay] > 0 AND
                              [PCTE].[FSumPay] > [ICTE].[FSum] - [ICTE].[Sum]) [P]
)
SELECT [Client]
      ,[Doc]
      ,[DatePay]
      ,IIF([LagRem] > 0, [LagRem], [Sum]) AS [SumDoc]
      ,IIF([LagRem] < 0, [LagRem] * (-1), [SumPay]) AS [SumPay]
      ,[Rem]
	  ,[SaldoOrig]
	  , null [SaldoRem]
	  ,[SaldoRem]
  FROM [CTE]
 ORDER BY [Client], [Date], [DatePay]


Имею таблицу справа, хотелось слева)
Реализуемо ли это?
...
Рейтинг: 0 / 0
27.10.2020, 19:50
| Ответить | Цитировать | Написать  
Собрать таблицу с расчётом остатка, кредиторская задолженность
    #40014615
Earl11
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Earl11
Гость
Никто не сможет ответить?
...
Рейтинг: 0 / 0
02.11.2020, 22:23
| Ответить | Цитировать | Написать  
Собрать таблицу с расчётом остатка, кредиторская задолженность
    #40014774
Фотография Focha
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Focha
Участник
Earl11
Никто не сможет ответить?

Форум для обсуждения, а не решить за тебя задач, зп не мы же получаем.
...
Рейтинг: 0 / 0
03.11.2020, 11:44
| Ответить | Цитировать | Написать  
7 сообщений из 7, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Собрать таблицу с расчётом остатка, кредиторская задолженность
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=40012341&tid=1685455]:
 0ms
get settings:
 7ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 91ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 40ms
get tp. blocked users:
 1ms
others: 15ms
total:  182ms
созд. / загр.: 182ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]