Sumy narastające - zliczanie podstawy zadlużenia i odsetek ustawowych

Cześć!

Chciałbym prosić o pomoc z poniższym problemem.

Przyszło mi napisać w T-SQL kalkulator odsetek ustawowych, który dostaje jedną należność z kwotą i terminem, tabelę wpłat wraz z kwotą i datą_wykonania oraz tabelę stawek odsetek ustawowych. Ma to realizować podobną funkcję do tego, tylko dla wielu kwot na wejściu: http://www.kalkulatory.gofin.pl/Kalkulator-odsetek-ustawowych,12.html

Problem z naliczaniem odsetek ustawowych jest - jak pewnie wszyscy chociaż trochę z tematem zaznajomieni wiedzą - niestety taki, że musimy przechowywać podstawę długu osobno i odsetki osobno (ponieważ od odsetek nie liczy się odsetek pochodnych), a przy każdej wpłacie najpierw pomniejszać kwotę odsetek a potem dopiero podstawę długu. Nie da się liczyć jednego bez drugiego, ponieważ w jawny sposób wyliczanie jednej kolumny opiera się na wartościach wcześniejszych tej i poprzedniej.

Rodzi się pytanie, jak w T-SQL (wersja tak naprawdę nie gra roli, aktualnie korzystam z MS SQL Server 2014) to zrealizować. Agregaty z OVER niestety nie pozwalają na odwoływanie się do samych siebie, a jednym z wymogów jest niestosowanie nic poza DQL, więc wszelkie kursory i pętle odpadają, wszystko ma być zrobione selectami. Ze swojej strony trochę nagiąłem wymóg zadania, tworząc funkcje inline, ponieważ inaczej kodu powstałoby o wiele za dużo, a wydajność na tym nie traci.

Schemat bazy:

 
CREATE TABLE stawki (
	dzien DATE NOT NULL PRIMARY KEY,
	stawka DECIMAL NULL,
);

CREATE TABLE naleznosci (
	id INTEGER NOT NULL PRIMARY KEY,
	kwota DECIMAL NULL,
	termin DATE NULL
);

CREATE TABLE wplaty (
	id INTEGER NOT NULL PRIMARY KEY,
	naleznosci_id INTEGER NOT NULL,
	kwota DECIMAL NULL,
	data_wplaty DATE NULL,
	FOREIGN KEY(naleznosci_id)
	REFERENCES naleznosci(id)
);

Trochę insertów do wygenerowania sensownej zawartości:
http://pastebin.com/e1wG8paa (do testów polecam zwłaszcza 24-28)

Funkcje inline, nie wszystkie wykorzystywane:
http://pastebin.com/8Bw7fvKC

Aktualna wersja skryptu generującego tabelę (jest tam sporo niepotrzebnych kolumn, ale tak łatwiej mi było pracować):
http://pastebin.com/RUkzv2Zw

Proszę o jakieś wskazówki. Z góry dzięki!

Nie znam tematu, więc jak jest aktualny to się odezwij, ale skoro masz SQl 2014 to może jakieś CTE ?

To nie jest do końca tak, że zmniejszasz kwotę odsetek, przykłady:

Pominę daty będę tylko posługiwał się ilością dni:

#1 faktura niezaplacona
sprawa prosta liczymy odsetki od calej kwoty faktury
#2 faktura zaplacona w całosci 10 dni po terminie
liczymy odsetki za 10 dni
#3 faktura częściowo zapłacona, zaplata w terminie
faktura = 100
zaplata = 10
liczę odsetki od 90 za wszystkie dni
#4 faktura częściowo zapłacona, zaplata 10 po terminie
faktura = 100
zaplata = 10
liczę odsetki od 90 za wszystkie dni, i od 10 za 10 dni
#5 faktura nadplacona
faktura = 100
z1 = 10 w terminie
z2 = 40,20 dni po terminie
z3 = 60, 30 dni po terminie

 liczę 10 za 10 dni, 40 za 20 dni, 50 za 60 dni

Przypadek piąty wymaga sumy bieżącej wpłat bo musimy policzyć ilość od części pozostałej do zaplaty, a nie od wpłaty.

W całym tym twoim przykładzie brakuje mi informacji na który dzień liczysz te odsetki, ale załóżmy że funkcją na dzień dzisiejszy.

Nie potrzebujesz do tego, żadnej funkcji wystarczy odpowiednie manewrowanie i łączenie tabel.

Przetestuj sobie to rozwiązanie:

