[es] - SQL mozgalica za mene, za nekog mozda \\\'piece of cake\\\'

Mikelly

Član broj: 16730
Poruke: 389
*.crnagora.net.

Profil

SQL mozgalica za mene, za nekog mozda \\\'piece of cake\\\'

^{27.11.2008. u 12:45 - pre 187 meseci}

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.

Odgovor na temu

sallle
Sasa Ninkovic
GTECH
Beograd

Član broj: 146
Poruke: 480
..178.212.adsl.dyn.beotel.net.

ICQ: 20785904

+4 Profil

Re: SQL mozgalica za mene, za nekog mozda \'piece of cake\'

^{28.11.2008. u 01:16 - pre 187 meseci}

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>

Odgovor na temu

Mikelly

Član broj: 16730
Poruke: 389
*.crnagora.net.

Profil

Re: SQL mozgalica za mene, za nekog mozda \'piece of cake\'

^{28.11.2008. u 12:44 - pre 187 meseci}

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.

Odgovor na temu

sallle
Sasa Ninkovic
GTECH
Beograd

Član broj: 146
Poruke: 480
80.93.229.*

ICQ: 20785904

+4 Profil

Re: SQL mozgalica za mene, za nekog mozda \'piece of cake\'

^{28.11.2008. u 14:11 - pre 187 meseci}

u pravu si, nisam to primetio

Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3453

Jabber: djoka_l

+1462 Profil

Re: SQL mozgalica za mene, za nekog mozda \'piece of cake\'

^{28.11.2008. u 14:49 - pre 187 meseci}

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...

Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.

+79 Profil

Re: SQL mozgalica za mene, za nekog mozda \'piece of cake\'

^{28.11.2008. u 14:50 - pre 187 meseci}

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.

Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.

+79 Profil

Re: SQL mozgalica za mene, za nekog mozda \'piece of cake\'

^{28.11.2008. u 15:44 - pre 187 meseci}

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.

Odgovor na temu

Mikelly

Član broj: 16730
Poruke: 389
77.222.3.*

Profil

Re: SQL mozgalica za mene, za nekog mozda \'piece of cake\'

^{28.11.2008. u 23:21 - pre 187 meseci}

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?

Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.

+79 Profil

Re: SQL mozgalica za mene, za nekog mozda \\\\\\\\\\\\\\\'piece of cake\\\\\\\\\\\\\\\'

^{01.12.2008. u 15:26 - pre 187 meseci}

Citat:

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]}

Odgovor na temu

Mikelly

Član broj: 16730
Poruke: 389
77.222.6.*

Profil

Re: SQL mozgalica za mene, za nekog mozda \'piece of cake\'

^{02.12.2008. u 12:29 - pre 187 meseci}

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.

Odgovor na temu