|
|
[ Mikelly @ 27.11.2008. 12:45 ] @
|
| Pogledajte tabelu koju imam na raspolaganju (pretpostavljam podatke):
Code:
ID, Datum_knjizenja, Datum_dospijeca, Potrazuje, Duguje
1, nebitno 15.01.2008 45 0
2, nebitno 15.02.2008 42 0
3, nebitno 15.03.2008 38 0
4, nebitno 29.03.2008 0 100
5, nebitno 24.01.2008 11 0
6, nebitno 15.04.2008 45 0
7, nebitno 04.04.2008 0 50
...
Hocu da dobijem sledece:
Code:
--- Datum_dospijeca, Saldo, Proteklo dana
--- 15.01.2008. -45 0
--- 24.01.2008. -56 9
--- 15.02.2008. -98 22
--- 15.03.2008. -136 28
--- 29.03.2008. -36 14
--- 04.04.2008. +14 7
--- 15.04.2008. -31 11
...
E sad, nijesam bio lijen, iz knjige OReily SQL Cookbook sam nasao resenje za saldo, tj. running sum, mada oni tamo predlazu da se vrsi uporedjivanje po kljucu, ja uporedjivanje moram vrsiti po datumu dospijeca, jer mi zapisi tako MORAJU biti sortirani. Evo kako to izgleda:
Code:
SELECT DISTINCT
Test.Datum, (SELECT(SUM(t.Duguje - t.Potrazuje)) FROM Test t WHERE t.Datum <= Test.Datum) AS Saldo
FROM Temp
ORDER BY Test.Datum ASC
Pa sam jos pokusao i da ubacim ovo sa datumima i evo do cega sam dosao:
Code:
DateDiff(day,
(SELECT TOP 1 t3.Datum FROM Test t3 WHERE t3.Datum < Test.Datum ORDER BY t3.Datum DESC),
(SELECT TOP 1 t2.Datum FROM Test t2 WHERE t2.Datum = Test.Datum)) AS ProtekloDana
Drugi select je trenutni datum, a prvi select je datum prije njega.
Ovo mora ici kao subquery gornjeg queryja, pa je citav query:
Code:
SELECT DISTINCT Test.Datum, (SELECT(SUM(t.Duguje - t.Potrazuje)) FROM Test t WHERE t.Datum <= Test.Datum) AS Saldo, DateDiff(day, (SELECT TOP 1 t3.Datum FROM Test t3 WHERE t3.Datum < Test.Datum ORDER BY t3.Datum DESC), (SELECT TOP 1 t2.Datum FROM Test t2 WHERE t2.Datum = Test.Datum)) AS ProtekloDana
FROM Temp
ORDER BY Test.Datum ASC
i radi :), ali se bojim da je klimavo, i da se nedje krije kakav bug, a posto ce biti hiljade i hiljade ovih zapisa, a ovaj query ce biti zahtijevan svako malo, koliko ce se ovakav query uopste brzo izvrsavati, a mozda neko ima i totalno drugaciji pristup gornjem problemu.
Molim vas za pomoc bilo koje vrste.
Pozdrav. |
[ sallle @ 28.11.2008. 01:16 ] @
1.da probas vako:
Code:
SELECT DISTINCT Test.Datum as Dtm, (SELECT(SUM(t.Duguje - t.Potrazuje)) FROM Test t WHERE t.Datum <= Dtm) AS Saldo, DateDiff(day,Dtm,(select min(datum) from Test)) AS ProtekloDana
FROM Temp
ORDER BY Test.Datum ASC
verovatno bi brze radilo ukoliko bi iso preko kursora, jer bi mogao u jednom prolazu resis problem.
Takodje ova funkcija DateDiff se mozda moze ubrazati jer verovatno u svakom redu izvrsava ponovno izracunavanje parametara, sto mozemo da premostimo sa:
Code:
declare @minDatum datetime
select @minDatum = min(datum) from Test
<tvoj upit>, DateDiff(day, Dtm, @minDatum) <nastavak upita>
[ Mikelly @ 28.11.2008. 12:44 ] @
Nisam pred bazom, a nisam ni veliki strucnjak za SQL, ali mi se cini da ono min(datum) from test daje minimalni datum citave tabele, tj. taj datum je konstantan tokom citavog upita, a meni treba razlika izmedju datuma dva uzastopna zapisa, trenutnog i sledeceg, koji se uvijek mijenjaju.
Ispravi me ako grijesim.
Pozdrav.
[ sallle @ 28.11.2008. 14:11 ] @
u pravu si, nisam to primetio
[ djoka_l @ 28.11.2008. 14:49 ] @
Pretpostavljam da se pitanje odnosi na rešavanje ovog problema na MS SQL bazi (sudeći po DateDiff).
Ako te interesuje kako se ovo elegantno rešava na Oracle bazi analitičkim funkcijama, modu da ti napišem...
[ Zidar @ 28.11.2008. 14:50 ] @
Nisi nam objasnio dovoljno sta u stvari pokusavas da uradis. Iz tabele koju si dao i rezultata, ne uspevam da razumem sta radis. Mislim da znam sta hoces, ali nisi dovoljno jasno objasnio, pa mozda i gresim.
Pomoglo bi ako bi nam dao CREATE statement za tabelu i nekoliko INSER INTO da imamo neke podatke s kojima radimo. Kveri koji bi odradio sta ti treba nije jednostavan ali nije ni mozgalica. Medjutim, tesko ga je napisati iz glave, bez testiranja, pa bi zasta pomoglo ako nam das tets tabelu i neke podatke.
U odnosu na sta se racuna [Proteklo dana]? Meni se ucinilo na prvi pogled da se racuna od datuma koje ima red gde je ID=1, ali nije tako. O, vidim da je to razlika dva uzastona dana.
Sta predstavlja kolona [Saldo] u rezultatu? verovatno sumu svih ulaza i sumu svih izlaza do datuma za koji se racuna.
Pojasni sta ti treba i daj tabelu i test podatke.
[ Zidar @ 28.11.2008. 15:44 ] @
Ajde da te ne mucim mnogo, ionako mi ne bi mnogo pomoglo jer su datumi formatirani drugacije nego kod mene ;-)
Code:
IF Object_ID('SaldoTest') IS NOT NULL DROP TABLE Saldotest
CREATE TABLE SaldoTest
(ID int
, Datum_knjizenja datetime
, Datum_dospijeca datetime
, Potrazuje money NOT NULL DEFAULT 0
, Duguje money NOT NULL DEFAULT 0
)
-- Orati paznju na uslov NOT NULL i DEFAULT
-- Bez toga su moguce greske u proracunu
-- koje se dosta tesko otkrivaju
INSERT INTO SaldoTest
(ID, Datum_dospijeca, Potrazuje, Duguje)
SELECT 1, '2008/01/15', 45, 0
UNION
SELECT 2, '2008/02/15', 42, 0
UNION
SELECT 3,'2008/03/15', 38, 0
UNION
SELECT 4,'2008/03/29', 0,100
UNION SELECT 5,'2008/01/24', 11, 0
UNION
SELECT 6,'2008/04/15', 45, 0
UNION
SELECT 7,'2008/04/04', 0,50
SELECT * FROM SaldoTest
-- nije sortirano, nema veze
Tebi trebaju dve stvari: a) da izracunas razliku u danima izmedju dva uzastopna datuma u tabeli i b) saldo na tekuci dan, kao razlika ukupnog dugovanja do tog dana i ukupnog potrazivanja do tog dana. Ovo je bilo knjigovodstvo. Sledi SQL:
Code:
---- Koliko je Proteklo dana od tekuceg do prethodnog?
-- prvo da utvrdimo sta je to protekli dan:
SELECT
A.Datum_dospijeca AS Tekuci
-- prethodni dan je onaj koji ima najveci datum a manji je od tekuceg datuma
, Prethodni = (SELECT MAX(B.Datum_dospijeca)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca < A.Datum_dospijeca)
FROM SaldoTest AS A
-- ORDER BY nije biran za proracun, to je za nas da lakse vidimo rezultat
-- Ako se kveri zivrsi bez ORDER BY rezultati ce biti isti, ali ce na izlazu mozda
-- biti u pogresnom redosledu
-- Ovo vazi za sve kverije u ovom zadatku
ORDER BY A.Datum_dospijeca
-- razlika u danima:
SELECT
A.Datum_dospijeca AS Tekuci
, Prethodni = (SELECT MAX(B.Datum_dospijeca)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca < A.Datum_dospijeca)
-- Razlika = Datediff(dat, prethodni, tekuci)
, Datediff(day
, (SELECT MAX(B.Datum_dospijeca)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca < A.Datum_dospijeca)
, A.Datum_dospijeca
)
AS [Proteklo dana]
FROM SaldoTest AS A
ORDER BY A.Datum_dospijeca
Sada da izracunamo saldo:
Code:
-- Kumulativna potrazivanja i dugovanja na tekuci datum
SELECT
A.Datum_dospijeca
, A.Potrazuje
, (SELECT SUM(Potrazuje)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca <= A.Datum_dospijeca
) AS [Kumulativno Potrazivanje]
, (SELECT SUM(Duguje)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca <= A.Datum_dospijeca
) AS [Kumulativno Dugovanje]
FROM SaldoTest As A
ORDER BY A.Datum_dospijeca
-- Saldo na tejkuci datum = [Kumulativ Potrazuje] - [Kumulativ Duguje]
SELECT
A.Datum_dospijeca
, A.Potrazuje
, A.Duguje
, (SELECT SUM(Potrazuje)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca <= A.Datum_dospijeca
) AS [Kumulativno Potrazivanje]
, (SELECT SUM(Duguje)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca <= A.Datum_dospijeca
) AS [Kumulativno Dugovanje]
, (SELECT SUM(Potrazuje)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca <= A.Datum_dospijeca
)
-
(SELECT SUM(Duguje)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca <= A.Datum_dospijeca
) AS Saldo
FROM SaldoTest As A
ORDER BY A.Datum_dospijeca
Na kraju sve ovo treba pokupiti u jedna kveri. ako odbacimo 'mdejurezultate' dobijemo nesto sto i ne izgleda tako strasno:
Code:
-- Sad se ova dva kverija spoje u jedan:
SELECT
--- ovo je iz kverija za saldo
A.Datum_dospijeca
, A.Potrazuje
, A.Duguje
, (SELECT SUM(Potrazuje)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca <= A.Datum_dospijeca
)
-
(SELECT SUM(Duguje)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca <= A.Datum_dospijeca
) AS Saldo
-- ovo je iz kverija za razliku u danima:
, Datediff(day
, (SELECT MAX(B.Datum_dospijeca)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca < A.Datum_dospijeca)
, A.Datum_dospijeca
)
AS [Proteklo dana]
FROM SaldoTest As A
ORDER BY A.Datum_dospijeca
-- sto daje rezultat:
Datum_dospijeca Potrazuje Duguje Saldo Proteklo dana
----------------------- --------------------- --------------------- --------------------- -------------
2008-01-15 00:00:00.000 45.00 0.00 45.00 NULL
2008-01-24 00:00:00.000 11.00 0.00 56.00 9
2008-02-15 00:00:00.000 42.00 0.00 98.00 22
2008-03-15 00:00:00.000 38.00 0.00 136.00 29
2008-03-29 00:00:00.000 0.00 100.00 36.00 14
2008-04-04 00:00:00.000 0.00 50.00 -14.00 6
2008-04-15 00:00:00.000 45.00 0.00 31.00 11
(7 row(s) affected)
Napominjem da ORDER BY nema nikakvog uticaja na izracunavanje salda i razlike u danima. Svi kveriji mogu da se odrade i bez ORDER BY. Rezultati ce biti identicni za iste datume, samo rezultujuci skup podataka nece biti sortiran po datumima.
Da li bi ovo islo brze pomocu kursora? Za jako veliki broj podataka, verovatno. da li treba odmah napisati kursor? Verovatno da ne. Ako se lepo indeksiraju datumske kolone, moglo bi da bude brzo i ovako kako je. Nako bi mogao da testira za recimo 100,000 podataka ili 500,000 pa 5 miliona i tako dalje. U jednom momentu ce kveri postati suvise spor. Poslednji broj redova koji je bio dovoljno brz je granica kdaa treba preci na kursor.
Kako napraviti test tabelu sa 5 miliona rekorda? Koristite tabelu brojeva i tabelu kalendar. O cemu se radi? Pogledajte ovde:
http://sqlserver2000.databases...n-auxiliary-numbers-table.html
http://sqlserver2000.databases...-auxiliary-calendar-table.html
Niko se nije naucen rodio pa ni Zidar ;-) Sve trikove je pokupio po knjigama i po internetu.
 [ Mikelly @ 28.11.2008. 23:21 ] @