with StawkiOkresy as (
	SELECT dzien, 
		   Dateadd(day, -1, Isnull((SELECT TOP 1 dzien 
									FROM   stawki AS p 
									WHERE  p.dzien > stawki.dzien), CONVERT(DATE, 
							Dateadd(year, 100, Getdate()), 121))) AS dt_do, 
		   stawka 
	FROM   stawki 
)
, wpl as (
	SELECT *, 
		   Sum(wplata) 
			 OVER( 
			   partition BY naleznosci_id 
			   ORDER BY data_wplaty rows UNBOUNDED PRECEDING) AS rs 
	FROM   (SELECT naleznosci_id, 
				   data_wplaty, 
				   Sum(kwota) wplata 
			FROM   wplaty 
			GROUP  BY naleznosci_id, 
					  data_wplaty) AS dt 
)
, wplatyDoLiczenia as ( 
	SELECT id, 
		   kwota, 
		   termin, 
		   wplata, 
		   data_wplaty, 
		   CASE 
			 WHEN termin > data_wplaty THEN 0 
			 ELSE 
			   CASE 
				 WHEN rs < kwota THEN wplata 
				 ELSE 
				   CASE 
					 WHEN kwota - ( rs - wplata ) >= 0 THEN kwota - ( rs - wplata ) 
					 ELSE 0 
				   END 
			   END 
		   END PodstawaOdsetek 
	FROM   naleznosci n 
		   INNER JOIN wpl 
				   ON n.id = wpl.naleznosci_id 
	UNION ALL 
	SELECT id, 
		   kwota, 
		   termin, 
		   NULL                      wplata, 
		   NULL                      data_wplaty, 
		   kwota - Isnull(wplata, 0) PodstawaOdsetek 
	FROM   naleznosci n 
		   LEFT JOIN (SELECT naleznosci_id, 
							 Sum(kwota) wplata 
					  FROM   wplaty 
					  GROUP  BY naleznosci_id) w 
				  ON n.id = w.naleznosci_id 
	WHERE  kwota - Isnull(wplata, 0) > 0 
)

, finalnaTabelaOdsetek as (
	select 
		fw.*
		,case when dzien <= termin then termin else dzien end as ods_od
		,case when dt_do>=isnull(data_wplaty,getdate()) then isnull(data_wplaty,getdate()) else dt_do end as ods_do
		,stawka
	from 
		wplatydoliczenia as fw
		left join StawkiOkresy ods on fw.termin <= ods.dt_do 
										and ods.dzien <= getdate()
										and isnull(fw.data_wplaty,getdate()) >= ods.dzien
										and fw.podstawaodsetek > 0 
)

select 
	*
	,Podstawaodsetek*DateDiff(d,ods_od,ods_do)*(stawka/100)/365 Odsetki
 from 
	finalnaTabelaOdsetek
order by
    id,data_wplaty	

Dla należności 24 zwróci:

Nie testowałem tego jakoś bardzo dokładnie, ale chciałem pokazać zarys w jaki sposób można to zrobić.

To wymaga SQL servera >= 2012

Dzięki. Rozwiązanie jest fajne, podchodzi do problemu w trochę inny sposób. Przyznam, że nie sprawdzałem dokładnie zakresów dat, stwierdzając, że dzień w tą lub w tą sobie wyrównam doświadczalnie.

Kilka rzeczy pozwijam, podstawię za nulle coś przyajźniejszego do oglądania i będzie. Dziękuję za pomoc!

Dzień na który liczysz jest o tyle ważny, że ogranicza wpłaty do faktury które bierzesz pod uwagę, więc nie można tego zrobić doświadczalnie...

Mój ostatni select celowo wygląda tak, abyś zobaczył jak te odsetki są liczone, bo syntetycznie to powinno być tak:

Select
    id
    ,kwota
    ,termin
    ,min(ods_od) od
    ,max(ods_do) do
    ,sum(Podstawaodsetek*DateDiff(d,ods_od,ods_do)*(stawka/100)/365) Odsetki
FROM 
    finalnaTabelaOdsetek
group by
    id
    ,kwota
    ,termin

Dzięki. Prawdę powiedziawszy, dalej nie za bardzo rozumiem, w jaki sposób jesteśmy w stanie określić, kiedy kończy się potrzeba wyliczania odsetek (cała należność jest spłacona). Domyślam się, że pochodzi to z tego zagnieżdżonego CASE w wplatyDoLiczenia...

Nie widzę możliwości edycji posta, więc...

