Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Собрать таблицу с расчётом остатка, кредиторская задолженность / 7 сообщений из 7, страница 1 из 1
24.09.2020, 11:22
    #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, 12:04
    #40002054
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Собрать таблицу с расчётом остатка, кредиторская задолженность
Earl11
Может быть есть другие алгоритмы для данного случая, а не пилить два цикла? Возможно рекурсией как-то?
Это типовая задача, называется накопительный итог, рассчитывается одним запросом.
Поищите в форуме.

И ещё, вместо картинки выложите тестовые данные (с временными таблицами накладной и платежа).
Тогда можно будет более предметно говорить, показывать код.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.09.2020, 15:35
    #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
| Ответить | Цитировать | Написать  
27.09.2020, 17:09
    #40003101
Earl11
Earl11
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Собрать таблицу с расчётом остатка, кредиторская задолженность
Sybex, большое Вам спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.10.2020, 19:50
    #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
| Ответить | Цитировать | Написать  
02.11.2020, 22:23
    #40014615
Earl11
Earl11
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Собрать таблицу с расчётом остатка, кредиторская задолженность
Никто не сможет ответить?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2020, 11:44
    #40014774
Focha
Focha
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Собрать таблицу с расчётом остатка, кредиторская задолженность
Earl11
Никто не сможет ответить?

Форум для обсуждения, а не решить за тебя задач, зп не мы же получаем.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Собрать таблицу с расчётом остатка, кредиторская задолженность / 7 сообщений из 7, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 131ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1685455]:
 0ms
get settings:
 8ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 31ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 42ms
get tp. blocked users:
 1ms
others: 14ms
total:  131ms
x
x
Закрыть


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