Evo da se ispravim, pojasnjenje problema, retroaktivno:
Prvo, Zidar, svaka cast, majstor si!
Mada sam i ja zadovoljan kako sam sklepao onaj moj, ali sam se izglupirao trazeci trenutni datum, kada mi je on vec datum spoljasnjeg upita, i sto sam mjesto MAX isao sa TOP 1 ... ORDER BY ... DESC, e debila :)
Dakle, u pitanju je opstinska sluzba gradica dje ja zivim, kojoj su konacno dozlogrdila brda papira i hoce na racunaru da vodi evidenciju poreskih obveznika. Problem nastaje kod racunanja kamata kada obveznici zakasne sa uplatom (uplate nakon datuma dospijeca). Uplata obveznika je "Duguje" a vrijednost poreske prijave "Potrazuje". Kada poreski obveznik zakasni sa uplatom zaracunava mu se kamata od 0.03% dnevno, ali isto tako, kada je u pretplati, ima pravo na pozitivnu kamatu iste stope.
Prvo sam krenuo da "kopiram" sistem iz Podgorice (dali su mi na uvid primjer njihove kartice). Oni tamo nakon svake uplate obveznika rasknjizavaju kamate smjestajuci iznos na konto kamata (kreirajuci zapise u bazi).
Medjutim, kada dodje do retroaktivnih promjena, bilo izmijenjenom poreskom prijavom, ili pogresnom uplatom ili greskom operatera, kamate se skroz pobrkaju nakon ispravki, tako da je potrebno obaviti dosta posla. Ja sam razmisljao da kreiram posebnu log tabelu gdje bih cuvao podatke o tome koja uplata je "cistila" koje prijave i koliku je kamatu generisala, itd. kako bih kasnije mogao ispraviti stvari, ali sam brzo od toga odustao i krenuo u trazenje "cistijeg" rjesenja.
Naumio sam da svaki put nanovo racunam kamate, jer su one, na kraju krajeva, redundantne, zavise od drugih zapisa, te da izracunatu vrijednost umanjim za iznos uplata koje je obveznik izvrsio uplatama kojima regulise kamate (ne uplate koje se ondose na same poreske obaveze), i tako dobijem tacnu vrijednost.
I, evo, upravo dok kucam ovo, kapiram da imam propust u logici rjesenja, ali nije veliki, valjda. Da uzmem rezultat Zidarovog konacnog querija, samo Saldu mijenjam predznak jer kod njih ide Duguje - Potrazuje.
Code:
Datum_dospijeca Potrazuje Duguje Saldo Proteklo dana
----------------------- --------------------- --------------------- --------------------- -------------
2008-01-15 00:00:00.000 45.00 0.00 -45.00 NULL
2008-01-24 00:00:00.000 11.00 0.00 -56.00 9
2008-02-15 00:00:00.000 42.00 0.00 -98.00 22
2008-03-15 00:00:00.000 38.00 0.00 -136.00 29
2008-03-29 00:00:00.000 0.00 100.00 -36.00 14
2008-04-04 00:00:00.000 0.00 50.00 +14.00 6
2008-04-15 00:00:00.000 45.00 0.00 -31.00 11
Prvih 45eur generise negativnu kamatu za 9 dana, a ne za NULL dana, tako da je: kamata = -45 * 9 * 0.0003.
Sledecih 22 dana generise negativnu kamatu za 56eur, dok bi za 14 eura pretplate isla pozitivna kamata za 11 dana.
Dakle, ispravno rjesenje bi znacilo da se "Proteklo dana" popne za jedan red navise, a da se zadnji zapis uporedjuje sa "danasnjim" datumom.
Penjanje bi se, cini mi se, rijesilo tako sto bi Zidareva datediff f-ja izgledala:
Code:
Datediff(day, A.Datum_dospijeca, (SELECT MIN(B.Datum_dospijeca) FROM SaldoTest AS B WHERE B.Datum_dospijeca > A.Datum_dospijeca))
Ali kako da zadnji datum uporedim sa trenutnim. Ja totalno razmisljam na if, for, while nacin, tek treba da savladam SQL tako.
Mozda bi moglo na kraju:
Code:
FROM SaldoTest As A UNION (SELECT neki_id, danasnji_datum, 0, 0)
ako to sintaksa dozvoljava...
Pozdrav, i hvala na trudu momci :)
P.S.
Da li sta mijenja ako ima dvije aktivnosti na isti datum?
[ Zidar @ 01.12.2008. 15:26 ] @
Citat: Prvih 45eur generise negativnu kamatu za 9 dana, a ne za NULL dana, tako da je: kamata = -45 * 9 * 0.0003.
Sledecih 22 dana generise negativnu kamatu za 56eur, dok bi za 14 eura pretplate isla pozitivna kamata za 11 dana.
Problem se moze ovajko preformulisati:
U skupu podataka koji generis 'zidarev kveri' treba izracunati kamatu tako sto se vrednost u koloni "Saldo" mnozi sa vrednoscu kolone "Proteklo dana" iz sledeceg reda. "Saldo" u poslednjem redu se uporedjuej sa "Proteklo dana" u odnosu na danasnji dan.
Da se ovo resi nije potreban loop niti kursor. Prvo je potrebno modifikovati 'zidarev kveri' tako da svi redovi dobiju redne brojeve.
Za tu svrhu MS SQL ima funkciju Row_number. Sve sto nam treba je da na pocetek kverija ubacimo liniju
Code:
RedniBroj = row_number() OVER (ORDER BY A.Datum_dospijeca)
Sintaksu za row_number mozes da nadjes u Books online ili u negde drugo. Zasto ne koristim kolonu ID iz tabele - pa nema garancije da ce redovi biti uneti bas u redosledu koji nama treba. Nije dobro biti zavistan od toga kako su fizicki uneti redovi u tabelu.
Kveri bi dakle izgledao ovako:
Code:
SELECT
RedniBroj = row_number() OVER (ORDER BY A.Datum_dospijeca)
, A.Datum_dospijeca
, A.Potrazuje
, A.Duguje
, (SELECT SUM(Potrazuje)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca <= A.Datum_dospijeca
)
-
(SELECT SUM(Duguje)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca <= A.Datum_dospijeca
) AS Saldo
, Datediff(day
, (SELECT MAX(B.Datum_dospijeca)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca < A.Datum_dospijeca)
, A.Datum_dospijeca
)
AS [Proteklo dana]
FROM SaldoTest As A
ORDER BY A.Datum_dospijeca
-- sto daje rezultat:
RedniBroj Datum_dospijeca Potrazuje Duguje Saldo Proteklo dana
-------------------- ----------------------- --------------------- --------------------- --------------------- -------------
1 2008-01-15 00:00:00.000 45.00 0.00 45.00 NULL
2 2008-01-24 00:00:00.000 11.00 0.00 56.00 9
3 2008-02-15 00:00:00.000 42.00 0.00 98.00 22
4 2008-03-15 00:00:00.000 38.00 0.00 136.00 29
5 2008-03-29 00:00:00.000 0.00 100.00 36.00 14
6 2008-04-04 00:00:00.000 0.00 50.00 -14.00 6
7 2008-04-15 00:00:00.000 45.00 0.00 31.00 11
(7 row(s) affected)
Primedbe na racunanje razlike u danima stoje. U tekstu ja sam zadrazo moj originalno kveri jer mi je tako lakse, sustina se na menja, za ono dsto hocemo da pokazemo a ja manje kucam Hocemo dakle da pokazemo kako se red uporedjuej sa sledecim redom. Sad bi poslednji kveri trebao sam sebe da pozove nekako. Postoji vise nacina da se to uradi. Starinsk nacin, iz vremena SQL 2000 i ranije jeste da se poslednji kveri sacuva kao view, pa se onda napravi JOIN na samog sebe. Ovako nekako:
Code:
IF Object_ID('vwSaldo') IS NOT NULL DROP VIEW vwSaldo
GO
CREATE VIEW vwSaldo AS
SELECT
RedniBroj = row_number() OVER (ORDER BY A.Datum_dospijeca)
, A.Datum_dospijeca
, A.Potrazuje
, A.Duguje
, (SELECT SUM(Potrazuje)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca <= A.Datum_dospijeca
)
-
(SELECT SUM(Duguje)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca <= A.Datum_dospijeca
) AS Saldo
, Datediff(day
, (SELECT MAX(B.Datum_dospijeca)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca < A.Datum_dospijeca)
, A.Datum_dospijeca
)
AS [Proteklo dana]
FROM SaldoTest As A
-- da vidimo da li radi:
SELECT * FROM vwSaldo ORDER BY Datum_dospijeca
Sad povezemo svaki red sa sledecim redom:
Code:
-- JOIN svaki red sa prethodnim redom:
SELECT
-- podaci iz tekuceg reda
A.RedniBroj
, A.Datum_dospijeca
----, A.Potrazuje
----, A.Duguje
, A.Saldo
-- borj dana dolazi iz sledeceg reda
, B.[Proteklo dana]
, Kamata = A.Saldo * B.[Proteklo dana] * 0.003
FROM vwSaldo AS A -- tekuci red
JOIN vwSaldo AS B -- sledeci red
ON A.RedniBroj = (B.RedniBroj - 1)
-- rezultat
RedniBroj Datum_dospijeca Saldo Proteklo dana Kamata
-------------------- ----------------------- --------------------- ------------- ---------------------------------------
1 2008-01-15 00:00:00.000 45.00 9 1.2150000
2 2008-01-24 00:00:00.000 56.00 22 3.6960000
3 2008-02-15 00:00:00.000 98.00 29 8.5260000
4 2008-03-15 00:00:00.000 136.00 14 5.7120000
5 2008-03-29 00:00:00.000 36.00 6 0.6480000
6 2008-04-04 00:00:00.000 -14.00 11 -0.4620000
(6 row(s) affected)
Rezultat je dobar ali nije kompletan, nedostaje resenje za poslednji red. Ideja sa UNION je na dobrom putu. Pitanje ej samo gde i kada ubaciti UNION. Ako pokusas sada, na kraju svega, moglo bi da prodje ali bi bilo komplikovano. Mnogo je lakse ubaciti UNION na samom pocetku. View koji smo kreirali cita podatke iz tabele SaldoTest. Tabela Saldotest u stvari ima jedan red manje nego sto bi trebalo. Tabeli treba nekako dodati jedan red na kraju koji bi imao Datum_Dospijeca = danasnji dan i nule u Duguje/Potrazuje. Najlakse je da se view napise ovako:
Code:
-- Kako resiti poslednji dan:
IF Object_ID('vwSaldo') IS NOT NULL DROP VIEW vwSaldo
GO
CREATE VIEW vwSaldo AS
SELECT
RedniBroj = row_number() OVER (ORDER BY A.Datum_dospijeca)
, A.Datum_dospijeca
, A.Potrazuje
, A.Duguje
, (SELECT SUM(Potrazuje)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca <= A.Datum_dospijeca
)
-
(SELECT SUM(Duguje)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca <= A.Datum_dospijeca
) AS Saldo
, Datediff(day
, (SELECT MAX(B.Datum_dospijeca)
FROM SaldoTest AS B
WHERE B.Datum_dospijeca < A.Datum_dospijeca)
, A.Datum_dospijeca
)
AS [Proteklo dana]
FROM
(
SELECT Datum_dospijeca, Potrazuje, Duguje
FROM SaldoTest
UNION
-- ovo je red koji 'nedostaje' u tabeli SaldiTest
SELECT Getdate() AS Datum_dospijeca, 0 AS Potrazuje, 0 AS Duguje
) A
Svi kveriji bazirani vwSaldo radice isto kao i pre jer nismo promenili nazive kolona niti dodavali oduzimali kolone u view. Promenili smo FROM deo i dobili jedan red vise na izlazu.
Sto se tice ispravljanja greski i retroaktivne promene kamate, to je slozeniji pronblem koji opet mozemo da razbijemo na dva dela.
A) Ako uvedes tabelu Kamate sa kolonama (KamatnaStopa, VaziOd) i koja bi izgledala otprilike ovako:
KamatnaStopa VaziOd
----------------------
0.003 1 Jan 2008
0.0035 15 Jan 2008
0.0028 1 Apr 2008
Sada mozes da na scvaki datum u tabeli SaldoTest primenis kamatnu stopu koja je vazila na taj dan. Nije jednostavno ali tako nekako moze.
B) Sto se tice ispravke gresaka, i tu pomaze kolona VaziOd. Za svaki red u tabeli SaldoTest uvedes kolonu VaziOd, to je datum od kada vazi ta kolona. Red koji ima gresku se pretvara u novi red, sa novim datumom vazenja. Kad uzimas podatke za obracun kamate, uzimas one gde datum maksimalan. Kveriji se diodatno komplikuju, ali sta se moze.
AKom odes na ovaj sajt http://books.google.ca/books?i...;printsec=frontcover#PPA315,M1 i izaberes "Preview the is book" mozes da nadjes u sadrzaju poglavlje Working with temporal data. Tamo imas primer bas iz knjigovodstva o ispravljanju gresaka u transakcijama.
Bolji link: http://www.simple-talk.com/sql...on-managing-data-bitemporally/
[Ovu poruku je menjao Zidar dana 01.12.2008. u 17:04 GMT+1][ Mikelly @ 02.12.2008. 12:29 ] @
Opet, Zidar, svaka cast na fori sa rednim brojevima, majstor si!
Posto cu te jos ponesto pitat u vezi ove iste stvari, ako te ne bude mrzjelo da odgovaras, evo test podataka iz moje baze:
Code:
ID Obveznik Konto Datum_knjizenja Datum_dospijeca Potrazuje Duguje Storno
--------------------------------------------------------------------------------------------------------------
1 1 1 11/29/2008 3:07:06 PM 1/1/2008 12:00:00 AM 56 0 False
2 1 2 11/29/2008 3:07:06 PM 2/1/2008 12:00:00 AM 56 0 False
3 1 1 11/29/2008 3:07:06 PM 3/1/2008 12:00:00 AM 38 0 False
4 1 2 11/29/2008 3:07:06 PM 3/31/2008 12:00:00 AM 0 250 False
5 1 1 11/29/2008 3:07:06 PM 1/1/2008 12:00:00 AM 10 0 False
6 1 2 11/29/2008 3:07:06 PM 4/1/2008 12:00:00 AM 38 0 False
7 1 1 11/29/2008 3:07:06 PM 4/11/2008 12:00:00 AM 0 50 False
8 1 2 11/29/2008 3:07:06 PM 1/1/2008 12:00:00 AM 14 0 False
9 1 1 11/29/2008 3:07:06 PM 3/1/2008 12:00:00 AM 22 0 False
10 1 2 11/29/2008 3:07:06 PM 4/1/2008 12:00:00 AM 15 0 False
Obveznik i konto su strani kljucevi drugih tabela. Obveznika ce biti oko 500, a konta ce biti 5 plus jos jedan konto na koji se uplacuju kamate.
Evo kako sam ja rijesio problem:
Code:
WITH Buff(ID, Obveznik, Konto, Datum_knjizenja, Datum_dospijeca, Potrazuje, Duguje, Saldo) AS
(SELECT * FROM Data UNION (SELECT (SELECT MAX(ID)+1 FROM Data), 1, 1, Getdate(), Getdate(), 0, 0, 0))
SELECT DISTINCT Buff.Datum_dospijeca,
(SELECT(SUM(t.Duguje - t.Potrazuje)) AS Saldo FROM Buff t WHERE t.Datum_dospijeca <= Buff.Datum_dospijeca) AS Saldo,
Coalesce(
DateDiff(
day,
Buff.Datum_dospijeca,
(SELECT MIN(t3.Datum_dospijeca) FROM Buff t3 WHERE t3.Datum_dospijeca > Buff.Datum_dospijeca)
)
,0) AS ProtekloDana,
(SELECT(SUM(t.Duguje - t.Potrazuje)) AS Saldo FROM Buff t WHERE t.Datum_dospijeca <= Buff.Datum_dospijeca) * 0.0003 *
Coalesce(
DateDiff(
day,
Buff.Datum_dospijeca,
(SELECT MIN(t3.Datum_dospijeca) FROM Buff t3 WHERE t3.Datum_dospijeca > Buff.Datum_dospijeca)
)
,0) AS Kamata
FROM Buff
ORDER BY Datum_dospijeca
Rezultat queryja:
Code:
Datum_dospijeca Saldo ProtekloDana Kamata
--------------------------------------------------------------
2008-01-01 00:00:00.000 -80 31 -0.744
2008-02-01 00:00:00.000 -136 29 -1.1832
2008-03-01 00:00:00.000 -196 30 -1.764
2008-03-31 00:00:00.000 54 1 0.0162
2008-04-01 00:00:00.000 1 10 0.003
2008-04-11 00:00:00.000 51 234 3.5802
2008-12-01 18:08:37.187 51 0 0
Ja sam isao drugim pravcem, mjesto da mnozim Saldo jednog reda sa ProtekloDana narednog reda, ProtekloDana racunam kao razliku trenutnog i sledeceg, a ne kao razliku trenutnog i proslog kao prije, tako da vec imam Saldo i ProtekloDana u istom redu. I to je radi super.
Medjutim, odamah ima nesto sto ne razumijem. Naime, ne mogu da referencirama kolone na osnovu alijasa, nego mi izbacuje gresku tipa ne moze da nadje kolonu Saldo i ProtekloDana, pa mjesto prostog Saldo * ProtekloDana moram ponovo da pisem izraze. Valjda je SQL server dovoljno pametan, pa nece istu stvar dva puta racunat, ali mi nije jasno zasto alijasi ne rade. Koristim SQL Server Express 2005.
Onda, vec razmisljam dalje, htio bih da napravim upit koji ce mi dati gotove iznose kamata za svakog obveznika i konto.
Zbog toga sam proba da rijesim isti problem bez WITH:
Code:
SELECT DISTINCT Data.Datum_dospijeca,
(SELECT(SUM(t.Duguje - t.Potrazuje)) AS Saldo FROM Data t WHERE t.Datum_dospijeca <= Data.Datum_dospijeca) AS Saldo,
Coalesce(
DateDiff(
day,
Data.Datum_dospijeca,
(SELECT MIN(t1.Datum_dospijeca) FROM Data t1 WHERE t1.Datum_dospijeca > Data.Datum_dospijeca)
)
,0) AS ProtekloDana,
(SELECT(SUM(t.Duguje - t.Potrazuje)) AS Saldo FROM Data t WHERE t.Datum_dospijeca <= Data.Datum_dospijeca) * 0.0003 *
Coalesce
(DateDiff(
day,
Data.Datum_dospijeca,
(SELECT MIN(t1.Datum_dospijeca) FROM Data t1 WHERE t1.Datum_dospijeca > Data.Datum_dospijeca)
)
,0) AS Kamata
FROM
(
SELECT * FROM Data
UNION
(
SELECT (SELECT MAX(ID)+1 FROM Data), 1, 1, GetDate(), GetDate(), 0, 0, 0
)
) AS Data
ORDER BY Datum_dospijeca
Ali pogledaj rezultat:
Code:
Datum_dospijeca Saldo ProtekloDana Kamata
--------------------------------------------------------------
2008-01-01 00:00:00.000 -80 31 -0.744
2008-02-01 00:00:00.000 -136 29 -1.1832
2008-03-01 00:00:00.000 -196 30 -1.764
2008-03-31 00:00:00.000 54 1 0.0162
2008-04-01 00:00:00.000 1 10 0.003
2008-04-11 00:00:00.000 51 0 0
2008-12-01 18:19:09.857 51 0 0
Tu je druga stvar koju ne razumijem. Ako stavim da je alijas FROM odjeljka BILO STA DRUGO osim rijeci DATA, prilikom pozivanja procedure prijavice mi gresku 'ne mogu da nadjem objekat, recimo, 'Test'. Tek kad stavim da je alijas DATA procedura onda prodje, ali se zadnji - UNION - zapis ne obradjuje. Uzmimo predzadnji zapis, datum 2008-04-11. Imamo vrijednost nula u koloni ProtekloDana, sto znaci da zadnje uporedjivanje t.Datum_dospijeca > Data.Datum_dospijeca daje false, pa SELECT MIN daje Null. Dakle, SELECT MIN ne vidi zadnji zapis. Kolona Saldo sadrzi ispravnih 51, jer ona zavisi samo od proslih zapisa i trenutnog zapisa (<=) dok kolona ProtekloDana zavisi od trenutnog i sledeceg (>). Ali ipak, eto ga zadnji zapis u rezultatu.
Problem je vjerovatno sto sam alijas nazvao Data. Ko zna koje Data SQL Server odje koristi, tabelu Data ili alijas Data???
Sto se tice zbirnog upita za sve obveznike, mora se opet preko ovih ugnjezdenih upita. Valjda. Prvo sracunam sve moguce kombinacije Obveznika i Konta koje se pojavljuju, pa onda treba da racunam kamate za svaku od njih. Ovi ugnjezdeni upiti su kao for each u C#.
E sad, treba da limitiram set podataka unutrasnjeg upita tako da odgovaraju samo onima iz spoljasnjeg. Tu WHERE i GROUP BY nista ne pomazu, jedva ukapirah. Moram WHERE unutar FROM, ili bi eventualno mogao da dodam jos dva uporedivanja t1.Obveznik = Data.Obveznik i t1.Konto = t2.Konto onome t1.Datum_dospijeca > Data.Datum_dospijeca. To je ono sto si pricao da ORDER BY nema uticaja na rezultat upita, jel?
Od resenja sa WITH sam odma odusta, ta klauzula mora valjda bit van svih ugnjezdavanja.
Evo kako sam probao:
Code:
SELECT DISTINCT Obveznik, Konto,
(
SELECT SUM(Kamata) FROM
(
SELECT (SELECT(SUM(t.Duguje - t.Potrazuje)) AS Saldo FROM Data t WHERE t.Datum_dospijeca <= Data.Datum_dospijeca) * 0.0003 *
Coalesce(
DateDiff(
day,
Data.Datum_dospijeca,
(SELECT MIN(t1.Datum_dospijeca) FROM Data t1 WHERE t1.Datum_dospijeca > Data.Datum_dospijeca)
)
,0) AS Kamata
FROM
(
SELECT * FROM Data
WHERE Data.Obveznik = Spoljni.Obveznik AND Data.Konto = Spoljni.Konto
UNION (SELECT (SELECT MAX(ID)+1 FROM Data), Spoljni.Obveznik, Spoljni.Konto, Getdate(), Getdate(), 0, 0, 0)
) AS Data
) AS tmp
)
FROM Data AS Spoljni
Srednji SELECT je tu jer mi ne da da racunam tu sumu direktno u unutrasnjem upitu, kaze ne moze da racuna sumu nad poljem koje vec ima agregatnu f-ju ili ima korelisan upit. Ajde dobro.
Evo sta je rezultat ovog upita:
Code:
Obveznik Konto Kamata
------------------------------
1 1 -5.016
1 2 -1.905
A trebalo bi da bude ovo (kada dva puta odradim pojedinacni upit):
Code:
Obveznik Konto Kamata
------------------------------
1 1 -8.073
1 2 +7.9812
Sigurno alijasi prave problem, ali ne znam zasto mi to rade. Pogledaj molim te, ako budes imao kad.
Pozdrav i hvala. [ Zidar @ 02.12.2008. 14:08 ] @
:-)
Moras da usporis malo. Ne razumem se mnogo u posao koji radis pa ne verujem da mogu dalje da ti pomognem mnogo. Gledanje u etst podatke ne pomaze mi mnogo, jer se u postu sve prelomi pa se ne vidi bas dobro. Dalje, da bih bilo sta uradio sa kverijima moram da imam podatke kod mene. Treba da prilozis CREATE izraz za atbelu i INERT izraze tako da ja mogu da reprodukujem podatrke kod mene. tek onda mogu da probam kverije koji rade i koji ne rade i mozda budem u stanju da pomognem. Bez toga - nista.
[ Mikelly @ 02.12.2008. 14:19 ] @
Vazi, majstore!
Copyright (C) 2001-2025 by www.elitesecurity.org. All rights reserved.
|