Co się dzieje z odsetkami? One są spłacane w ogóle przeze mnie? Bo według kodu co spłacana jest tylko podstawa zadłużenia. Chyba, że nie rozliczam ich po drodze, tylko suma pod koniec.

Biały Pomidor napisał(a):

Prawdę powiedziawszy, dalej nie za bardzo rozumiem, w jaki sposób jesteśmy w stanie określić, kiedy kończy się potrzeba wyliczania odsetek (cała należność jest spłacona). Domyślam się, że pochodzi to z tego zagnieżdżonego CASE w wplatyDoLiczenia...

Przeanalizuj dla wpłaty 24, kluczowy jest tu widok wpl, który robi sumę bieżącą, stąd mam informacje w wierszu jak kwota została zapłacona, (celowo jest tam group by po dacie, aby kilka wpłat z jednego dnia traktować jako jedną.

Teraz widok wplatydoliczenia

Tu ważna jest kolumna PodstawaOdsetek, to tak naprawdę sprawdzenie czy od danej wpłaty liczę odsetki, tak naprawdę czytelniej by to było nazwać podstawadoliczeniaodsetek.
W pierwszym wierszu nie liczę bo zapłata w termine, podstawa 0, w kolejnych liczę odsetki od wpłaty, ponieważ nie przekraczam wartości faktury, w ostatnim liczę od 5000, ponieważ mam nadpłatę o 3 tysiące, i tak mamy case który sprawdza te przypadki:

           CASE 
             WHEN termin > data_wplaty THEN 0                                                      --Zapłata w terminie podstawa: 0
             ELSE 
               CASE 
                 WHEN rs < kwota THEN wplata                                                         --Kwota zapłat mniejsza od kwoty faktury podstawa: wpłata
                 ELSE 
                   CASE 
                     WHEN kwota - ( rs - wplata ) >= 0 THEN kwota - ( rs - wplata )       --Kwota faktury - poprzednie wpłaty >0, podstawa: od tego co zostało do zaplaty
                     ELSE 0                                                                                     --wpłata już do nadpłaconej faktury, podstawa: 0
                   END 
               END 
           END PodstawaOdsetek 

Zwróć uwagę, że wplatyDoLiczenia mają uniona który bierze kwotę, pozostałą do zaplaty lub nie są zapłacone wcale.

Biały Pomidor napisał(a):

Co się dzieje z odsetkami? One są spłacane w ogóle przeze mnie? Bo według kodu co spłacana jest tylko podstawa zadłużenia. Chyba, że nie rozliczam ich po drodze, tylko suma pod koniec.

Przy liczeniu odsetek w ogóle nie patrzysz na to czy ktoś je spłacał, generalnie to jest tak, że odsetki "zapisuje" się tylko od zapłat, (chyba że ktoś nalicza okresowo), przykład z banku, jak np. przekroczysz debet od ktorego liczą się odsetki, to zostaną one dopiero naliczone jak spłacisz przekroczenie debetu, okresowo płacisz odsetki przy kredycie odnawialnym, od wykorzystanej kwoty.

Elo
Mam pewne wątpliwości
odsetki liczysz od wyznaczonej daty z tabeli stawki?
Ponieważ zakładając że są 2-3 przedziały odsetek w różnych latach z różnymi stopami procentowymi to skąd bierzesz dokładną stawkę z przedziału 3 stawek

Liczę od Terminu Płatnosci do dnia wpłaty lub do dnia na ktory licze odsetki, jeżeli nie ma wpłaty.
Jeżeli zmienia się stawka, to liczę po nowej stawce, np. jeżeli stawka zmienia sie co 4 miesiące, to liczę odsetki za 3 miesiące wg, stawki, zmieniam na kolejna i licze za 3 miesiące itd.

Bardzo fajny sposób. Udało mi się go przystosować do MySQL-a. Mam tylko jeden problem. Jeśli mam np 2 faktury i 1 wpłatę to tę wpłatę zalicza mi do 1 i 2 faktury a powinno tylko na jedną (no chyba że wpłata jest większa niż faktura 1 to resztę powinno przerzucić na 2). Nie mogę sobie poradzić z takim rozwiązaniem.Może ktoś coś podpowie.

Wpłata powinna być powiązana z fakturą, jak w przykładzie naleznosci.id <-> wplaty.naleznosci_id, jeżeli tak nie jest to masz inną strukturę i ciezko doradzić

No właśnie Wpłaty mogę połączyć z fakturami tylko po ID_Klienta. Muszę jakoś wymyśleć jak zaliczyć wpłaty na faktury, zakładając wpłatę na najstarszą fakturę.